Hi, I have 3 tables, Table Person, Table Skill and Table JoinPersonSkill (many-to-many).

The idea is that a 'Person' can have many 'Skills'.

I would like to do a select where the DB is searched for Persons with
Skill 'A' AND Skill 'B'.

The syntax for the Skill A OR Skill B select is as follows:

select Person.name from Person, Skill, JoinPersonSkill
where
Person.ID = JoinPersonSkill.PersonID
and Skill.ID = JoinPersonSkill.SkillID
and Skill.name = 'A' or Skill.name = 'B' ;

How do I get a Person with Skill A AND Skill B.
Substituting the OR for an AND gives me no results.
Do I need a JOIN condition?

Cheers,
Joseppic.

Basically, this query can be written in many ways. one of the way is
select Person.name
from Person, Skill, JoinPersonSkill
where Person.ID=JoinPersonSkill.PersonId
and Skill.ID=JoinPersonSkill.SkillID
and Skill.name='A'
and exists (
select 1
from Skill a , JoinPersonSkill b
where Person.ID=b.PersonId
and a.ID=b.SkillID
and b.name='B'
)

Try this:

SELECT Person.[Name]
FROM Person INNER JOIN
(SELECT JoinPersonSkill.PersonID
FROM JoinPersonSkill INNER JOIN Skill
ON JoinPersonSkill.SkillID = Skill.SkillID
WHERE Skill.[Name] = 'A' AND Skill.[Name] = 'B'
) AS js
ON Person.PersonID = js.PersonID

Are you sure this will work?

I think Skill.[Name] = 'A' AND Skill.[Name] = 'B' will never be true?

Sushant

Thanks,

This seems a little complex - I am trying to build such a select on the fly in PHP when I have been passed an array of Skills.

Is there a simpler way? Otherwise I'll be messing with my php to form such strings.


Thanks again.

sushant is right. Skill.[Name] = 'A' AND Skill.[Name] = 'B' never returns TURE. I made a mistake.

Try this:

SELECT [Name]
FROM Person
WHERE PersonID IN
(SELECT JoinPersonSkill.PersonID
FROM JoinPersonSkill INNER JOIN Skill
ON JoinPersonSkill.SkillID = Skill.SkillID
WHERE Skill.[Name] = 'A')
AND PersonID IN
(SELECT JoinPersonSkill.PersonID
FROM JoinPersonSkill INNER JOIN Skill
ON JoinPersonSkill.SkillID = Skill.SkillID
WHERE Skill.[Name] = 'B')

Try this using =ALL (not sure if your db supports)

select a.name
from Person a, JoinPersonSkill b
where a.PersonId=b.PersonId
and b.SkillId= ALL ( /* write query to select the all skills you want */)


Sushant

Hi,

I am using Postgresql 7.3, it seems to have the ALL command available but I still cannot get it to work.

Here's my new select:

select Person.name
from Person, JoinPersonSkill, Skill

where Person.Id=JoinPersonSkill.PersonId
and JoinPersonSkill.SkillId= ALL ( select ID from Skill where Skill.name = '3D' or Skill.name = '2D' );

This still returns 0.

Any ideas?

Is an INTERSECT a better option?

Cheers,

Joseppic.

simpler way:note this can also be used to select someone with 2 out of 3 skills, and so on

very easy to extend this to any number of skills listed, and any number required

neat, eh?

Thanks.

It works a treat.


Joseppic.