CODEWITHSUNDEEP
mysqlsql
Gazzer
Gazzer

Reputation: 4646

Matching items in one table that don't match in a subset of a second table

Suppose I have a Product table, and a 

id product
1  Apple
2  Bag
3  Cat
4  Ducati

and a Cart table 

 id  user_id   product_id
 1     1          2
 2     1          3
 3     2          1
 4     3          1

So, I want to look at a particular user and see what he/she does NOT have in their Cart.

In other words, in the above example

SELECT ...... WHERE user_id=1 .....

would return Apple and Ducati because User 1 already has Bag and Cat.

(This may well duplicate another question but there are so many variations I couldn't find the exact match and put in these simple terms may help)

Upvotes: 2

Views: 76

Answers (4)

Kevin Bowersox
Kevin Bowersox

Reputation: 94459

Perform a left join from product to all products purchased by user1, which can be retrieved with a subselect in the join. This will cause all product id's that are not in user1's care to have null product ids. The where clause will select all null product id's meaning they will not have been in a users cart, essentially filtering purchased items.

select p.name
from product p
left join (select product_id, user_id
           from cart where user_id = 1)
c
on p.id = c.product_id
where c.product_id is null;

SQL Fiddle: http://sqlfiddle.com/#!2/5318eb/17

Upvotes: 2

Amit Singh
Amit Singh

Reputation: 8109

This will give you all product for all users which are not in there cart.

select  c.user_id,a.Product
from cart c Cross Join product a 
left Join 
cart b on b.product_id=a.id and c.user_id=b.user_Id
where b.product_id is null
group by  c.user_id,a.Product

Sql Fiddle Demo

Upvotes: 0

Deepak Rai
Deepak Rai

Reputation: 2203

SELECT product FROM product_table 
WHERE product NOT IN
(SELECT product_id FROM cart_table WHERE user_id = 1);

Upvotes: 0

asantaballa
asantaballa

Reputation: 4048

Select
  * 
From Product p
Where p.id Not In
    (
        Select c.product_id
        From Cart c
        Where User ID = ____
    )

Upvotes: 0

Related Questions