sql query to select records from mysql

Can suggest me the best way to select records from MySQL

customer
--------
id  bed_id  status
1   1       vocated     
2   1       booked
3   2       vocated

bed
-----
id  name
1   lower 
2   middle
3   upper

I need to select bed which are vacated or not allotted

Here, the expected results are The empty bed should be: bed_id’s = 2 and 3 what is best SQL query to get this kind of result

Answer

Join the tables and group by the bed’s id and put the conditions in a HAVING clause:

select b.id, b.name
from bed b left join customer c
on c.bed_id = b.id
group by b.id
having (
  sum(status = 'vacated') > 0
  and
  sum(status = 'booked') = 0
) or sum(status is null) > 0

I’m not sure if you need and or or in the conditions