Queries will be the death of me

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ChrisN - 29 Nov 2004 03:02 GMT

Hello! My query skills are in need of some help. I've simplified my tables
in order to explain what it is that I'm trying to do. I need this to create
dynamic checkboxes on an asp.net page and to have the checkbox values set as
the tables are drawn dynamically. Anyway, here goes:

Table A
ItemID <bigint>
Title <string>

Table B
PurchaseID <bitint>
ItemID <smallint>

I have two tables (A and B). The two tables are linked on field ItemID. I
need to make a query that will return ALL of the values from table A along
with a boolean value stating whether or not table B contains an item - but
only for a specific purchaseID.

For example:

Table A (ItemID, Title)
1 VCR
2 DVD
3 Television
4 Phone

Table B (PurchaseID, ItemID)
1 1 (VCR)
1 2 (DVD)
1 3 (Tel)
2 2 (DVD)
2 1 (...)
2 4
3 4
3 1

For the above data I need a list of the Items from tableA and a boolean
value showing whether the item exists in Table B for a specific PurchaseID,
in this case I used PurchaseID = 2.

Result Table (ItemID, Title, Boolean)
1 VCR 1
2 DVD 1
3 Television 0
4 Phone 1

The result shows that there exists a VCR, DVD and Phone for purchaseID, but
no Television.

Grateful for any help...

Reply to this Message

John Kane - 29 Nov 2004 09:13 GMT

Hello ChrisN,
I think this is what you're looking for (with tables and data included):

-- DROP TABLE A - Items
CREATE TABLE A (
ItemID int NOT NULL, -- PK
Title varchar (50) NOT NULL)
go
ALTER TABLE A WITH NOCHECK ADD PRIMARY KEY CLUSTERED (ItemID)
go
-- DROP TABLE B - Purchases
CREATE TABLE B (
PurchaseID int NOT NULL, -- PK
ItemID int NOT NULL) -- PK
go
ALTER TABLE B WITH NOCHECK ADD PRIMARY KEY CLUSTERED (PurchaseID, ItemID)
go
-- Insert test sample data into table A:
insert into A values (1, 'VCR')
insert into A values (2, 'DVD')
insert into A values (3, 'Television')
insert into A values (4, 'Phone')
go
-- Insert test sample data into table B:
insert into B values (1, 1) -- VCR
insert into B values (1, 2) -- DVD
insert into B values (1, 3) -- Television
insert into B values (2, 2) -- DVD
insert into B values (2, 1) -- VCR
insert into B values (2, 4) -- Phone
insert into B values (3, 4) -- Phone
insert into B values (3, 1) -- VCR
go
SELECT TableA.ItemID, TableA.Title
FROM A TableA
INNER JOIN B TableB ON (TableA.ItemID = TableB.ItemID)
WHERE PurchaseID = 2
go

However, are you also interested in a FTS query based upon the above INNER
JOIN between tables A & B since you posted this question in the fulltext
newsgroup? If so, could you post an example of what you're looking for?

Thanks,
John

> Hello! My query skills are in need of some help. I've simplified my tables
> in order to explain what it is that I'm trying to do. I need this to create
[quoted text clipped - 46 lines]
>
> Grateful for any help...

Reply to this Message

ChrisN - 29 Nov 2004 15:18 GMT

Thank you John for your response. Being a dough head I did post to the
wrong newsgroup and realized after it sent. I'll give your solution a try
in a little while.

Thanks again,
Chris.

> Hello ChrisN,
> I think this is what you're looking for (with tables and data included):
[quoted text clipped - 100 lines]
>>
>> Grateful for any help...

Reply to this Message