CODEWITHSUNDEEP
mysql
Prince OfThief
Prince OfThief

Reputation: 6423

MySQL : query question

alt text

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

Upvotes: 1

Views: 62

Answers (3)

Roopesh Shenoy
Roopesh Shenoy

Reputation: 3427

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

Upvotes: 3

Sanjay T. Sharma
Sanjay T. Sharma

Reputation: 23248

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.

Upvotes: 2

Null Pointer
Null Pointer

Reputation: 9309

try this 

SELECT roomID,description  FROM  room WHERE roomID NOT IN ( SELECT roomID from application )

Upvotes: 2

Related Questions