Mysql – SQL: How to Select Multiple Row Data for the Same ID

MySQLsqlite

My data is like this:

ID      col1
0000001 00020
0000001 00023
0000001 00019
0000001 00010
0000003 00030
0000003 00020

I want to find which IDs have a combination of 2 values in the column 'col1' (appearing at least once each); for example, for the values 00020 and 00023 here I would get the ID 0000001 only.

Best Answer

If I interpret of your requirement correctly:

SELECT id
    FROM tbl
    WHERE col1 IN ('00020', '00023')
    GROUP BY id
    HAVING GROUP_CONCAT(DISTINCT col1 ORDER BY col1) = '00020,00023'

Performance might benefit from INDEX(col1, id).

A different query might include HAVING (COUNT DISTINCT col1) > 1, but the implied presence of dups may mess it up.

To select ids that have at least one of those col1s:

SELECT DISTINCT id
    FROM tbl
    WHERE col1 IN ('00020', '00023')

To select rows that have at least one of the values in ('00020', '00010') and in ('00023', '00033'):

SELECT id
    FROM tbl
    WHERE EXISTS( ( SELECT 1 FROM tbl
                      WHERE id = a.id
                        AND col1 IN ('00020', '00010') ) )
      AND EXISTS( ( SELECT 1 FROM tbl
                      WHERE id = a.id
                        AND col1 IN ('00023', '00033') ) )

(There are probably other ways, but this was the first one that came to mind.)

Related Question