CODEWITHSUNDEEP
sqlsql-serverselectjoin
HSharma
HSharma

Reputation: 403

SQL server matching two table on a column

I have two tables one storing user skills another storing skills required for a job. I want to match how many skills a of each user matches with a job. The table structure is

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 was trying to have comma separated skills and compare but these can be in different order.

Edit All the answers here are excellent. The result I am looking for is matching all jobs with all users as later on I will match other properties as well.

Upvotes: 4

Views: 808

Answers (3)

Mureinik
Mureinik

Reputation: 311188

You could join the tables by the skill columns and count the matches:

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:
IF you want to also show users and jobs that have no matching skills, you can use a full outer join instead.

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

EDIT 2:
As Jiri Tousek commented, the above query will produce nulls where there's no match between a user and a job. If you want a full Cartesian products between them, you could use (abuse?) the cross join syntax and count how many skills actually match between each user and each job:

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

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1269673

If you want to match all users and all jobs, then Mureinik's otherwise excellent answer is not correct.

You need to generate all the rows first, which I would do using a cross join and then count the matching ones:

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;

Note: This assumes the existence of a users and a jobs table. You can of course generate these using subqueries.

Upvotes: 2

Nolan Shang
Nolan Shang

Reputation: 2328

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

Upvotes: 2

Related Questions