SQL Row Comparison Query

[fishboy76]

Hi Everyone. I am new to SQL and trying to write a query against the following results table that would allow me to list the subjects where the student Mary has got a higher score than Tom.

 

Subject Student Teacher Score Maths Tom Anderson 67 Maths Mary Anderson 68 English Tom Lewis 55 English Mary Lewis 44 French Tom Joubert 87 French Mary Joubert 76 Geography Tom Arnold 76 Geography Mary Arnold 82

 

I believe I should be using the join clause but am unable to get this to work.

 

Thanks

[Barand]

You need to join the table to itself on the subject column then apply your search criteria in a WHERE clause

 

 

SELECT a.subject 
FROM score a
  INNER JOIN score b USING (subject)
WHERE a.student = 'Mary'
  AND b.student = 'Tom'
  AND a.score > b.score;

 

 

[fishboy76]

Thanks Barand. That worked perfectly.