CODEWITHSUNDEEP
sqloracle-database
Danny
Danny

Reputation: 7518

Find foreign key matching multiple row values

I have a table design like so

person_id   |   department
1           |   a
1           |   b
2           |   a
2           |   c
3           |   b
3           |   a

and would like to have a query return all of the person_id's that belong to both department a and b. I figured it would be something along the lines of

select person_id from table group by person_id having ....

but can't quite figure it out. Does anyone know how I can do this?

Edit: I figured out I can do the following. But it seems like a hack.

select person_id from table where department='A' or department='B' 
     group by person_id having count(1) > 1

Upvotes: 2

Views: 2298

Answers (2)

Xavi López
Xavi López

Reputation: 27880

You can also achieve this with a subquery: 

SELECT person_id 
FROM table t1 
WHERE t1.department = 'a' 
    AND EXISTS (SELECT 1 
            FROM table t2 
            WHERE t2.department = 'b' 
                AND t1.person_id = t2.person_id`)

Upvotes: 1

MatBailie
MatBailie

Reputation: 86735

SELECT
  person_id
FROM
  yourTable
WHERE
     department = 'a'
  OR department = 'b'
GROUP BY
  person_id
HAVING
  COUNT(DISTINCT department) = 2

Note: The DISTINCT is only needed if a person can be a member of the same department more than once.

Upvotes: 4

Related Questions