CODEWITHSUNDEEP
mysqlsql
Kaz
Kaz

Reputation: 748

MySQL : Check condition on two rows

I'm stuck on a pretty simple task.

An image is better than words so here's a sample of my table :

I'd like to retrieve every distinct product_id that are both in groups 27 and 16 for exemple.

So I made this request :

SELECT DISTINCT product_id FROM my_table WHERE group_id = 27 AND group_id = 16

It's not working and I understand why, but I don't know how to do differently...

I know it's a very noobish question but I don't know what to use in this case, INNER JOIN, LEFT JOIN ...

Upvotes: 0

Views: 202

Answers (4)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You may do as

SELECT product_id FROM my_table WHERE group_id in(27,16)
group by  product_id 
having count(DISTINCT group_id) >= 2

Upvotes: 2

Lokesh Kumar Gaurav
Lokesh Kumar Gaurav

Reputation: 726

TRY THIS

SELECT DISTINCT product_id FROM my_table WHERE group_id IN(27,16)

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175616

You can use EXISTS:

SELECT DISTINCT m1.product_id 
FROM my_table m1
WHERE m1.group_id = 27
  AND EXISTS (SELECT 1
              FROM my_table m2
              WHERE m1.product_id = m2.product_id
                AND m2.group_id = 16);

Upvotes: 2

Arun Krish
Arun Krish

Reputation: 2153

Try this query

SELECT product_id, GROUP_CONCAT(group_id ORDER BY group_id) As groups
FROM my_table
GROUP BY product_id
HAVING
 FIND_IN_SET('27', groups)
 AND FIND_IN_SET('16', groups)

Upvotes: 1

Related Questions