3

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.

1
  • Please clarify something: Can, say, (1,20) show up in two different rows in the source data?
    – Rick James
    Nov 14 '18 at 22:12
2

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.)

4
  • Yeah great! And what if instead of selecting 2 values, I want to select rows that have at least one of the values in ('00020', '00010') and in ('00023', '00033')? Nov 12 '18 at 20:19
  • HAVING GROUP_CONCAT(DISTINCT col1 ORDER BY col1) = '00020,00023' There is more simple variant: HAVING COUNT(DISTINCT col1) = 2. And in case of a pair (id,col1) is unique (by index!) the DISTINCT is not nesessary and must be removed.
    – Akina
    Nov 13 '18 at 7:24
  • @Akina - The pair being unique does not imply that col1, itself, is unique.
    – Rick James
    Nov 13 '18 at 14:58
  • @RickJames You group records by id. If the pair is unique, the col1 is unique for each separate id.
    – Akina
    Nov 13 '18 at 18:16
0

I want to find which IDs have a combination of 2 values in the column 'col1'

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

For the case the solution is simple enough the pair (id,col1) must be unique. And the solution will be:

SELECT id
FROM table
-- WHERE col1 IN ('00020', '00010', '00023', '00033')
GROUP BY id
HAVING 2 = SUM(col1 IN ('00020', '00010'))
    OR 2 = SUM(col1 IN ('00023', '00033'))

If the pair is not unique, you must prepare source data:

SELECT id
FROM ( SELECT DISTINCT id, col1
       FROM table 
       -- WHERE col1 IN ('00020', '00010', '00023', '00033')
     ) purifying
GROUP BY id
HAVING 2 = SUM(col1 IN ('00020', '00010'))
    OR 2 = SUM(col1 IN ('00023', '00033'))

In both cases uncommenting WHERE may increase performance (especially if a data array is huge)... but it must be tested in practice.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.