Queries will be the death of me

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...
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
"ChrisN" <cnewald@.hotmail.com> wrote in message
news:uJV$f$b1EHA.3416@.TK2MSFTNGP09.phx.gbl...
> 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...
>
|||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.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OlzoyOf1EHA.1300@.TK2MSFTNGP14.phx.gbl...
> 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
>
>
> "ChrisN" <cnewald@.hotmail.com> wrote in message
> news:uJV$f$b1EHA.3416@.TK2MSFTNGP09.phx.gbl...
> tables
> create
> as
> I
> PurchaseID,
> but
>
sql