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.

Problem with In Place Upgrade of Named Instance?

I have two software products that are accessing the same MSDE Database instance. I recently upgraded the MSDE instance to SQL Express using the in place method. The upgrade runs with success. However, one scenerio is breaking both software products.

1. Product A is not installed

2. An older version of Product B which uses MSDE is intalled.

3. I upgrade the MSDE instance to SQL Express

4. An older version of Product A is installed that uses MSDE.

Note: At this point the install of product A attempts to install an MSDE named instance that is the same as the upgraded instance

5. The install of Product A fails and corrupts the registry keys

6. Now my SQL Express named instance is corrupt and product B can not find the database instance.

Question: Can MSDE and SQL Express run side by side with both having an instance with the same name? I am pretty sure that the answer is going to be No.

If not, how can I prevent this scenerio from occurring. I'm thinking that I will have to do a side by side upgrade creating a new instance with a different name. Then I will have to direct both products to the new named instance. Any ideas or suggestions?

no you can not have the 2 different database engines running with the same instance name. I personally would install the sqlexpress system using the .\SQLEXPRESS instance name as this is the default instance name when using the express or full version visual studio products.

If you do wish to install the systems on the same machine you might be able to direct the instance names by moderfiying the setup ini files on the MSDE System, or the command line tags.

Problem with Excel Pivot Table

I am facing problem like;

We generate reports thru Excel Pivot table. The data is coming from sales
cube. Let?s assume that there are 2 products A and B. The user wants to
filter for each product and view ?stock in hand? and ?total sales? for
particular month (as two columns).

From the Pivot table filtering, when we choose product A and month ?Jan?,
it is showing values for ?stock in hand? and ?total sales? columns for that
month.

But when we choose product B and month ?Jan?, it is only showing ?stock in
hand? value and the ?total sales? column was suppressed/unseen. We know
that there is no sales transaction for ?Jan? month. But we still want to
show the column title even if there are no records in the database / cube.

I am not using any scripting, this pivot table was generated just thru
Pivot table wizard.

Any way to achieve this, please advise me.

Advanced thanks for u r help.

--
Message posted via http://www.sqlmonster.comBajal Mohamed via SQLMonster.com (forum@.SQLMonster.com) writes:
> I am facing problem like;
> We generate reports thru Excel Pivot table. The data is coming from sales
> cube. Let?s assume that there are 2 products A and B. The user wants to
> filter for each product and view ?stock in hand? and ?total sales? for
> particular month (as two columns).
> From the Pivot table filtering, when we choose product A and month
> ?Jan?, it is showing values for ?stock in hand? and ?total sales?
> columns for that month.
> But when we choose product B and month ?Jan?, it is only showing ?stock in
> hand? value and the ?total sales? column was suppressed/unseen. We know
> that there is no sales transaction for ?Jan? month. But we still want to
> show the column title even if there are no records in the database / cube.
> I am not using any scripting, this pivot table was generated just thru
> Pivot table wizard.

I'm not really sure why you are asking this in a newsgroup for
SQL Server. It sonds like an Excel issue to me.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp