CODEWITHSUNDEEP
mysql
Ruben_PH
Ruben_PH

Reputation: 1812

SELECT DISTINCT from multiple row and DO NOT RETURN if containing row with undesired value

ID_Number|Subject_Code|Grade
000001   |Math        |Dropped
000001   |English     |Passed
000001   |Physics     |Passed
000002   |Math        |Passed
000002   |English     |Passed
000002   |Physics     |Passed
000003   |Math        |Passed
000003   |English     |Passed
000003   |Physics     |Passed

Now I want to SELECT DISTINCT ID_Number wherein all of the subject_code should have grade as Passed, the query should return 000002 and 000003 only since 000001 has 1 Dropped subject, other grade not to include is 'Withdrawn' and 'Failed'.

Upvotes: 1

Views: 59

Answers (1)

Joe G Joseph
Joe G Joseph

Reputation: 24046

SELECT DISTINCT ID_Number
FROM   <table>
WHERE  ID_Number not in(select ID_Number from <table> where Grade <> 'Passed')

SQL fiddle demo

Upvotes: 3

Related Questions