left join driving me crazy

[Allan]

Please help,

below is my problem. Let's say I have 2 tables, a Products table and a
Colors table that go as follow:

Table Products

prodID Name
1 shirt
2 tshirt
3 pants
4 jeans

and

Table Colors

prodID Colors
1 Blue
1 Red
2 Blue
3 Black
3 White
4 Blue
I want to find out all the products that come in Blue, and if not I
want to have the color empty. The result I want from my Query / SQL
Statement is:

prodID Colors Name...

1 Blue
2 Blue
3
4 Blue

What should my SQL statement/Query be like?

I tried:

Select Product.ProdID, Colors.Colors
From
Products
Left Join Colors
on Product.ProdID = Colors.ProdID
where Colors.Colors = "blue"

and this is what I get:

prodID Colors

1 Blue
2 Blue
4 Blue
Notice that prodID 2 doesn't show up but I want to return all prodIDs
whether or not they have a color Blue.

Please help...

Thanks

[Douglas J. Steele]

Try creating a query that only returns Blue records from the Colors table:

SELECT prodID, Colors
FROM Colors
WHERE Colors = 'Blue'

Save that (for the sake of argument, call it qryBlue), then use it instead
of Colors in your Left Join:

Select Product.ProdID, qryBlue.Colors
From
Products
Left Join qryBlue
On Product.ProdID = qryBlue.ProdID

In newer versions of Access, this can actually be done in a single step:

SELECT Products.prodID, Sub.Colors
FROM Products LEFT JOIN
[SELECT prodID, Colors FROM Colors WHERE Colors = "Blue"]. AS Sub
ON Products.prodID = Sub.prodID;
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Allan" <pr********@hotmail.com> wrote in message
news:7b**************************@posting.google.c om...

Please help,

below is my problem. Let's say I have 2 tables, a Products table and a
Colors table that go as follow:

Table Products

prodID Name
1 shirt
2 tshirt
3 pants
4 jeans

and

Table Colors

prodID Colors
1 Blue
1 Red
2 Blue
3 Black
3 White
4 Blue
I want to find out all the products that come in Blue, and if not I
want to have the color empty. The result I want from my Query / SQL
Statement is:

prodID Colors Name...

1 Blue
2 Blue
3
4 Blue

What should my SQL statement/Query be like?

I tried:

Select Product.ProdID, Colors.Colors
From
Products
Left Join Colors
on Product.ProdID = Colors.ProdID
where Colors.Colors = "blue"

and this is what I get:

prodID Colors

1 Blue
2 Blue
4 Blue
Notice that prodID 2 doesn't show up but I want to return all prodIDs
whether or not they have a color Blue.

Please help...

Thanks

[Bob Quintal]

pr********@hotmail.com (Allan) wrote in
news:7b**************************@posting.google.c om:

Please help,

below is my problem. Let's say I have 2 tables, a Products
table and a Colors table that go as follow:

Table Products

prodID Name
1 shirt
2 tshirt
3 pants
4 jeans

and

Table Colors

prodID Colors
1 Blue
1 Red
2 Blue
3 Black
3 White
4 Blue
I want to find out all the products that come in Blue, and if
not I want to have the color empty. The result I want from my
Query / SQL Statement is:

prodID Colors Name...

1 Blue
2 Blue
3
4 Blue

What should my SQL statement/Query be like?

I tried:

Select Product.ProdID, Colors.Colors
From
Products
Left Join Colors
on Product.ProdID = Colors.ProdID
where Colors.Colors = "blue"

and this is what I get:

prodID Colors

1 Blue
2 Blue
4 Blue
Notice that prodID 2 doesn't show up but I want to return all
prodIDs whether or not they have a color Blue.

Please help...

Thanks

WHERE Colors.colors = "Blue" or colors.colors is Null

BOb Quintal

[Albert D. Kallal]

>

Select Product.ProdID, Colors.Colors
From
Products
Left Join Colors
on Product.ProdID = Colors.ProdID
where Colors.Colors = "blue"

and this is what I get:

prodID Colors

1 Blue
2 Blue
4 Blue
Well, if you want the empy ones also..then just add that condition....
where Colors.Colors = "blue" or Colors.Colors is null

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn