MySQL GROUP BY "and filter"

group-by, mysql, sql

Solution

SELECT name, GROUP_CONCAT(number)
FROM objects
WHERE number IN (2,3)
GROUP BY name
HAVING COUNT(*) = 2

- SEE SQLFiddle Demo

or if you want to retain all value on which the name has,

SELECT  a.name, GROUP_CONCAT(A.number)
FROM    objects a
        INNER JOIN
        (
          SELECT name
          FROM objects
          WHERE number IN (2,3)
          GROUP BY name
          HAVING COUNT(*) = 2
        ) b ON a.Name = b.Name
GROUP BY a.name

- SEE SQLFiddle Demo

Problem

Let's say i have query like this: ``` SELECT name, GROUP_CONCAT(number) FROM objects GROUP BY name ``` And it outputs: ``` +----------+----------------------+ | NAME | GROUP_CONCAT(NUMBER) | +----------+----------------------+ | false_1 | 2,1 | | false_2 | 3,4 | | true_1 | 4,3,2,1 | | true_2 | 2,3 | +----------+----------------------+ ``` Now how can i return rows having `2 AND 3` as `number`? Note: This query is grouped - table has 10 rows, like so: ``` +---------+--------+ | NAME | NUMBER | +---------+--------+ | true_1 | 1 | | true_1 | 2 | | true_1 | 3 | | ... | ... | +---------+--------+ ``` [Link to SQLFiddle]

Original source