Multiple Table Joins with Group_Concat where some records don't exist in all tables

Question

I am trying to do a fairly complex (for me) query that will grab a Description field from a Main Table and then append it with titles and values from related Look-Up-Tables. Not all records have records in the Look-up tables. I'll pose further questions as subsequent questions as I go along, but to start my issue is that only those records with values in all the tables show up.

http://sqlfiddle.com/#!9/09047/13

• (null)
• This is Record 2 Text
  
  Color:
  Red
  
  Fruit:
  Apple
• (null)

If I use Concat_WS I get all records but my 'label' in the concat disappears:

http://sqlfiddle.com/#!9/09047/16

• This is Record 1 Text
  
  Blue
  
• This is Record 2 Text
  
  Red
  Apple
• This is Record 3 Text
  
  
  Grape

So my first step is to get all the record descriptions regardless of how many Look-up-Tables they exist in and to get the Names/Labels displaying.

Answer 1

It looks like you need COALESCE:

Select J.id, 
  Concat(J.Description,
    COALESCE(Concat('<b>Color</b>:<br>',
         group_concat(F.Name SEPARATOR '<br>')),''),
    '<br>',
    COALESCE(Concat('<b>Fruit</b>:<br>',
         group_concat(F2.Name SEPARATOR '<br>')),'')

  ) AS output
from Main J
Left Join LUT_1 L          On J.ID=L.MainID
Left Join LUT_Names_1 F    On F.ID=L.LUT_NAME_ID
Left Join LUT_2 L2         On J.ID=L2.MainID
Left Join LUT_Names_2 F2   On F2.ID=L2.LUT_NAME_ID
Group by J.ID;

SQLFiddle Demo

EDIT:

As always for MySQL, the query itself is basing on MySQL extension. If you set it to ONLY_FULL_GROUP_BY (default for MySQL 5.7.5 and above):

SET sql_mode=ONLY_FULL_GROUP_BY;
-- query will return error

J.Description' isn't in GROUP BY

To correct this you will need to use aggregation function on that column like: MAX:

SET sql_mode=ONLY_FULL_GROUP_BY;

Select J.id, 
Concat(MAX(J.Description),
  COALESCE(Concat('<b>Color</b>:<br>',
         group_concat(F.Name SEPARATOR '<br>')),''),
  '<br>',
  COALESCE(Concat('<b>Fruit</b>:<br>',
         group_concat(F2.Name SEPARATOR '<br>')),'')

)
from Main J
Left Join LUT_1 L          On J.ID=L.MainID
Left Join LUT_Names_1 F    On F.ID=L.LUT_NAME_ID
Left Join LUT_2 L2         On J.ID=L2.MainID
Left Join LUT_Names_2 F2   On F2.ID=L2.LUT_NAME_ID
Group by J.ID;