SQL should find a group that includes exactly 2 specified values and check the total number of elements in the group (supplement my query)

Initially we have such a table

contact_id group_id
1 1
2 1
2 3
3 1
3 3
3 2
1 2

After that I make a query to search for groups containing the values of contacts 1 and 3

SELECT `group_id` ,COUNT(DISTINCT(`contact_id`)) AS `variants` 
FROM `TaskTeam_member` 
WHERE `contact_id`='1' OR `contact_id`='3' 
GROUP BY `group_id` 
HAVING `variants`='2' 

it turns out that such a table (correct)

contact_id variants
1 2
2 2

And now I need to add in addition to searching for values 1 and 3 in the group to check the total number of elements in it (I need 2), that is, if presumably there are elements 1 and 3 in group 1, but the total number of elements is 3 and not 2 as in the example above, then this group should not be output

the result should be like this

contact_id variants
2 2

help me complete my request!

>Solution :

If you want to check for other contact_ids you need to include all records, but only count the ones you want:

SELECT `group_id`
FROM `TaskTeam_member` 
GROUP BY `group_id` 
HAVING COUNT(DISTINCT contact_id)=2 AND COUNT(DISTINCT CASE contact_id WHEN 1 THEN 1 WHEN 3 THEN 3 END)=2

Leave a Reply