not in - 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

not in

Author

Topic

blizzard1
Starting Member

4 Posts

Posted - 2011-09-22 : 09:36:38

Hey guys!
I'm trying to get this to work, but it won't.
I want to know which customer did not buy which product.
But right now it only tells me which products were not bought by all of the customers but it should tell me for each customer individually!

SELECT distinct a.customerid,b.productid
FROM b,a
WHERE b.productid NOT IN
(SELECT productid
FROM a where customerid) order by customerid;

Help please :(

blizzard1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-09-22 : 10:25:18

should be something like this if there isn't a product-table....


select
c.customerid,
dt.productid
from customer as c
cross join (select distinct productid from ordertable)dt
where not exists(select * from ordertable as o 
                 where o.customerid=c.customerid 
                   and o.productid=dt.productid)

No, you're never too old to Yak'n'Roll if you're too young to die.

blizzard1
Starting Member

4 Posts

Posted - 2011-09-22 : 11:39:40

Thanks for your reply!

I didn't get it to work though :(

I have one combined table(I created a view) like this:
customerid / orderid / productid
1 1 3
1 1 4
2 2 1
3 4 2
4 11 6

so I want it to tell me that customer 1 never bought productid
1,2,5,6 and customer 2 never bought productid 2,3,4,5,6

I hope you get what I'm trying to say

blizzard1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-09-22 : 13:27:55

How can you be sure to have ALL customers in your view?
To answer more correct we need more information so we don't have to guess about
Exact table structure (tables that are used in your view?)
Sample data and
Wanted result.

No, you're never too old to Yak'n'Roll if you're too young to die.

blizzard1
Starting Member

4 Posts

Posted - 2011-09-23 : 11:26:08

Hi I created a whole example now:

create database blizzard1;
Use blizzard1;
CREATE TABLE customer (
customerid INT AUTO_INCREMENT,
lastname Varchar(30) NOT NULL,
firstname Varchar(30) NOT NULL,
PRIMARY KEY (customerid)
);

I hope you can help me out here