CODEWITHSUNDEEP
mysqlsqldatabase
Bala
Bala

Reputation: 390

query not functioning as expected

Below is my query which i'm trying to use, 

select DISTINCT c.sno from Cards as c, reservation as r 
where c.name='CRS-4-FC' AND c.sno != r.ssno;

This query is supposed to select only those sno from Cards which dont exist in the reservation table ie no sno is present in the column of ssno in reservation table. I put in

c.sno!=r.ssno

so that i dont choose all the sno. But when this query runs i get all the sno rather than those that are not reserved. Can someone help out where im going wrong..

Upvotes: 0

Views: 33

Answers (3)

Steve
Steve

Reputation: 216263

select DISTINCT c.sno 
from Cards as c LEFT JOIN reservation as r  
ON c.sno = r.ssno
where c.name='CRS-4-FC' AND r.ssno IS NULL;

Upvotes: 1

Omesh
Omesh

Reputation: 29071

try LEFT JOIN instead:

SELECT DISTINCT c.sno
FROM Cards c
    LEFT JOIN reservation as r
        ON c.sno = r.ssno
WHERE c.name='CRS-4-FC' AND
      r.ssno IS NULL;

See Visual Explanation of SQL Joins

Upvotes: 1

juergen d
juergen d

Reputation: 204746

try

select DISTINCT c.sno
from Cards as c 
left outer join reservation as r on c.sno = r.ssno
where r.ssno is null
and c.name='CRS-4-FC'

Upvotes: 2

Related Questions