Mysql query how to return groups for empty values?

Question

I have this mysql query which returns the vacant beds/rooms grouped by category. But if there is no room in the category, that group is not shown, what I want that even where there is no vacant rooms the group is shown with empty/null value against it.

select `rct`.`room_category` AS `room_category`,
    group_concat(`rn`.`room_name` separator ',') AS `vacant_beds` 
    from ((`room_name` `rn` join `room_category` 
    `rct` on((`rn`.`room_category` = `rct`.`id`))) 
    left join `patient_detail` `pd` on(((`rn`.`id` = `pd`.`bed_type`) 
    and (isnull(`pd`.`discharge_date`) or (now() between `pd`.`admission_date` and   `pd`.`discharge_date`))))) 
    where isnull(`pd`.`id`) group by `rn`.`room_category`

The result of the query is like this: room_category vacant_beds

MALE GENERAL WARD    MG-5
FEMALE GENERAL WARD  FG-2,FG-3,FG-4
MOTHER CHILD WARD    MC-1,MC-3,MC-4
NICU                 NICU-8,NICU-4,NICU-5,NICU-6,NICU-1,NICU-7,NICU-2
CLASSIC              CL-9,CL-4,CL-5,CL-7,CL-8
DELUXE               DLX-6,DLX-3,DLX-4,DLX-5

In the above result Twin is missing as there is no vacant beds against it. How I can achieve this?

stackoverflow.com/questions/22369095/…

merezha
– merezha

2015-04-12 16:00:30 +00:00
Commented Apr 12, 2015 at 16:00 — merezha
– merezha, Commented Apr 12, 2015 at 16:00

Máté Juhász · Accepted Answer · 2015-04-12 16:34:48Z

0

You need to have to queries: One for the vacant beds as you already have and another one for the groups without vacant beds, here include also "no vacant" AS vacant_ beds.

A handy way to create second query is to match against the first one looking for missing categories: SELECT rct.Room_category, " " as vacant_room FROM rct LEFT JOIN old_query on ... WHERE old_query.room_category IS NULL

Then just need to UNION the two queries.

edited Apr 12, 2015 at 16:34

answered Apr 12, 2015 at 16:03

Máté Juhász

2,3552 gold badges24 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Joshi Over a year ago

Then How do I know which beds are vacant then? is there no other way? What I want is that it returns the group but with empty value for example - NICU - ' '

Joshi Over a year ago

I can't make it working. can you please elaborate a little more. Thanks.

Máté Juhász Over a year ago

Sorry for not making enough clear. Do you have error message or problem creating the query? If you could post some data it would help to clarify the query.

Collectives™ on Stack Overflow

Mysql query how to return groups for empty values?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related