I have a table like this (two columns)
std_id skill 1 Java 1 J2EE 1 Struts 2 Java 2 J2EE 3 Java 3 J2EE 3 Struts
Now I want to retrieve student IDs (std_id's) for those students that have three skills: Java, J2EE, Struts. Each student must posses all skills, which means my answer should be as follows:
std_id 1 3
How do I write this query?
The students you're looking for are those that have all three rows in the skills table. This requires analysis of a group of rows, namely, the group of skill rows for each student. I would suggest to you that GROUP BY will be involved in the solution.
SELECT std_id FROM skills WHERE skill IN ('Java','J2EE','Struts') GROUP BY std_id HAVING COUNT(*) = 3
The WHERE clause ensures that only the three rows of skills which we're interested in are retrieved from the table for each student. The GROUP BY and HAVING clauses ensure that all three were found.
How would you modify this solution to return, say, all students who had at least two of those three skills?
This was first published in September 2008