CODEWITHSUNDEEP
mysqlsqlgroup-by
Kristian
Kristian

Reputation: 3461

MySQL GROUP BY "and filter"

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]

Upvotes: 8

Views: 12129

Answers (1)

John Woo
John Woo

Reputation: 263693

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

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

Upvotes: 13

Related Questions