Query one value but not another

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

SQL Server 2000 Forums

Transact-SQL (2000)

Query one value but not another

Author

Topic

martyn
Starting Member

9 Posts

Posted - 2006-12-07 : 13:17:03

I'm trying to write a query that does the following

I want a resultset of customers who bought one product but have not bought another product

My table is
InvoiceNumber, Productcode, customernumber, price and quantity
so for each invoice I get multiple rows in the table (One for each product)

I'm going round in circles where to start

Thanks in advance

Martyn

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 14:13:01

Very good that your data is normalized. Try this

SELECT		t1.*
FROM		<YourTableNameHere> t1
WHERE		t1.ProductCode IN (<ValidProductCodesHere>)
		AND t1.CustomerNumber NOT IN (SELECT t2.CustomerNumber FROM <YourTableNameHere> t2 WHERE t2.ProductCode IN (<InvalidProductCodesHere>))

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 14:15:31

[code]SELECT t.*
FROM <YourTableNameHere> t
LEFT JOIN (
SELECT CustomerNumber
FROM <YourTableNameHere>
WHERE ProductCode IN (<InvalidProductCodesHere>)
) x ON x.CustomerNumber = t.CustomerNumber
WHERE t.ProductCode IN (<ValidProductCodesHere>)
AND x.CustomerNumber IS NULL[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 14:25:48

Or, if you want only the CustomerNumbers,

SELECT DISTINCT t1.CustomerNumber
FROM <YourTableNameHere> t1
...

or

SELECT DISTINCT t.CustomerNumber
FROM <YourTableNameHere> t
...

Peter Larsson
Helsingborg, Sweden

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-12-07 : 14:46:13

I don't like NOT IN

SELECT t1.*
FROM <YourTableNameHere> t1
WHERE t1.ProductCode IN (<ValidProductCodesHere>)
AND not exists (
select 1
from <YourTableNameHere> t2
where t1.CustomerNumber = t2.CustomerNumber and
t2.ProductCode in (<InvalidProductCodesHere>))

Jay
to here knows when

martyn
Starting Member

9 Posts

Posted - 2006-12-08 : 04:35:29

Absolutly brillent - both ways work great

Thank you very much

Martyn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources