CODEWITHSUNDEEP
sqlsql-servert-sql
4thSpace
4thSpace

Reputation: 44332

Finding multiple ID matches in a single table

In SQL Server (2008), I'd like to find all employees in a single table that have skillids of 3, 4, 5 or 4, 5, 6. They need to have one of those sets. Just having skillid=4, for example, should not produce a match. How do I construct this type of query?

An example table:

pkid, empid, skillid
 1     2       3
 2     2       4
 3     2       5
 4     5       6

In the above example, empid=2 is a match for the set 3,4,5. empid=5 is not.

Upvotes: 1

Views: 3325

Answers (5)

Nicholas Carey
Nicholas Carey

Reputation: 74297

My reading of your original question is that you want to find those employees who meet either of the following criteria:

  • They have skill ids 3 AND 4 AND 5
  • They have skill ids 4 AND 5 AND 6

(or both). If this case, you'll want to do something like this using correlated subqueries:

select *
from employee e
where (     exists ( select * from employee_skill es3 on es3.empid = e.empid and es3.skillid = 3 )
        and exists ( select * from employee_skill es3 on es3.empid = e.empid and es3.skillid = 4 )
        and exists ( select * from employee_skill es3 on es3.empid = e.empid and es3.skillid = 5 )
      )
   OR (     exists ( select * from employee_skill es3 on es3.empid = e.empid and es3.skillid = 4 )
        and exists ( select * from employee_skill es3 on es3.empid = e.empid and es3.skillid = 5 )
        and exists ( select * from employee_skill es3 on es3.empid = e.empid and es3.skillid = 6 )
      )

However, Since your two target skill sets { 3, 4, 5 } and { 4, 5, 6 } have the common subset, { 4 , 5 }, we can simplify. Refactoring, we get

select *
from employee e
where exists ( select * from employee_skill es3 on es3.empid = e.empid and es3.skillid =    4       )
  and exists ( select * from employee_skill es3 on es3.empid = e.empid and es3.skillid =    5       )
  and exists ( select * from employee_skill es3 on es3.empid = e.empid and es3.skillid in ( 3 , 6 ) )

Another technique would be to use left join:

select *
from employee e
left join employee_skill es3 on es3.empid = e.empid and es3.skillid = 3
left join employee_skill es4 on es4.empid = e.empid and es4.skillid = 4
left join employee_skill es5 on es5.empid = e.empid and es5.skillid = 5
left join employee_skill es6 on es6.empid = e.empid and es6.skillid = 6
where es4.empid is not null
  and es5.empid is not null
  and (    es3.empid is not null
        OR es6.empid is not null
      )

This latter approach using left join contains an implicit assumption that a particular employee/skill combination is unique within the data model. If that is not the case, then this approach would require the use of select distinct lest you get duplicate rows in your result set.

Upvotes: 2

Arnoud
Arnoud

Reputation: 36

empid IN (3,4,5) is shorthand for empid = 3 or empid = 4 or empid = 5, so empid IN 3,4,5, OR empid IN (4,5,6) is the same as empid IN (3,4,5,6). But, we need to avoid counting people with 3,5,6, or 3,4,6. You could do something like this:

SELECT empid, 345 AS skillset
FROM your_table
WHERE skillid IN (3,4,5)
GROUP BY empid
HAVING COUNT(DISTINCT skillid) = 3
UNION ALL
SELECT empid, 456 AS skillset
FROM your_table
WHERE skillid IN (4,5,6)
GROUP BY empid
HAVING COUNT(DISTINCT skillid) = 3

You can put a SELECT around this if you care to see which empid's have both sets, etc.

Upvotes: 0

Hannah Vernon
Hannah Vernon

Reputation: 3472

Both previous answers suffer from the inability to differentiate between set membership in (1,2,6) or (1,5,6), or (2,5,6) etc. The result has to be only show an empid that is member of ALL of 1,2,3 OR 4,5,6.

Try:

    create table Table1 (pkid int constraint PK_Table1 primary key, empid int, skillid int)

    insert into table1 values (1,2,1)
    insert into table1 values (2,2,2)
    insert into table1 values (3,2,3)
    insert into table1 values (4,3,1)

    SELECT empid 
    FROM (
        SELECT empid, sum(t.s1) as s1, sum(t.s2) as s2, sum(t.s3) as s3, sum(t.s4) as s4, sum(t.s5) as s5, sum(t.s6) as s6
        FROM
        (
            select empid, 1 s1, 0 s2, 0 s3, 0 s4, 0 s5, 0 s6
            from table1
            where skillid = 1 
            union all
            select empid, 0, 1, 0, 0, 0, 0
            from table1
            where skillid = 2 
            union all
            select empid, 0, 0, 1, 0, 0, 0
            from table1
            where skillid = 3 
            union all
            select empid, 0, 0, 0, 1, 0, 0
            from table1
            where skillid = 4 
            union all
            select empid, 0, 0, 0, 0, 1, 0
            from table1
            where skillid = 5 
            union all
            select empid, 0, 0, 0, 0, 0, 1
            from table1
            where skillid = 6 
        ) t
        GROUP BY t.empid
    ) tt 
    WHERE (tt.s1 = 1 and tt.s2 = 1 and tt.s3 = 1) or (tt.s4 = 1 and tt.s5=1 and tt.s6=1)

Upvotes: 1

juergen d
juergen d

Reputation: 204794

Try

select pkid, empid
from your_table
where skillid in (3,4,5) or skillid in (4,5,6) 
group by pkid, empid
having count(distinct skillid) = 3

Upvotes: 1

Taryn
Taryn

Reputation: 247730

You will need to use a GROUP BY and a HAVING clause on the query:

select empid
from t1
where skillid in (3, 4, 5) 
    or skillid in (4, 5, 6) 
group by empid
having count(distinct skillid) = 3

See SQL Fiddle with Demo

Upvotes: 4

Related Questions