Help needed with a query that must match many items 2

[Jocsta]

I am trying to come up with a query similar to an IN (1,2,3) statement, but that MUST match all criteria in a one to many relationship, rather than just some of.

I have a working example of what I am looking for, but it just seems wrong to me, and as the queries are built up dynamically (and part of a much larger query), it could run into problems with large amounts of criteria.....

The example returns the id's of all people who have ALL the required skills..

SELECT P.PERSON_ID
FROM PEOPLE P
WHERE P.PERSON_ID in (
SELECT DISTINCT s1.PERSON_ID
FROM SKILLS s1
INNER JOIN SKILLS s2 ON s1.PERSON_ID = s2.PERSON_ID
INNER JOIN SKILLS s3 ON s1.PERSON_ID = s3.PERSON_ID
WHERE s1.SKILL = 'Skill 1'
AND s2.SKILL = 'Skill 2'
AND s3.SKILL = 'Skill 3'
)


This seems to be a case of overkill, so is there a simpler, and more scaleable way to do what I'm looking for?? I just can't see a different way of doing it, but am far from being any kind of SQL expert...

Thanks,
<< JOC >>

 

[JohnDTampaBay]

Assuming that Skills.Person_ID is a foreign key to People.Person_ID, you can eliminate the outer query.

SELECT DISTINCT s1.PERSON_ID
   FROM   SKILLS s1
      INNER JOIN SKILLS s2 ON s1.PERSON_ID = s2.PERSON_ID
      INNER JOIN SKILLS s3 ON s1.PERSON_ID = s3.PERSON_ID
   WHERE  s1.SKILL = 'Skill 1'
   AND    s2.SKILL = 'Skill 2'
   AND    s3.SKILL = 'Skill 3'

This only works for 3 specific skills. Are the desired skills being passed as variables, like @Skill1, @Skill2, etc.? What is the maximum number of possible skills for querying?

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[Jocsta]

Thanks for the reply...
The example I provided was just made up to try to clearly explain the solution I've got so far, and isn't really indicative of the query as a whole. But I'll stick to the &quot;people&quot; / &quot;skills&quot; example for descriptive purposes.

The queries themselves are all dynamically created at runtime (I know-horible, but the reasons why are far too complicated to go into here).

There is theoretically no upper limit on the number of &quot;skills&quot; that could be included (which is why I'm worried about possibly running out of columns, especially as there may be any number of columns in the tables being joined together).

Thanks,

<< JOC >>

 

[swampBoogie]

This query is somewhat simpler to maintain if you need to add/withdraw skills

SELECT PERSON_ID
   FROM   SKILLS
   WHERE  SKILL in ('Skill 1','Skill 2','Skill 3')
 group by person_id
 having count(distinct skill) = 3

 

[JohnDTampaBay]

Nicely done, swampBoogie. It will still be a dynamic query, but it is much easier. Only the IN string of skills and the count are dynamic. Sveeeet!


--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[Jocsta]

Fantastic... seems to be exactly what I was looking for. I had tried to come up with something similar using count myself, but just couldn't get it to work in SQLServer.

Never thought of putting it in a having clause, as I don't think I've ever used one before....

Thanks again,

<< JOC >>