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!
CodePudding user response：
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