Retrieving rows that have multiple association entries

Question

Suppose I have an ASOC-type table with the following entries:

+----------+----------+
| PERSON   | SKILL    |
+----------+----------+
| Bob      | Java     |
| Bob      | Database |
| Sarah    | Java     |
| Sarah    | HTML     |
| Jane     | Java     |
| Jane     | HTML     |
| Jane     | Database |
+----------+----------+

I want a query that will return the list of persons who have both the Java and Database skills, i.e. Bob and Jane.

If I run a query like:

SELECT PERSON
FROM   PERSON_SKILLS_ASOC
WHERE  SKILL = 'Java'
OR     SKILL = 'Database'

I will get Sarah as well, who doesn't qualify for the position I'm trying to fill. Do I need to do some kind of union/intersection query?

I'm on MySQL. Thanks in advance.

Change `OR` to `AND` as you want a person that posses both skills and not one or another. — Prix, Jul 05 '13 at 15:03
WHERE skill in ('java','database') GROUP BY person HAVING COUNT(*) = 2 -- where '2' is the number of items in the IN() argument — Strawberry, Jul 05 '13 at 15:09

ntalbs · Accepted Answer · 2013-07-05T15:19:06.387

1

select a.person
from (select person from person_skills_asoc where skill = 'Java') a
     (select person from person_skills_asoc where skill = 'Database') b
where a.person = b.person;

or

select a.person
from person_skills_asoc a, person_skills_asoc b
where a.person = b.person
  and a.skill = 'Java'
  and b.skill = 'Database';

edited Jul 05 '13 at 15:19

answered Jul 05 '13 at 15:06

ntalbs

28,700
8
66
83

score 0 · Answer 2 · answered Jul 05 '13 at 15:06

I've not worked in MySQL for years, so my syntax may be off slightly, but the gist is EXISTS:

SELECT
    PSA1.Person
FROM
    PERSON_SKILLS_ASOC AS PSA1
WHERE
        PSA1.Skill = 'Java'
    AND EXISTS (SELECT * FROM PERSON_SKILLS_ASOC AS PSA2 WHERE PSA1.Person = PSA2.Person AND PSA2.Skill = 'Database')

You can also achieve this with a join.

score 0 · Answer 3 · edited May 23 '17 at 10:32

In SQL Server for example you could use INTERSECT, apparently that's not available in MySQL (yet?). A possible solution using a self join (check the SQLFiddle):

SELECT P1.PERSON
FROM   PERSON_SKILLS_ASOC AS P1
INNER JOIN PERSON_SKILLS_ASOC AS P2 ON P1.PERSON = P2.PERSON
WHERE  P1.SKILL = 'Java'
       AND P2.SKILL = 'Database';

There is also another nice answer on SO on alternatives for INTERSECT in MySQL here: https://stackoverflow.com/a/3201426/249353.

Kickstart · Answer 4 · 2013-07-05T15:47:27.527

0

Using joins like this:-

SELECT a.PERSON
FROM   PERSON_SKILLS_ASOC a
INNER JOIN PERSON_SKILLS_ASOC b ON a.PERSON = b.PERSON
WHERE  a.SKILL = 'Java'
AND    b.SKILL = 'Database'

Or using counts:-

SELECT PERSON
FROM   PERSON_SKILLS_ASOC a
WHERE  SKILL IN ('Java', 'Database')
GROUP BY PERSON
HAVING  COUNT(DISTINCT SKILL) = 2

edited Jul 05 '13 at 15:47

answered Jul 05 '13 at 15:33

Kickstart

21,403
2
21
33

Retrieving rows that have multiple association entries

4 Answers4