CODEWITHSUNDEEP
sqldb2
NWilkie
NWilkie

Reputation: 156

Selecting values from one column based on a different columns value

Having trouble stating my problem succinctly here, so I'll just give an example.
Let's say I have a DB2 table about Students:

Name           Class          Grade
Billy J        Econ           A
Sarah S        Maths          B
Greg X         Computes       A-
Billy J        Maths          D
Greg X         Maths          C+

And I want to retrieve those students that are in both Econ and Maths, and display the information thusly:

Name       Maths Grade       Econ Grade
Billy J    D                 A

How in the world can I accomplish this?

Upvotes: 1

Views: 1396

Answers (2)

therealmitchconnors
therealmitchconnors

Reputation: 2758

SELECT * from Students
where id in 
(SELECT id from Students where Class = 'Econ')
AND id in
(SELECT id from Students where Class = 'Math');

Upvotes: 0

Larry Lustig
Larry Lustig

Reputation: 50970

This solution will solve the problem for the two classes you named:

SELECT Name, Math.Grade AS MathsGrade, Econ.Grade AS EconGrade
  FROM Students Math INNER JOIN Students Econ ON Math.Name = Econ.Name
  WHERE Math.Class = 'Maths' AND Econ.Class = 'Econ'

The only thing that this solution doesn't do is include the spaces in your derived column names. You can do that by writing Maths Grade and Econ Grade in whatever characters DB2 uses for identifier quotes.

To be included students must have both a Maths and an Econ grade.

Upvotes: 5

Related Questions