CODEWITHSUNDEEP
mysqlsql
yolegu
yolegu

Reputation: 103

MySQL query to select groups containing at least a certain number of elements

I have the following table:

+-------------------+
| Id | G_id | E_id  |
+-------------------+
| 1  |   1  |   1   |
---------------------
| 2  |   1  |   2   |
---------------------
| 3  |   2  |   1   |
---------------------
| 4  |   2  |   3   |
---------------------
| 5  |   3  |   1   |
---------------------
| 6  |   3  |   2   |
---------------------
| 7  |   3  |   3   |
+-------------------+

where G_id is a group's id contaning several elements identified by their E_id. For exemple, group 1 is made of elements 1 and 2, group 2 of elements 1 and 3 and group 3 of elements 1, 2 and 3.

I want to make a query over this table so that it returns all groups that contain at least elements 1 and 3, ie groups 2 and 3. Thanks !

Upvotes: 4

Views: 431

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93734

You need Group By and Having clause

select G_id 
from yourtable 
where E_id in (1,3)
group by G_id 
having count(distinct E_id) = 2

Update: 

select G_id 
from yourtable 
group by G_id 
having count(case when E_id = 1 then 1 end) > 0
   and count(case when E_id = 3 then 1 end) > 0
   and count(distinct E_id) = 3

Upvotes: 4

Related Questions