CODEWITHSUNDEEP
mysqlsql
mdespeuilles
mdespeuilles

Reputation: 178

SQL Query filter on two columns

I have a table with this columns :

id | partner_id | product_id
-----------------------------
1  |   2        |   71
2  |   2        |   83
3  |   3        |   71
4  |   4        |   83
5  |   4        |   71
6  |   5        |   22
7  |   4        |   55

I want only the lines where the partner_id have the product_id 83 AND 71 like this : 

id | partner_id | product_id
-----------------------------
1  |   2        |   71
2  |   2        |   83
4  |   4        |   83
5  |   4        |   71

Thank your very much for your help :-)

Upvotes: 1

Views: 201

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use exists:

select t.*
from t
where t.product_id in (71, 83) and
      exists (select 1
              from t t2
              where t2.partner_id = t.partner_id and
                    t2.product_id in (71, 83) and
                    t2.product_id <> t.product_id
             );

However, it might make sense also if you only wanted the partners. In that case, you can use group by:

select partner_id
from t
where product_id in (71, 83)
group by partner_id
having count(*) = 2;  -- assuming no duplicates

Upvotes: 2

CoderCharmander
CoderCharmander

Reputation: 1910

select * from tableName where product_id = 71 or product_id = 83

Upvotes: -1

Related Questions