如何在LEFT JOIN的MYSQL will字段中GROUP_CONCAT中存在空值? [英] How to GROUP_CONCAT where there are null values in MYSQL will fields from LEFT JOIN?

问题描述

这与我以前提交的示例问题有关

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 ..........

结果如下:

我为同一个ListingID获得了多个RegionID，而对于来自LEFT联接的不存在的记录，rest为null(预期).

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

现在，我需要将region_Id分组以使用相同的列表ID.

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

因此，我所做的唯一更改是注释掉了"region.REGION_ID"并启用了"GROUP_CONCAT(region.REGION_ID)AS GROUP_ID".

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

结果如下:

我不明白为什么现在要删除空行.我想要它们...！ 我应该在这里纠正什么?

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

推荐答案

我应该做的唯一更改是:将GROUP BY ListingID添加到主过滤器子句中.

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

这篇关于如何在LEFT JOIN的MYSQL will字段中GROUP_CONCAT中存在空值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！