User97798 User97798 - 3 months ago 9
MySQL Question

How to make nested select query 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 = 'INFO1';

Then result should be show like this.

Result
+-----------------------------------------------------------------------------------------------+
| ID | Name | Rooms | RoomGuests |
+-----------------------------------------------------------------------------------------------+
| INFO1 | ABC | 2 | [{"NoOfAdults" : "2", "NoOfChild" : "2", "ChildAge" : "[4,5]"}, |
| | | | {"NoOfAdults" : "3", "NoOfChild" : "", "ChildAge" : "[]"}] |
+-----------------------------------------------------------------------------------------------+


For all result should be show like

Result
+-----------------------------------------------------------------------------------------------+
| ID | Name | Rooms | RoomGuests |
+-----------------------------------------------------------------------------------------------+
| INFO1 | ABC | 2 | [{"NoOfAdults" : "2", "NoOfChild" : "2", "ChildAge" : "[4,5]"}, |
| | | | {"NoOfAdults" : "3", "NoOfChild" : "", "ChildAge" : "[]"}] |
| | | | |
| INFO2 | DEF | 1 | [{"NoOfAdults" : "2", "NoOfChild" : "1", "ChildAge" : "[2]"}] |
| | | | |
| 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 but is not working. i am not be able to understand how to do

SELECT
S.`ID`, A.`Name`, A.`Rooms`,
CONCAT(
'[',
GROUP_CONCAT(
CONCAT(
'{
\"NoOfAdults\":\"', R.Adults,'\",
\"NoOfChild\":\"', R.Child,'\",
\"ChildAge\":
\"',
CONCAT(
'[',
GROUP_CONCAT(
CONCAT('{',C.Age,'}')
),
']'
),
,'\",
}'
)
),
']'
) AS RoomGuests,
FROM `Info_Table` AS I
LEFT JOIN `RoomGuests_Table` AS R ON R.`R_ID` = A.`ID`
LEFT JOIN `ChildAge_Table` AS C ON C.`R_ID` = R.`R_ID`
GROUP BY A.R_ID;




Or is there any best way to Make Array like this Please let me know

Array
(
[ID] => INFO1
[Name] => ABC
[Rooms] => 2
[RoomGuests] => Array
(
[0] => Array
(
[NoOfAdults] => 2
[NoOfChild] => 2
[ChildAge] => Array
(
[0] => 4
[1] => 5
)

)

[1] => Array
(
[NoOfAdults] => 3
[NoOfChild] => 0
[ChildAge] => Array
(
)

)

)

)

Answer

Try to use this

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\":', 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
        GROUP BY RG_ID
    ) RG ON RG.RG_ID = i.ID
WHERE i.ID = INFO1;