CODEWITHSUNDEEP
mysqlsql
S.M_Emamian
S.M_Emamian

Reputation: 17383

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

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)

Upvotes: 1

Views: 44

Answers (3)

Atose O
Atose O

Reputation: 1

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)
                                          )

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

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

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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)
                 );

Upvotes: 5

Related Questions