Ask the Expert

How to write an SQL query using GROUP BY for row analysis

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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: