How to use if condition with left join in mysql

Question

I have below Table which is connected with each other like

Info_Table -> RoomGuests_Table -> ChildAge_Table

These are Tables

        Info_Table          
+---------------------------+
|   ID      |  Name | Rooms |
+---------------------------+
|   INFO1   |   ABC |   2   |
|   INFO2   |   DEF |   1   |
|   INFO3   |   GHI |   3   |
+---------------------------+


           RoomGuests_Table     
+-----------------------------------+   
|   ID  |   R_ID    | Adult | Child |
+-----------------------------------+   
|   RG1 |   INFO1   |   2   |   2   |
|   RG2 |   INFO1   |   3   |   0   |
|   RG3 |   INFO2   |   2   |   1   |
|   RG4 |   INFO3   |   2   |   1   |
|   RG5 |   INFO3   |   2   |   2   |
|   RG6 |   INFO3   |   2   |   1   |
+-----------------------------------+


      ChildAge_Table                        
+-----------------------+   
|   ID  | R_ID  |   Age |
+-----------------------+   
|   CA1 |   RG1 |   4   |
|   CA2 |   RG1 |   5   |
|   CA3 |   RG3 |   2   |
|   CA4 |   RG4 |   7   |
|   CA5 |   RG5 |   1   |
|   CA6 |   RG5 |   5   |
|   CA7 |   RG6 |   3   |
+-----------------------+   

I Want Result like this

If Info_Table's ID = 'INFO3'; Then result should be show like this.

                                    Result                              
+-----------------------------------------------------------------------------------------------+   
|   ID      | Name  | Rooms |                           RoomGuests                              |
+-----------------------------------------------------------------------------------------------+   
|   INFO3   |   GHI |   3   | [{ "NoOfAdults":"2", "NoOfChild":"1", "ChildAge":[7] },           |
|           |       |       |  { "NoOfAdults":"2", "NoOfChild":"2", "ChildAge":[1,5] },         |
|           |       |       |  { "NoOfAdults":"2", "NoOfChild":"1", "ChildAge":[3] }]           |
+-----------------------------------------------------------------------------------------------+

I have tried below code and works.

SELECT i.ID, i.name,i.rooms, RG.RoomGuests
FROM Info_Table i 
LEFT JOIN (
    SELECT 
        R.ID, R.R_ID AS RG_ID,      
        CONCAT(
            '[',
                GROUP_CONCAT( 
                    CONCAT(
                        '{
                            \"NoOfAdults\":\"', Adult,'\",
                            \"NoOfChild\":\"', Child,'\",
                            \"ChildAge\":', CA.ChildAge,'
                        }'
                    ) 
                ),
            ']'
        ) AS RoomGuests 

    FROM RoomGuests_Table R

    LEFT JOIN (
        SELECT 
            C.R_ID AS CA_ID, 
            CONCAT(
                '[',
                    GROUP_CONCAT( Age SEPARATOR ','),
                ']'
            ) AS ChildAge
        FROM ChildAge_Table C
        GROUP BY CA_ID
    ) CA ON CA.CA_ID = R.ID)

    GROUP BY RG_ID

) RG ON RG.RG_ID = i.ID
WHERE i.ID = 'INFO3';

but in below condition is not working

IF Record like this

        Info_Table          
+---------------------------+
|   ID      |  Name | Rooms |
+---------------------------+
|   INFO3   |   GHI |   3   |
+---------------------------+


           RoomGuests_Table     
+-----------------------------------+   
|   ID  |   R_ID    | Adult | Child |
+-----------------------------------+   
|   RG4 |   INFO3   |   2   |   0   |
|   RG5 |   INFO3   |   2   |   2   |
|   RG6 |   INFO3   |   2   |   1   |
+-----------------------------------+


      ChildAge_Table                        
+-----------------------+   
|   ID  | R_ID  |   Age |
+-----------------------+   
|   CA5 |   RG5 |   1   |
|   CA6 |   RG5 |   5   |
|   CA7 |   RG6 |   3   |
+-----------------------+   

As you can see RoomGuests_Table 's RG4 has no child mean it has 0 value so in this condition it should show result like this

                                Result                              
+-----------------------------------------------------------------------------------------------+   
|   ID      | Name  | Rooms |                           RoomGuests                              |
+-----------------------------------------------------------------------------------------------+   
|   INFO3   |   GHI |   3   | [{ "NoOfAdults":"2", "NoOfChild":"0", "ChildAge":[] },            |
|           |       |       |  { "NoOfAdults":"2", "NoOfChild":"2", "ChildAge":[1,5] },         |
|           |       |       |  { "NoOfAdults":"2", "NoOfChild":"1", "ChildAge":[3] }]           |
+-----------------------------------------------------------------------------------------------+   

But is showing like this

                                    Result                              
+-----------------------------------------------------------------------------------------------+   
|   ID      | Name  | Rooms |                           RoomGuests                              |
+-----------------------------------------------------------------------------------------------+   
|   INFO3   |   GHI |   3   | [{ "NoOfAdults":"2", "NoOfChild":"2", "ChildAge":[1,5] },         |
|           |       |       |  { "NoOfAdults":"2", "NoOfChild":"1", "ChildAge":[3] }]           |
+-----------------------------------------------------------------------------------------------+

ChildAge_Table has no record of RG4 so how to my desire result or is there any way to check if CA.ChildAge = null then put there '[]'

Answer 1

Try protecting that CA.ChildAge with an IFNULL like so:

...CONCAT(
          '{\"NoOfAdults\":\"', Adult,'\",
            \"NoOfChild\":\"',  Child,'\",
            \"ChildAge\":',     IFNULL(CA.ChildAge, '[]'),'}'
         )...

The problem is that C.ChildAge is absent for 'RG4' (which you know) and thus is LEFT JOINed to RoomGuests_Table as a NULL CA.ChildAge. This NULL makes the inner CONCAT('{...}') NULL as well, but NULLs are filtered out by GROUP_CONCAT, meaning the outer CONCAT('[...]') won't see it.

Here's full, working SQL:

   SELECT i.ID, i.name,i.rooms, RG.RoomGuests
     FROM Info_Table i 
LEFT JOIN (   SELECT R.ID, R.R_ID AS RG_ID,      
                     CONCAT('[',
                            GROUP_CONCAT(CONCAT('{\"NoOfAdults\":\"', Adult,'\",\n',
                                                 '\"NoOfChild\":\"',  Child,'\",\n',
                                                 '\"ChildAge\":',     IFNULL(CA.ChildAge, '[]'),'}')),
                            ']')
                       AS RoomGuests 
                FROM RoomGuests_Table R
           LEFT JOIN (  SELECT C.R_ID AS CA_ID, 
                               CONCAT('[',
                                      GROUP_CONCAT( Age SEPARATOR ','),
                                      ']') AS ChildAge
                          FROM ChildAge_Table C
                      GROUP BY CA_ID) CA
                  ON CA.CA_ID = R.ID) RG
       ON RG.RG_ID = i.ID
    WHERE i.ID = 'INFO3'
 GROUP BY RG_ID;