SQL server matching two table on a column

Question

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.

Please edit the question and show the results that you want. — Gordon Linoff
– Gordon Linoff, Commented Mar 3, 2017 at 14:40

Jiri Tousek · Accepted Answer · 2017-03-03 15:01:46Z

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

How would this work for combinations that have no skills in common?
@JiriTousek It would exclude them because of the JOIN. If you wanted to include non-matches, you would need to make it a LEFT JOIN instead of JOIN. LEFT JOIN includes rows from the table on the left side of the join that do not match any rows on the table on the right side of the join. For rows with no matches, the columns from the table on the right will be null.
@JiriTousek with the current query, users and jobs that have no matches will be omitted. If you want to show them too, you could use a full outer join instead of an inner join - see my edited answer for details.
I thought full outer join would produce rows like null, SQL developer, 1 and John, null, 1 instead of John, SQL Developer, 0. Test on Oracle seems to support this. Not sure OP needs any of that, I was just wondering how it could be done to be correct for total mismatch as well.
@JiriTousek that's a good point, thanks! Using (abusing?) a cross join seems to do the trick (see my edited answer for details).

Gordon Linoff · Accepted Answer · 2017-03-03 14:43:32Z

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.

Nolan Shang · Accepted Answer · 2017-03-03 14:18:01Z

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

Collectives™ on Stack Overflow

SQL server matching two table on a column

3 Answers 3

5 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Related