How to GROUP_CONCAT where there are null values in MYSQL will fields from LEFT JOIN?

Question

This is related to my previous example question submitted here. I have the following sql query :

SELECT 
listings.*,
region.REGION_ID,
#GROUP_CONCAT(region.REGION_ID) AS GROUP_IDs,
......
......
INNER JOIN schedule ON schedule.SCHEDULE_ID = listings.LISTING_SCHEDULE_ID 
LEFT JOIN listing_region ON listing_region.LIST_REGION_LISTING_ID = 
listings.LISTING_ID 
......
......
WHERE listings.LISTING_ID IN 
(SELECT LISTING_ID FROM listings WHERE ..........

The result is as follows :

I get multiple RegionIDs for the same ListingID and rest is null (expected) for non-existing records from LEFT join.

Now I need to group region_Ids for the same Listing ID.

So the only change I did was commenting out "region.REGION_ID" and enabled "GROUP_CONCAT(region.REGION_ID) AS GROUP_IDs,".

The result is as follows :

I do not understand why null rows are now dropped. I want them...! What should I correct here?

No, I did not. Adding GROUP BY shows only one null record for all the records that have null fields for region_ID. — PCG, Mar 31 '19 at 16:36
@ forpas : I should have used "GROUP BY LISTING_ID" (I tried REGION_ID instead) here. DId not notice that until you raised it. Thank you very much for the help. — PCG, Mar 31 '19 at 16:39

score 0 · Answer 1 · answered Mar 31 '19 at 16:42

0

The only change I was supposed to do to Add: GROUP BY ListingID to main filter clause.

answered Mar 31 '19 at 16:42

PCG

2,049
5
24
42

How to GROUP_CONCAT where there are null values in MYSQL will fields from LEFT JOIN?

1 Answers1

Linked