SQL Server matching all rows from table 1 with all rows from table2

Question

someone please help me with this question, 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 the langauge table (this table is for explanation only)

   LangaugeID   LanguageName
   1            English
   2            French
   3            Italian

Is there a way to make a request that employees receive, where they can speak all the languages ​​needed for each task?

eg:

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

Answer 1

:

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

, , .

(t1) ,

(t2) , , , /, , .

LEFT JOIN, ,

:

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

update: , , .

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)

Answer 2

, NOT EXISTS

,

SELECT t1.EmployeeId, t2.TaskId
FROM (
    SELECT DISTINCT EmployeeID
    FROM Employee
) t1 , (
    SELECT DISTINCT TaskID
    FROM Task
) t2
WHERE NOT EXISTS (   
    SELECT 1 FROM Task t
    LEFT JOIN Employee e 
        ON e.EmployeeID = t1.EmployeeID
        AND e.LanguageID = t.LanguageID
    WHERE t.TaskID = t2.TaskID
    AND LanguageRequired = 1
    AND e.EmployeeID IS NULL
)

http://www.sqlfiddle.com/#!6/e3c78/1

Answer 3

You can use logic Jointo get the result, for example:

SELECT a.EmployeeID FROM Employee a, Task b WHERE b.LanguageRequired == a.LanguageID;