CODEWITHSUNDEEP
mysqlsql
Steven
Steven

Reputation: 1098

Retrieving rows that have multiple association entries

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.

Upvotes: 0

Views: 86

Answers (4)

Kickstart
Kickstart

Reputation: 21513

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

Upvotes: 0

Josien
Josien

Reputation: 13867

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.

Upvotes: 0

ntalbs
ntalbs

Reputation: 29448

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';

Upvotes: 1

user565869
user565869

Reputation:

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.

Upvotes: 0

Related Questions