How to write an SQL query using GROUP BY for row analysis
By Rudy Limeback, SQL Consultant, r937.com
SearchOracle.com
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?
Oracle White Papers: Fusion Middleware