MySQL: Select all records where it matches multiple IDs

Question

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

I had a related problem which has already been answered. The code I am working with is a modified version of that. — dalatron, Feb 18 '14 at 00:48
I've tried removing the GROUP BY which gave me no results. Just using the nested query - http://sqlfiddle.com/#!2/35fe1/32 - gives all records, even if the employee does not have both skills. — dalatron, Feb 18 '14 at 01:02

score 1 · Answer 1 · answered Feb 18 '14 at 00:56

1

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

answered Feb 18 '14 at 00:56

Gidon Wise

1,896
1
11
11

The number of skills would change each time it is used. It could be 1 or 20. – dalatron Feb 18 '14 at 01:11
IF you only want employees that have exactly 2 skills then change "HAVING > 1" into "HAVING = 2". If you want to only report on 2 skills then you can join with a MAX() id and a MIN() or some other arbitrary set of 2. – Gidon Wise Feb 18 '14 at 02:56
Thanks Gordon. I was actually searching for employees with all the selected skills. Fabio has point me in the right direction and I was able to work it our from there. See my answer below. – dalatron Feb 18 '14 at 03:47

Fabio · Answer 2 · 2014-02-18T01:18:36.050

0

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

edited Feb 18 '14 at 01:18

answered Feb 18 '14 at 00:45

Fabio

23,183
12
55
64

Thanks. I need to specify the ID of the skills. In the live db, I have over 100 skills and you can search for employees with a select number of skills. The number of skills changes for each search. – dalatron Feb 18 '14 at 01:08
What do you mean? Can you show an example? So i can fix this for you – Fabio Feb 18 '14 at 01:11
Sorry, the query does not show up in SQL Fiddle. Here it is: SELECT EmpId, Name, SkillName, Level FROM ( SELECT em.ID as EmpId, em.Name, es.Level as Level, sk.Name as SkillName, es.ID as SkillID FROM Employee em INNER JOIN Emp_Skills es ON es.Emp_ID = em.ID INNER JOIN Skill sk ON sk.ID = es.Skill_ID WHERE es.Skill_ID IN ('1', '2') ) X GROUP BY EmpID, Name HAVING COUNT(DISTINCT SkillID) = 2; – dalatron Feb 18 '14 at 01:14
Thanks for your help Fabio. I was able to work out the solution based on your answer. See below… – dalatron Feb 18 '14 at 03:45

score 0 · Answer 3 · answered Feb 18 '14 at 01:55

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 *

score 0 · Answer 4 · answered Feb 18 '14 at 03:44

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.

MySQL: Select all records where it matches multiple IDs

4 Answers4