Question: Find all IDs which have a row with value A AND a row with value B

Question

Find all IDs which have a row with value A AND a row with value B

Answers 2
Added at 2017-11-30 20:11
Tags
Question

I have a table like this:

+-----+-------+-----+
| id  | value | ... |
+-----+-------+-----+
| 1   | A     | ... |
| 1   | B     | ... |
| 1   | C     | ... |
| 2   | B     | ... |
| 2   | C     | ... |
| 3   | A     | ... |
| 3   | C     | ... |
| 4   | B     | ... |
| 4   | A     | ... |
| ... | ...   | ... |
+-----+-------+-----+

I want to limit this to just ids that have both rows with A and rows with B in the value column. In this case, the table would look like this:

+-----+-------+-----+
| id  | value | ... |
+-----+-------+-----+
| 1   | A     | ... |
| 1   | B     | ... |
| 1   | C     | ... |
| 4   | B     | ... |
| 4   | A     | ... |
| ... | ...   | ... |
+-----+-------+-----+

… because neither id 2 nor 3 had both A and B in the value column.

Is there a succinct way to locate these IDs?

Answers to

Find all IDs which have a row with value A AND a row with value B

nr: #1 dodano: 2017-11-30 20:11

One way to do this is to count the distinct number of a/bs an id has:

SELECT *
FROM   mytable
WHERE  id IN (SELECT   id
              FROM     mytable
              WHERE    value in ('a', 'b')
              GROUP BY id
              HAVING   COUNT(DISTINCT value) = 2)
nr: #2 dodano: 2017-11-30 20:11
select id, value
from t
where id in (
    select id
    from t
    group by id
    having bool_or(value = 'A') and bool_or(value = 'B')
)

or

select id, value
from t t0
where 
    exists (
        select 1
        from t
        where id = t0.id and value = 'A'
    ) and
    exists (
        select 1
        from t
        where id = t0.id and value = 'B'
    ) 
Source Show
◀ Wstecz