issue with GROUP_CONCAT in JOIN query mysql

Question

I have a join query where I left join 2 tables say tab_sectorand tab_sector_subdivisions. I have records in first table which may or may not have corresponding records in 2nd table. I am joining these tables in such a way that Its should return all row from first table and matching row from 2nd table. Also if a row in first table has multiple record in 2nd table, it should return as GROUP_CONCAT(field_name). But my query doesn't return the way I need.

Here is the query without GROUP_CONCAT:

SELECT tab_sector.sector_id,tab_sector.sector_title,tab_sector.sector_desc,tab_sector.sector_image,tab_sector_subdivisions.subdiv_id 
FROM tab_sector 
LEFT JOIN tab_sector_subdivisions 
ON tab_sector_subdivisions.sector_id = tab_sector.sector_id WHERE tab_sector.active = 'Y'

the result is:

you can see 2 rows for id 20. What I need is single row but subdiv_id as (19,20). Then I used GROUP_CONCAT in the query like:

    SELECT tab_sector.sector_id,tab_sector.sector_title,tab_sector.sector_desc,tab_sector.sector_image,GROUP_CONCAT(tab_sector_subdivisions.subdiv_id) 
FROM tab_sector 
LEFT JOIN tab_sector_subdivisions 
ON tab_sector_subdivisions.sector_id = tab_sector.sector_id WHERE tab_sector.active = 'Y'

Then the result will be :

My other two records are missing in this. I want that rows too in my result.

Can anyone please help me to solve this? Thanks in advance.

group_concat is an aggregate operator & aggregate operators use group by (implicitly if not explicitly). This is a faq & you would have found an answer if, as you always should, you googled many clear precise phrasings of your problem/question/goal. Eg just 'GROUP_CONCAT in JOIN query mysql rows missing'. — philipxy, Jul 05 '18 at 01:43
Possible duplicate of [GROUP\_CONCAT and LEFT\_JOIN issue - Not all rows returned](https://stackoverflow.com/questions/1653195/group-concat-and-left-join-issue-not-all-rows-returned) — philipxy, Jul 05 '18 at 01:47

score 1 · Accepted Answer · answered Jul 04 '18 at 11:18

You need the group by for the not aggregated columns

SELECT tab_sector.sector_id,tab_sector.sector_title,tab_sector.sector_desc
    ,tab_sector.sector_image,group_concat(tab_sector_subdivisions.subdiv_id )
LEFT JOIN tab_sector_subdivisions 
        ON tab_sector_subdivisions.sector_id = tab_sector.sector_id 
              AND tab_sector.active = 'Y'
GROUP BY  tab_sector.sector_id,tab_sector.sector_title,
     tab_sector.sector_desc, tab_sector.sector_image

score 0 · Answer 2 · answered Jul 04 '18 at 11:25

group by missed that`s why it is returning one record,

  SELECT tab_sector.sector_id,tab_sector.sector_title,tab_sector.sector_desc,tab_sector.sector_image,GROUP_CONCAT(tab_sector_subdivisions.subdiv_id) 
FROM tab_sector 
LEFT JOIN tab_sector_subdivisions 
ON tab_sector_subdivisions.sector_id = tab_sector.sector_id WHERE tab_sector.active = 'Y'
group by tab_sector.sector_id,tab_sector.sector_title,tab_sector.sector_desc,tab_sector.sector_image;

issue with GROUP_CONCAT in JOIN query mysql

2 Answers2

Linked

Related