Search code examples
mysqlconditional-statements

Select rows that contain more than one condition simultaneously

I have table structure like this:

ID   cond
1    5
1    2
1    6
8    2
9    1
9    5

When I want to select rows that contain one or more conditions i use OR (...WHEN cond=2 OR cond=6 GROUP BY ID...)

But how to select rows that contain more than one condition simultaneously grouped by ID? E.g. when i want to find rows that contain cond 2 and 6, it returns only ID 1

Thanks

Solution

  • There are multiple ways of doing this.

    Using COUNT (fastest):

    SELECT id FROM tbl WHERE tbl.cond IN ( 2, 6 ) HAVING COUNT(DISTINCT cond) = 2 GROUP BY id

    Using EXISTS (using nested loops, slower on very large tables, but less cryptical and more xtensible than the COUNT variant):

    SELECT DISTINCT id FROM tbl AS tbl1 WHERE EXISTS (SELECT * FROM tbl AS tbl2 WHERE tbl1.id = tbl2.id AND tbl2.cond = 2) AND EXISTS (SELECT * FROM tbl AS tbl2 WHERE tbl1.id = tbl2.id AND tbl2.cond = 6)

    Using GROUP_CONCAT (a MySql specific variation on the COUNT theme, but if you ever want exact matches, e.g. cond=2 and cond=6 an no other cond, then the below, altered to read SELECT id, GROUP_CONCAT(DISTINCT cond ORDER BY cond) AS conds ... WHERE conds='2,6' will perform best)

    SELECT id, ','||GROUP_CONCAT(DISTINCT cond)||',' AS conds FROM tbl WHERE INSTR(conds, ',2,') > 0 AND INSTR(conds, ',6,') > 0 GROUP BY id