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?

http://stackoverflow.com/questions/22369095/mysql-group-by-and-skip-grouping-on-null-values — merezha, Apr 12 '15 at 16:00

Máté Juhász · Answer 1 · 2015-04-12T16:34:48.987

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 '15 at 16:34

answered Apr 12 '15 at 16:03

Máté Juhász

2,197
1
19
40

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 Apr 12 '15 at 16:09
I can't make it working. can you please elaborate a little more. Thanks. – Joshi Apr 12 '15 at 17:26
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. – Máté Juhász Apr 12 '15 at 18:15

Mysql query how to return groups for empty values?

1 Answers1