CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Lucky Makhado
Lucky Makhado

Reputation: 107

I want opposite results

I have two tables I want to display student without results

Student tables

Sname  snumber
A              1
B              2
C              3
G              4

And results table

Snumber  marks
1                 90
4                 60

I have the following code

Select student.sname from student
Join results
On student.snumber=results.snumber

Its showing students With results. I want student without results which is B and C

Upvotes: 0

Views: 40

Answers (2)

Jatin Patel
Jatin Patel

Reputation: 2104

Try this,

Select student.sname 
from student
LEFT Join results
    On student.snumber=results.snumber
WHERE results.snumber is null

If you want all the students then remove the where clause.

Upvotes: 3

Paweł Dyl
Paweł Dyl

Reputation: 9143

After LEFT JOIN solution (@Jatin Patel), you can see NOT EXISTS one:

SELECT *
FROM Students S
WHERE NOT EXISTS(SELECT * FROM results R WHERE S.snumber=R.snumber)

Upvotes: 2

Related Questions