GROUP_CONCAT and join, how to include not matching rows also

Question

How to include non matching rows in GROUP_CONCAT ? Here, Table cls_sec contains each section assigned to class.

Class One has 2 section - 'A' and 'B',

Class Two has 1 Section 'A'

Class Three has No section.

Here section is key to join relation between tables, but how can I include class three which has no section in GROUP_CONCAT result.

Table cls - lists of Class

id  |   ttl
===========
1   |   One
2   |   Two
3   |   Three

Table sec - lists of section

id  |   ttl
===========
1   |   A
2   |   B

Table cls_sec - lists of each section assigned to Class

id  |   c_id|   s_id    
=====================
1   |   1   |   1
2   |   1   |   2
3   |   2   |   1

According to rows in cls_sec, my expectation is as below,

1.One-B,1.One-A,2.Two-A,3.Three 

if class has section, each section will display with its associated class, else echo only class (if any section hasn't assigned to this class) :

So what I do in mysql select if matched rows OR null rows

MySQL CODE

SELECT
    GROUP_CONCAT(DISTINCT cls.id,'.',cls.en_ttl, '-', sec.en_ttl 
                 ORDER BY cls.id) AS cls
FROM
    cls
LEFT JOIN
    cls_sec ON cls_sec.c_id = cls.id
JOIN
    sec ON sec.id = cls_sec.s_id OR cls_sec.s_id is NULL 
ORDER BY cls.id DESC

But I am getting

1.One-B,1.One-A,2.Two-A,3.Three-B,3.Three-A

Answer 1

• Join with OR .. IS NULL condition needs to be replaced with LEFT JOIN.
• There is no need of ORDER BY at the end, when you are aggregating into one row.
• Use Coalesce() function to consider the case when there is no section for a class.
• I have added Concat() function inside the Group_concat(), for ease of understandability. However, as suggested by @Raymond Nijland, we can still work without using it.
• Concat() function returns null, if any of the substrings to concatenate is null. So, when there is no section, we will need to move - into Concat(), to avoid any trailing - in the string.

Try:

SELECT
    GROUP_CONCAT(CONCAT(cls.id,'.',cls.en_ttl, 
                        COALESCE(CONCAT('-',sec.en_ttl), ''))  
                 ORDER BY cls.id) AS cls
FROM
    cls
LEFT JOIN
    cls_sec ON cls_sec.c_id = cls.id
LEFT JOIN
    sec ON sec.id = cls_sec.s_id