CODEWITHSUNDEEP
sqlselect
Boss Liao
Boss Liao

Reputation: 13

SQL select values based on other two columns

If I have a table like:

Name Subject Score
Tom Math 90
Tom Physics 70
Jerry Math 70
Jerry Physics 90
Bob Math 80
Bob Physics 80

How to write SQL code to find the names who do better at math rather than physics?

The output should be:

Name
Tom

Upvotes: 1

Views: 76

Answers (3)

Md. Tarikul Islam Soikot
Md. Tarikul Islam Soikot

Reputation: 595

You can check my solution in db-fiddle with data.

In short, You can apply below query

SELECT 
      s1.Name
      FROM TableName s1, TableName s2
WHERE s1.Name = s2.Name 
AND s1.Subject = 'Math' AND s2.Subject = 'Physics' 
AND s1.Score > s2.Score

Upvotes: 0

Stu
Stu

Reputation: 32599

Many ways. One way is to say you want to know which names have maths who also have subject physics that exists with a lower score, so

select *
from t
where subject='maths' and exists (
  select * from t t2 where t2.name=t.name 
    and t2.subject='physics'
    and t2.score < t.score
)

Upvotes: 1

Mureinik
Mureinik

Reputation: 311308

I'd join two queries on the table, one for math and one for physics:

SELECT m.name
FROM   (SELECT name, score
        FROM   mytable
        WHERE  subject = 'Math') m
JOIN   (SELECT name, score
        FROM   mytable
        WHERE  subject = 'Physics') p ON m.name = p.name AND m.score > p.score

Upvotes: 3

Related Questions