Question 178357 in Mysql Question posted in
The official MySQL documentation can be found here.

Can MySQL use SQL’s GROUP BY to match all items in a given list?

I have a table like below:

id device_id device_model
1 101 A
1 201 B
1 202 B
2 102 A
2 301 C
3 103 A
3 203 B
3 302 C

I want to select group of ids with specific device models given in a list. At first, I tried:

SELECT id
FROM tbl
WHERE device_model IN ('A', 'B')
GROUP BY id

But this works like OR… if an id has only one of ('A', 'B') it is returned. This is not the behavior I want, instead I want AND behavior.

I thought I can use this:

SELECT id
FROM tbl
WHERE device_model IN ('A', 'B')
HAVING COUNT(DISTINCT device_model) = 2;

But, this returns id 1 and 3; but I only want 1, not 3… since it also has device model C.

What can I do to get ids that contain exactly those device models given in a list?

Tags:

3

Answers


  1. 0

    Add the GROUP BY clause and one more condition inside the HAVING clause, that checks the presence of only A,B inside the field device_model.

    SELECT id
FROM tbl
GROUP BY id
HAVING COUNT(DISTINCT device_model) = 2
   AND MIN(device_model IN ('A', 'B')) = 1;

    This should work on MySQL, and should generalize well on any n amount of elements you want to check. It would suffice to add all your elements inside the array, and update the number of distinct elements.

    Output:

    id
    1

    Check the demo here.

    Login or Signup to reply.
  2. 0

    Since you want to test for "relational division remainder" you must get rid of where clause and add an additional condition in having clause:

    SELECT id
FROM tbl
GROUP BY id
HAVING COUNT(DISTINCT device_model) = 2
AND    COUNT(DISTINCT CASE WHEN device_model IN ('A', 'B') THEN device_model END) = 2
    Login or Signup to reply.
  3. 0

    You can use the HAVING clause along with conditional aggregation.

    SELECT id
FROM tbl
GROUP BY id
HAVING COUNT(DISTINCT CASE WHEN device_model IN ('A', 'B') THEN device_model END) = 2
   AND COUNT(DISTINCT CASE WHEN device_model NOT IN ('A', 'B') THEN device_model END) = 0;

    In this query, we use conditional aggregation to count the distinct device_models that match the specified models and those that don’t. The CASE statement is used to conditionally count the values based on the IN and NOT IN conditions.

    The HAVING clause then checks if the count of matching device_models is equal to the number of specified models (2 in this case) and the count of non-matching device_models is 0. This ensures that only the id groups having all the specified models and none of the others are selected.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search