SQL Server matching all rows from Table1 with all rows from Table2

Sarah Szabo 发表于 Dev

TMMDev

someone please help me with this query, i have 2 tables

Employee

EmployeeID   LanguageID
1            1
1            2
1            3
2            1
2            3
3            1
3            2
4            1
4            2
4            3

Task

TaskID   LanguageID   LangaugeRequired
1        1            1
1        2            0
2        1            1
2        2            1
2        3            1
3        2            0
3        3            1

LangaugeID is connected to table langauge (this table is for explaination only)

   LangaugeID   LanguageName
   1            English
   2            French
   3            Italian

is there a possilbe way to make a query which gets employees where they can speak all the languages required for each task?

for example:

Task ID 1 requires only LanguageID = 1, so the result should be EmployeeID 1,2,3,4
Task ID 2 requires all 3 languages, so the result should be EmployeeID 1,4
Task ID 3 requires only LanguageID = 3, so the result should be EmployeeID 1,2,4

Iłya Bursov

here is another variant to do this:

select t1.taskid, t2.employeeid from
(
    select a.taskid, count(distinct a.languageid) as lang_cnt
    from
    task as a
    where a.LangaugeRequired=1
    group by a.taskid
) as t1
left outer join
(
    select a.taskid, b.employeeid, count(distinct b.languageid) as lang_cnt
    from
    task as a
    inner join
    employee as b
    on (a.LangaugeRequired=1 and a.languageid=b.languageid)
    group by a.taskid, b.employeeid
) as t2
on (t1.taskid=t2.taskid and t1.lang_cnt=t2.lang_cnt)
###
here you can insert where statement, like:
where t1.taskid=1 and t2.employeeid=1
if such query returns row - this employee can work with this task, if no rows - no
###
order by t1.taskid, t2.employeeid

as you see, this query creates two temporary tables and then joins them.

first table (t1) calculates how many languages are required for each task

second table (t2) finds all employees who has at least 1 language required for task, groups by task/employee to find how many languages can be taken by this employee

the main query performs LEFT JOIN, as there can be situations when no employees can perform task

here is the output:

task    employee
1       1
1       2
1       3
1       4
2       1
2       4
3       1
3       2
3       4

update: simpler, but less correct variant, because it will not return tasks without possible employees

select a.taskid, b.employeeid, count(distinct b.languageid) as lang_cnt
from
task as a
inner join
employee as b
on (a.LangaugeRequired=1 and a.languageid=b.languageid)
group by a.taskid, b.employeeid
having count(distinct b.languageid) = (select count(distinct c.languageid) from task as c where c.LangaugeRequired=1 and c.taskid=a.taskid)

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。