If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > ANSI SQL > SQL Select query (with many-to-many)

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Posts: 51
Re: SQL Select query (with many-to-many)

Quote:
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'
)
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Posts: 51
Quote:
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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')
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Posts: 51
Quote:
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
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #9 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Posts: 4
Thanks.

It works a treat.


Joseppic.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On