There are one-one relationship between room and application means an application must occupy a room. If I want to get the room that doesn't occupied by application, how to write sql to query
Add a comment
|
3 Answers
select *
from room r
where not exists (select 1
from application a
where a.roomId = r.roomId)
OR
select *
from room r left outer join
application a on r.roomId = a.roomId
where a.roomId is null
Comments
try this
SELECT roomID,description FROM room WHERE roomID NOT IN ( SELECT roomID from application )
1 Comment
Roopesh Shenoy
avoid not in.. use not exists instead.. generally far better in terms of performance
Algorithm:
- Look at all the existing rooms
- Look at all the rooms used by existing applications
The difference between those is the rooms which are not used by any of the applications
select roomID from room where roomID not in (select roomID from application)
should do the trick.
