How to fetch Rows have not this column value in another table column value

Question

I have a table like this:

Tb_Product_Options:

Product_Id Option_Id 1 5 1 7 2 3 3 9 3 6

Now I want to get all Product_Ids have not this values : '5,9'. at this example my result must be: 2 (product_id)

Yogesh Sharma · Accepted Answer · 2020-01-14 10:49:33Z

You can aggregation :

select Product_Id from table t group by Product_Id having sum ( Option_Id in (5,9) ) = 0;

If you want all columns then you can use NOT EXISTS :

select t.* from table t where not exists (select 1 from table t1 where t1.Product_Id = t.Product_Id and t1.Option_Id in (5,9) );

Strawberry · Accepted Answer · 2020-01-14 10:51:39Z

In the old days, we would use an exclusion join:

SELECT DISTINCT x.* FROM my_table x LEFT JOIN my_table y ON y.stuff = x.stuff AND y.other_stuff IN(some,values) WHERE y.id IS NULL

Atose O · Accepted Answer · 2020-01-14 11:04:12Z

This surely works:

 select product_id from Tb_Product_Options where product_id not in ( select product_id from Tb_Product_Options where option_id in (5,9) )

3 Answers 3