CODEWITHSUNDEEP
mysqlsqljoin
dalatron
dalatron

Reputation: 82

MySQL: Select all records where it matches multiple IDs

This is based on a question that was answered here.

I need to export relational records to a flat CSV file. It displays a list of employees that match two skills. The export needs to have a separate line for each skill. For example:

EMPID  NAME               SKILLNAME  LEVEL
1      Fred Flintstone    PHP        Basic
1      Fred Flintstone    JQuery     Advanced
3      Steve Jobs         PHP        Basic
3      Steve Jobs         JQuery     Advanced

Here's the tables/records and a modified version of the previous answer: SQL Fiddle

I have tried a few variations but no luck.

Thanks

Upvotes: 0

Views: 2084

Answers (4)

dalatron
dalatron

Reputation: 82

Thanks to Fabio, I was able to find the solution based on his answer. The final query is:

SELECT a.Emp_ID, b.Name, c.Name as skname, a.Level
FROM Emp_Skills a
LEFT JOIN Employee b
ON a.Emp_ID = b.ID
LEFT JOIN Skill c
ON a.Skill_ID = c.ID
WHERE ( 
       SELECT count(d.Emp_Id) as total 
       FROM Emp_Skills d
       WHERE d.Emp_id = a.Emp_id 
       GROUP BY Emp_id 
       AND d.Skill_ID IN ('1', '2')
      ) = 2
AND a.Skill_ID IN ('1', '2')
ORDER BY a.Emp_ID ASC

I have added 

AND d.Skill_ID IN ('1', '2')
so it only finds employees with the selected skills. I am not concerned with the skills I am searching for, not the total number of skills an employee has. I have also changed '> 1' to '= 2'. That will change with the number of skills being search for. So if I am looking for an employee with 4 particular skills, it would be '= 4'.

Thanks everyone.

Upvotes: 0

Brian DeMilia
Brian DeMilia

Reputation: 13248

select a.emp_id, b.name, c.name as skname, a.level
  from emp_skills a
  join employee b
    on a.emp_id = b.id
  join skill c
    on a.skill_id = c.id
 where b.id in (select emp_id from emp_skills where skill_id = '1')
   and b.id in (select emp_id from emp_skills where skill_id = '2')
   and a.skill_id in ('1', '2')

*SQL FIDDLE: http://sqlfiddle.com/#!2/35fe1/40/0 *

Upvotes: 0

Fabio
Fabio

Reputation: 23490

There you go with a subquery

SELECT a.Emp_ID, b.Name, c.Name as skname, a.Level
FROM Emp_Skills a
LEFT JOIN Employee b
ON a.Emp_ID = b.ID
LEFT JOIN Skill c
ON a.Skill_ID = c.ID
WHERE ( 
       SELECT count(d.Emp_Id) as total 
       FROM Emp_Skills d
       WHERE d.Emp_id = a.Emp_id 
       GROUP BY Emp_id
      ) > 1
ORDER BY a.Emp_ID ASC

Sql fiddle here

Based on op new info

SELECT a.Emp_ID, b.Name, c.Name as skname, a.Level
FROM Emp_Skills a
LEFT JOIN Employee b
ON a.Emp_ID = b.ID
LEFT JOIN Skill c
ON a.Skill_ID = c.ID
WHERE ( 
       SELECT count(d.Emp_Id) as total 
       FROM Emp_Skills d
       WHERE d.Emp_id = a.Emp_id 
       GROUP BY Emp_id
      ) > 1
AND a.Skill_ID IN ('1', '2')
ORDER BY a.Emp_ID ASC

New sql fiddle here

Upvotes: 0

Gidon Wise
Gidon Wise

Reputation: 1916

You need a sub query to find the employees that have more than 2 skills. And then you just list the details of those skills by joining it to the Emp_skills table.

SELECT Emp_SKills.*
FROM Emp_Skills
JOIN (
SELECT COUNT(*) as skills, Emp_ID
FROM Emp_Skills
GROUP BY Emp_ID
HAVING skills > 1
) as mult ON mult.Emp_ID = Emp_Skills.Emp_ID
ORDER BY Emp_SKills.Emp_ID

Upvotes: 1

Related Questions