Help - alternative to correlated sub query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help - alternative to correlated sub query

Author

Topic

tiamat
Starting Member

3 Posts

Posted - 2008-01-21 : 06:23:24

Hi,

I am using SQL server 2005. To summarise the problem :

I have two tables - One with a range of skill ids that I would like to use to filter the second table for a list of person Ids. I only want to retrieve the person ids where the second table has entries for ALL the range of skills ids in the first table.

To illustrate, I have a table with 'n' number of skill Ids I.e.

Table SkillRange has values :

Id
5
10
11

What I want is to get the person id values in another table where a person has all the skill values that exist in table 1 (SkillRange).

I.e, table PersonSkills could have:

personId, skillId
1, 5
1, 10
1, 11

Because personId 1 has entries for all values in SkillRange i.e. 5, 10, 11 then I would like personId 1 to be returned.

Expanding the example, with values :

personId, skillId
1, 5
1, 10
1, 11

2, 3
2, 5

3, 5
3, 10
3, 11

4, 10
4, 11

Here I would want personIds 1 and 3 to be returned because they are the only Ids that have all the skill ids that are in the SkillRange table.

Now, I believe I could do this in a nested correlated subquery. However, I can't do this if the number of Id's in the SkillRange table is variable I.e. I dont know the nesting level.

Could anyone help - I am wondering if I have to write a function. But at this point I'm lost as to how to approach the issue.

Note :

SELECT personId
FROM PersonSkills
WHERE skillId IN (SELECT Id FROM SkillRange )

is an OR operation, what I am trying to achieve is an AND operation.

Many thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-21 : 06:33:55

[code]-- Prepare sample data
DECLARE @Skills TABLE (ID INT)

INSERT @Skills
SELECT 5 UNION ALL
SELECT 10 UNION ALL
SELECT 11

DECLARE @Persons TABLE (PersonID INT, SkillID INT)

INSERT @Persons
SELECT 1, 5 UNION ALL
SELECT 1, 10 UNION ALL
SELECT 1, 11 UNION ALL
SELECT 2, 3 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 3, 5 UNION ALL
SELECT 3, 10 UNION ALL
SELECT 3, 11 UNION ALL
SELECT 4, 10 UNION ALL
SELECT 4, 11

-- Show the expected output for any number of record in @Skills table
SELECT x.PersonID
FROM (
SELECT DISTINCT PersonID
FROM @Persons
) AS x
CROSS JOIN @Skills AS s
LEFT JOIN @Persons AS p ON p.PersonID = x.PersonID
AND p.SkillID = s.ID
GROUP BY x.PersonID
HAVING MAX(CASE WHEN p.PersonID IS NULL THEN 1 ELSE 0 END) = 0[/code]

E 12°55'05.25"
N 56°04'39.16"

tiamat
Starting Member

3 Posts

Posted - 2008-01-21 : 07:01:04

Peso!

Thats exactly what I was after, you won't believe how long I've been dwelling on it, so, thank you very much indeed

ns_nataly
Starting Member

13 Posts

Posted - 2008-01-21 : 07:02:41

-- for tables given above
declare @scNN int
select @scNN= count(distinct Id ) from @Skills

SELECT personId
FROM @Persons ps inner join @Skills sr
on sr.id=skillId
group by personId
having count(distinct skillId)=@scNN

Natalia

tiamat
Starting Member

3 Posts

Posted - 2008-01-21 : 07:10:18

Nice approach Natalia

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources