User97798 User97798 - 2 months ago 11
MySQL Question

How to use if condition with left join in mysql

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

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;
Comments