Problem with LEFT JOIN... please help!

**Erland Sommarskog** · Jul 20 '05, 03:57 AM

Re: Problem with LEFT JOIN... please help!

[posted and mailed, please reply in news]

Allan (proflicker@hot mail.com) writes:[color=blue]
> Select Product.ProdID, Colors.Colors
> From
> Products
> Left Join Colors
> on Product.ProdID = Colors.ProdID
> where Colors.Colors = "blue"[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

**Hugo Kornelis** · Jul 20 '05, 03:57 AM

Re: Problem with LEFT JOIN... please help!

On 7 Jun 2004 15:26:39 -0700, Allan wrote:
[color=blue]
>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[/color]

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)

**Allan** · Jul 20 '05, 03:57 AM

Re: Problem with LEFT JOIN... please help!

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_rFa ct.in_SPAM_fo> wrote in message news:<94r9c0lov k9hshhb5monp75s 24mg1abnd2@4ax. com>...[color=blue]
> On 7 Jun 2004 15:26:39 -0700, Allan wrote:
>[color=green]
> >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[/color]
>
> 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[/color]

**John Gilson** · Jul 20 '05, 03:57 AM

Re: Problem with LEFT JOIN... please help!

"Allan" <proflicker@hot mail.com> wrote in message
news:7b5b0602.0 406071919.319fa 873@posting.goo gle.com...[color=blue]
> Thanks for the quick reply...
>
> I had already tried that and I get an error message saying:
>
> JOIN expression not supported
>
> Any other suggestions?[/color]

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
[color=blue]
> Gad
>
>
> Hugo Kornelis <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message[/color]
news:<94r9c0lov k9hshhb5monp75s 24mg1abnd2@4ax. com>...[color=blue][color=green]
> > On 7 Jun 2004 15:26:39 -0700, Allan wrote:
> >[color=darkred]
> > >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[/color]
> >
> > 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[/color][/color]

**Erland Sommarskog** · Jul 20 '05, 03:58 AM

Re: Problem with LEFT JOIN... please help!

Allan (proflicker@hot mail.com) writes:[color=blue]
> Thanks for the quick reply...
>
> I had already tried that and I get an error message saying:
>
> JOIN expression not supported[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

**--CELKO--** · Jul 20 '05, 03:58 AM

Re: Problem with LEFT JOIN... please help!

>>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 (..)),
..);
[color=blue][color=green]
>> I want to find out all the products that come in Blue, and if not I[/color][/color]
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.

Problem with LEFT JOIN... please help!

Problem with LEFT JOIN... please help!

Comment

Comment

Comment

Comment

Comment

Comment