CODEWITHSUNDEEP
sqlsql-order-by
koryakinp
koryakinp

Reputation: 4125

Order table by number of matches

I have following database schema:

Applicants table:

applicant_id  | name 
--------------|----- 
      1       | Bill  
      2       | Tom  
      3       | Sam   
      4       | Alex

Skills table:

skill_id  | skill_name 
----------|-----------
    1     |    C++  
    2     |    java  
    3     |    html   
    4     |    PHP

ApplicantSkills (many-to-many between applicants and skills)

applicant_id  | skill_id 
--------------|-----------
      1       |     1  
      2       |     1  
      2       |     2   
      2       |     3  
      3       |     1  
      3       |     2

I want to select all applicants, but order them by number of matches with particular list of skills.

For example, if my list of skills is C++, java and html I want them to be ordered like this:

 name | matches
------|--------- 
 Tom  |    3   
 Sam  |    2 
 Bill |    1
 Alex |    0

Any suggestions would be greatly appreciated.

Upvotes: 2

Views: 70

Answers (2)

koryakinp
koryakinp

Reputation: 4125

The solution proposed by MM93 was almost correct. There was missing GROUP BY clause in inner query and IFNULL in main query.

In case someone is interesting here is correct solution:

SELECT a.applicant_name, IFNULL(sg.matches, 0)
FROM Applicants a
LEFT OUTER JOIN (
    SELECT a.applicant_id, count(s.skill_id) as matches
    FROM Applicants a
        JOIN ApplicantSkills aps ON aps.applicant_id = a.applicant_id
        JOIN Skills s ON aps.skill_id = s.skill_id
    WHERE s.skill_name IN ('C++','java','html')
        GROUP BY(a.applicant_id)
) sg ON a.applicant_id = sg.applicant_id
ORDER BY sg.matches DESC

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

  • Left Outer Join : To get all the applicants from applicants table even though he does not have any skill(Alex)
  • Count Aggregate : To Count the skill of each applicants
  • Order by : To order the result based on number of skills that applicants possess

Try this

SELECT          a.NAME , 
                Count(skill_id) AS matches 
FROM            applicants A 
LEFT OUTER JOIN 
                (SELECT     applicant_id, 
                            s.skill_id 
                 FROM       applicantskills 
                 INNER JOIN skills s 
                 ON         a.skill_id = s.skill_id 
                 WHERE      skill_name IN ('C++','java','html')) ask 
ON a.applicant_id= ask.applicant_id 
ORDER BY   matches DESC

Upvotes: 2

Related Questions