How to get id from rows with unique ids?

Question

I have a table like this:

id | customer_id | code
-----------------------
1  |  1          | A
2  |  1          | B
3  |  2          | A
4  |  2          | D
5  |  3          | B
6  |  3          | C
6  |  3          | D

I need an SQL query that returns all customer ids with codes equal to A and B. In the above data, this would only be customer_id 1.

If the codes were each their own column, this would be a straightforward query: SELECT DISTINCT customer_id FROM tablename WHERE code = A AND code = B. I can't seem to craft it over multiple rows, however.

Answer 1

You can use a GROUP BY customer_id with a HAVING clause:

select customer_id
from yourtable
where code in ('A', 'B')
group by customer_id
having count(distinct code) = 2

See SQL Fiddle with Demo

If you want to return more data from your table, then you can expand the query to:

select *
from yourtable t1
where exists (select customer_id
              from yourtable t2
              where code in ('A', 'B')
                and t1.customer_id = t2.customer_id
              group by customer_id
              having count(distinct code) = 2)

See SQL Fiddle with Demo