SQL server corresponding to two tables in a column

Question

I have two tables that store user skills, other storage skills necessary for work. I want to compare how many skills and each user matches with the task. Table structure

Table1: User_Skills
| ID | User_ID | Skill    |
---------------------------
| 1  |  1      |  .Net    |
---------------------------
| 2  |  1      |  Software|
---------------------------
| 3  |  1      |  Engineer|
---------------------------
| 4  |  2      |  .Net    |
---------------------------
| 5  |  2      |  Software|
---------------------------

Table2: Job_Skills_Requirement
| ID | Job_ID | Skill     |
--------------------------
| 1  |  1      |  .Net    |
---------------------------
| 2  |  1      |  Engineer|
---------------------------
| 3  |  1      |  HTML    |
---------------------------
| 4  |  2      |  Software|
---------------------------
| 5  |  2      |  HTML    |
---------------------------

I tried to separate the skills with a comma and compare, but they can be in a different order.

Edit All the answers here are excellent. The result that I am looking for corresponds to all the work with all users, since later I will correspond to other properties.

Answer 1

, Mureinik .

, , cross join, :

select u.user_id, j.job_id, count(jsr.job_id) as skills_in_common
from users u cross join
     jobs j left join
     user_skills us
     on us.user_id = u.user_id left join
     Job_Skills_Requirement jsr
     on jsr.job_id = j.job_id and
        jsr.skill = us.skill
group by u.user_id, j.job_id;

. users a jobs. , , .

Answer 2

skill :

SELECT   user_id, job_id, COUNT(*) AS matching_skills
FROM     user_skills u
JOIN     job_skills_requirement j ON u.skill = j.skill
GROUP BY user_id, job_id

EDIT:
, , full outer join.

SELECT          user_id, job_id, COUNT(*) AS matching_skills
FROM            user_skills u
FULL OUTER JOIN job_skills_requirement j ON u.skill = j.skill
GROUP BY        user_id, job_id

2:
Jiri Tousek, null, . , (?) cross join , :

SELECT     user_id, 
           job_id,
           COUNT(CASE WHEN u.skill = j.skill THEN 1 END) AS matching_skills
FROM       user_skills u
CROSS JOIN job_skills_requirement j
GROUP BY   user_id, job_id

Answer 3

WITH User_Skills(ID,User_ID,Skill)AS(
   SELECT 1,1,'.Net' UNION ALL
   SELECT 2,1,'Software' UNION ALL
   SELECT 3,1,'Engineer' UNION ALL
   SELECT 4,2,'.Net' UNION ALL
   SELECT 5,2 ,'Software'
),Job_Skills_Requirement(ID,Job_ID,Skill)AS(
   SELECT 1,1,'.Net' UNION ALL
   SELECT 2,1,'Engineer' UNION ALL
   SELECT 3,1,'HTML' UNION ALL
   SELECT 4,2,'Software' UNION ALL
   SELECT 5,2 ,'HTML'
),Job_User_Skill AS (
   SELECT j.Job_ID,u.User_ID,u.Skill
   FROM Job_Skills_Requirement AS j INNER JOIN User_Skills AS u ON u.Skill=j.Skill
)
SELECT jus.Job_ID,jus.User_ID,COUNT(jus.Skill),STUFF(c.Skills,1,1,'') AS Skill
FROM Job_User_Skill AS jus
CROSS APPLY(SELECT ','+j.Skill FROM Job_User_Skill AS j WHERE j.Job_ID=jus.Job_ID AND j.User_ID=jus.User_ID FOR XML PATH('')) c(Skills)
GROUP BY jus.Job_ID,jus.User_ID,c.Skills
ORDER BY jus.Job_ID

Job_ID      User_ID                 Skill
----------- ----------- ----------- -------------
1           1           2           .Net,Engineer
1           2           1           .Net
2           1           1           Software
2           2           1           Software