Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    SQL Select query (with many-to-many)

    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.

  2. #2
    Join Date
    Jan 2004
    Posts
    51

    Re: SQL Select query (with many-to-many)

    Originally posted by joseppic
    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'
    )

  3. #3
    Join Date
    Dec 2003
    Posts
    454
    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

  4. #4
    Join Date
    Jan 2004
    Posts
    51
    Originally posted by gyuan
    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

  5. #5
    Join Date
    Jan 2004
    Posts
    4
    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.

  6. #6
    Join Date
    Dec 2003
    Posts
    454
    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')

  7. #7
    Join Date
    Jan 2004
    Posts
    51
    Originally posted by joseppic
    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.
    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

  8. #8
    Join Date
    Jan 2004
    Posts
    4
    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.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    simpler way:
    Code:
    select Person.name 
      from Person
    inner
      join JoinPersonSkill
        on Person.ID = JoinPersonSkill.PersonID
    inner
      join Skill
        on JoinPersonSkill.SkillID = Skill.ID
     where Skill.name 
           in ( 'A' , 'B' )     -- list of multiple sk1llz0rz
    group 
        by Person.name 
    having count(*) = 2     -- how many are required
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2004
    Posts
    4
    Thanks.

    It works a treat.


    Joseppic.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •