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

                )

        )

)

Forget all the CONCAT/GROUP_CONCAT stuff. Just return an ordered array and handle the rest of the problem in your application code — Strawberry
– Strawberry, Commented Aug 30, 2016 at 12:36

Ajay Kumar · Accepted Answer · 2016-09-05 19:02:44Z

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;

Nemeros · Accepted Answer · 2016-08-30 12:09:22Z

0

Despite the fact that i think that the SGBD shouldn't take part to the decoration of the data, you need to do that in two steps.

First you need to do a group_concat to aggregate the child_table data. Then you will be able to do the main group_concat with the RoomGuests_Table and with the result of the first query.

If you try to do only 1 query with only one group_concat you will have duplicates data.

Exemple :

SELECT ...
FROM info_table it
left outer join RoomGuests_Table rt on it.ID = rt.R_ID
left outer join 
 ( SELECT ct.R_ID AS RG_ID, GROUP_CONCAT(ct.Age SEPARATOR ',') as concat_age
 FROM ChildAge_Table as ct
 GROUP BY ct.R_ID) tmp on tmp.RG_ID = rt.ID
GROUP BY ....

edited Aug 30, 2016 at 12:09

answered Aug 30, 2016 at 11:54

Nemeros

4252 silver badges7 bronze badges

2 Comments

User97798 Over a year ago

yes you are right i am getting duplicates data so how to do that please give me an example or some link, i am still searching about that

Nemeros Over a year ago

i eddited my answer to provide a minimal exmple strucure

Faizan Younus · Accepted Answer · 2016-08-30 12:37:43Z

0

Hope this helps.

select i.name,i.rooms , group_concat(jsonres separator ',') from  Info_Table i right join ( select r.r_id, CONCAT('[{"NoOfAdults":"', Adult,'","NoOfChild":"', Child,'","ChildAge":[',group_concat(C.Age separator ','),']"}')   jsonres from test.RoomGuests_Table r left join test.ChildAge_Table  c on r.ID=c.R_ID group by c.R_ID)  as r on i.id= r.r_id group by i.name,i.rooms;

answered Aug 30, 2016 at 12:37

Faizan Younus

8031 gold badge9 silver badges14 bronze badges

Collectives™ on Stack Overflow

How to make nested select query in mysql

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related