CODEWITHSUNDEEP
sqlsql-server
Angie
Angie

Reputation: 15

Left join in SQL-server

I have 3 tables Table Room with columns- rid, rname, type, Table Customer with columns- cid, cname, Table Booking with columns- bid, cid, rid, check-in, check-out.

Now I have to get all the room names and if the room is book on 2019-09-01 then display the customer name in front of the room other wise show null.

I tried to apply a left join and where condition but its only giving me those room names which are booked on that day its not showing other room name.

select rname, c.cname from room as r
left join booking as b on b.rid=r.rid
left join customer as c on c.cid=b.cid
where b.[check-in]='2019-09-01'

and if I'm applying other condition its giving me addition name of the those room which are completely null, means they are not booked at all on any date.

select rname, c.cname from room as r
left join booking as b on b.rid=r.rid
left join customer as c on c.cid=b.cid
where b.[check-in]='2019-09-01' or b.[check-in] is null;

Upvotes: 1

Views: 66

Answers (1)

jarlh
jarlh

Reputation: 44766

Move the b condition from WHERE to ON to get true LEFT JOIN result:

select rname, c.cname from room as r
left join booking as b on b.rid=r.rid and b.[check-in]='2019-09-01'
left join customer as c on c.cid=b.cid

Upvotes: 1

Related Questions