Problem with LEFT JOIN... please help!

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[posted and mailed, please reply in news]

Allan (proflicker@.hotmail.com) writes:
> Select Product.ProdID, Colors.Colors
> From
> Products
> Left Join Colors
> on Product.ProdID = Colors.ProdID
> where Colors.Colors = "blue"

When you say:

FROM a LEFT JOIN b on ...

You are, concpetually, constructing a table. Then you apply a WHERE
clause to filter out rows from that table.

Thus for

Products Left Join Colors on Product.ProdID = Colors.ProdID

You get a table with data in all columns for Products, but where
there is no matching row in Colors, you get NULL.

Then you apply a WHERE clause to this, but then you filter all those
NULL rows, because NULL is not equal to "blue".

The remedy is to move the condition to the ON clause:

Products Left Join Colors
on Product.ProdID = Colors.ProdID
and Colors.Color = "blue"

Now the condition on Colors becomes part of that conceptual table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 7 Jun 2004 15:26:39 -0700, Allan wrote:

>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

Hi Allan,

Try:

Select Product.ProdID, Colors.Colors
From
Products
Left Join Colors
on Product.ProdID = Colors.ProdID
and Colors.Colors = 'blue'

(untested)

Note: Changed "where" to "and" and also changed double-quotes to
single-quotes (single quotes are the standard string delimiter for SQL, as
defined by the ANSI standard. Double quotes are, depending on the setting
of some option, still supported in SQL Server 2000 for backward
compatibility)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for the quick reply...

I had already tried that and I get an error message saying:

JOIN expression not supported

Any other suggestions?

Gad

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<94r9c0lovk9hshhb5monp75s24mg1abnd2@.4ax.com>...
> On 7 Jun 2004 15:26:39 -0700, Allan wrote:
> >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
> Hi Allan,
> Try:
> Select Product.ProdID, Colors.Colors
> From
> Products
> Left Join Colors
> on Product.ProdID = Colors.ProdID
> and Colors.Colors = 'blue'
> (untested)
> Note: Changed "where" to "and" and also changed double-quotes to
> single-quotes (single quotes are the standard string delimiter for SQL, as
> defined by the ANSI standard. Double quotes are, depending on the setting
> of some option, still supported in SQL Server 2000 for backward
> compatibility)
> Best, Hugo|||"Allan" <proflicker@.hotmail.com> wrote in message
news:7b5b0602.0406071919.319fa873@.posting.google.c om...
> Thanks for the quick reply...
> I had already tried that and I get an error message saying:
> JOIN expression not supported
> Any other suggestions?

The name of your products table is "Products", right?
You have "Product" in the join condition of your query.

SELECT P.prodID, C.colors
FROM Products AS P
LEFT OUTER JOIN
Colors AS C
P.prodID = C.prodID AND
C.colors = 'blue'

--
JAG

> Gad
>
> Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:<94r9c0lovk9hshhb5monp75s24mg1abnd2@.4ax.com>...
> > On 7 Jun 2004 15:26:39 -0700, Allan wrote:
> > >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
> > Hi Allan,
> > Try:
> > Select Product.ProdID, Colors.Colors
> > From
> > Products
> > Left Join Colors
> > on Product.ProdID = Colors.ProdID
> > and Colors.Colors = 'blue'
> > (untested)
> > Note: Changed "where" to "and" and also changed double-quotes to
> > single-quotes (single quotes are the standard string delimiter for SQL, as
> > defined by the ANSI standard. Double quotes are, depending on the setting
> > of some option, still supported in SQL Server 2000 for backward
> > compatibility)
> > Best, Hugo|||Allan (proflicker@.hotmail.com) writes:
> Thanks for the quick reply...
> I had already tried that and I get an error message saying:
> JOIN expression not supported

It is at this time I find it appropriate to ask which version of SQL
Server you are using. Or rather which DBMS you are using. That message
does not sound familliar at all. But it could also be that you are issueing
the query through some unknown tool which has its own quirks. Did you
try running in Query Analyzer?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>>Let's say I have 2 tables, a Products table and a Colors table that
go as follow: <<

1) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

2) Next, can you explain why color is NOT AN ATTRIBUTE of a product??
In your reality and therefore, your data model, it floats around with
weight, height, or whatever physcial attributes that go with being a
color?

If you had done this right, wouldn't it look more like this?

CREATE Table Products
(sku INTEGER NOT NULL PRIMARY KEY,
description VARCHAR(25) NOT NULL,
color CHAR(5) NOT NULL
CHECK(color IN (..)),
..);

>> I want to find out all the products that come in Blue, and if not I
want to have the color empty. <<

Weird, if your inventory is of any size at all; How many non-blue
things do you think that J.C. Penney's has, as compared to blue
things? But you can try this:

SELECT I1.sku, 'Blue'
FROM Inventory AS I1
WHERE color = 'Blue'
UNION ALL
SELECT I1.sku, 'Not Blue'
FROM Inventory AS I1
WHERE color <> 'Blue';

If the Colors table were actually not an attribute in a properly
designed schema, then you'd use an outer join.