query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query help

Author

Topic

Alina
Starting Member

20 Posts

Posted - 2007-11-20 : 02:57:37

Hello. I have the following query and its projection gives me more answers than I need. I am suppose to display the stove type and version that have never been sold in CA. Can someone please point out to me what I'm doing wrong?
Thank you

SELECT DISTINCT STOVE.Type, STOVE.Version FROM STOVE, CUSTOMER, INVOICE, INV_LINE_ITEM
WHERE STOVE.SerialNumber=INV_LINE_ITEM.FK_StoveNbr
AND INV_LINE_ITEM.FK_InvoiceNbr=INVOICE.InvoiceNbr
AND INVOICE.FK_CustomerID=CUSTOMER.CustomerID
AND INVOICE.FK_CustomerID IN (SELECT CustomerID FROM CUSTOMER WHERE StateProvince NOT LIKE 'CA')

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-20 : 03:06:46

[code]SELECT DISTINCT STOVE.Type, STOVE.Version
FROM STOVE JOIN INV_LINE_ITEM ON STOVE.SerialNumber=INV_LINE_ITEM.FK_StoveNbr
JOIN INVOICE ON INV_LINE_ITEM.FK_InvoiceNbr=INVOICE.InvoiceNbr
JOIN CUSTOMER ON INVOICE.FK_CustomerID=CUSTOMER.CustomerID
WHERE CUSTOMER.StateProvince <> 'CA'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-20 : 03:11:30

[code]SELECT STOVE.Type, STOVE.Version
FROM STOVE
LEFT JOIN
(
SELECT INV_LINE_ITEM.FK_StoveNbr
FROM INV_LINE_ITEM
INNER JOIN INVOICE ON INV_LINE_ITEM.FK_InvoiceNbr = INVOICE.InvoiceNbr
INNER JOIN CUSTOMER ON INVOICE.FK_CustomerID = CUSTOMER.CustomerID
WHERE CUSTOMER.StateProvince = 'CA'
GROUP BY INV_LINE_ITEM.FK_StoveNbr
) S
ON STOVE.SerialNumber = S.FK_StoveNbr
WHERE S.FK_StoveNbr IS NULL[/code]

KH
[spoiler]Time is always against us[/spoiler]

Alina
Starting Member

20 Posts

Posted - 2007-11-20 : 03:53:29

Thank you. I'm still getting the same amount of projections. Maybe I need to double check something else, like the tables I'm using.
Thank you though, at least I know the commands I used are right and it's just a table mistake (I hope :) ).

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-20 : 05:07:50

you can also post your table DDL, sample data and the expected result.

KH
[spoiler]Time is always against us[/spoiler]

Alina
Starting Member

20 Posts

Posted - 2007-11-20 : 05:11:28

how would i do that? :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-20 : 05:16:02