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 6 2551
[posted and mailed, please reply in news]
Allan (pr********@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, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...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:<94********************************@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" <pr********@hotmail.com> wrote in message
news:7b**************************@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:<94********************************@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 (pr********@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, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Bruce Duncan |
last post by:
I'm a bit new to MySQL (know MS SQL well...and that may be the
problem...getting the syntax confused) and I'm having a join problem...can
anyone offer some help? Here's my problem:
I have table1...
|
by: Matt |
last post by:
Hello
I have to tables ar and arb, ar holds articles and a swedish
description, arb holds descriptions in other languages.
I want to retreive all articles that match a criteria from ar and...
|
by: JMorrell |
last post by:
First post to this community so am not sure if this is the correct place.
Here goes.
I have a MS Access db that keeps track of employees sick and annual leave
balances. In it, I have a report,...
|
by: Andrew McNab |
last post by:
Hi folks,
I have a problem with an MS Access SQL query which is being used in an
Access Report, and am wondering if anyone can help.
Basically, my query (shown below) gets some records from a...
|
by: lovkeshanand |
last post by:
Dear All,
I am using the oracle transparent gateway connectivity with sql server
using tg4msql,as far as
settings are concerned those were set and Connectivity is working Fine,
and getting the...
|
by: imranpariyani |
last post by:
Hi
i have a severe performance problem with one of my views which has 6 to 8 joins .. any help will be appreciated..
the view is:
CREATE OR REPLACE VIEW thsn.trade_view AS
SELECT...
|
by: HC |
last post by:
Hello, all, I started out thinking my problems were elsewhere but as I
have worked through this I have isolated my problem, currently, as a
difference between MSDE and SQL Express 2005 (I'll just...
|
by: icebox27 |
last post by:
Need urgent help!
I have 3 tables that are in Left Join
ex:
select * from tbl_a
left outer join tbl_b
on tbl_b.f1 = tbl_a.f1 and tbl_b.f2 = tbl_a.f2
left outer join tbl_c
on...
|
by: teneesh |
last post by:
Here I have a code for a view that has been created by a developer on my team. I am trying to use the very same code to create a view for a different formid/quesid. But I cannot figure out how this...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |