By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,489 Members | 1,840 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,489 IT Pros & Developers. It's quick & easy.

left join driving me crazy

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
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
Nov 13 '05 #3

P: n/a
>
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
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.