how to select n record child on every single parent record in relation data in mysql

Question

suppose i have two table one is parent and second is child. parent can have n child and i have to pull record data with child where parent => id is local key and child => parent_id is foreign key. how to do that with efficient way.

    parent_table
+-------------------+
|  id   |   name    |
+-------------------+
|   1   |     p1    |
|   2   |     p2    |
|   3   |     p3    |
|   4   |     p4    |
|   5   |     p5    |
|   6   |     p6    |
+-------------------+



          child_table
+------------------------------+
|  id   | parent_id  |  name   |
+------------------------------+
|   1   |     1      |   c1    |
|   2   |     1      |   c2    |
|   3   |     2      |   c3    |
|   4   |     3      |   c4    |
|   5   |     3      |   c5    |
|   6   |     3      |   c6    |
|   7   |     5      |   c7    |
|   8   |     6      |   c8    |
|   9   |     6      |   c9    |
+------------------------------+

result be like

Array
(
  [0] => Array
      (
          [id] => 1
          [name] => p1
          [children] => Array
              (
                [0] => Array
                    (
                      [id] => 1
                      [parent_id] => 1
                      [name] => c1
                    )

                [1] => Array
                    (
                      [id] => 2
                      [parent_id] => 1
                      [name] => c2
                    )
              )
      )

  [0] => Array
      (
          [id] => 2
          [name] => p2
          [children] => Array
              (
                [0] => Array
                    (
                      [id] => 1
                      [parent_id] => 2
                      [name] => c3
                    )
              )
      )

)

i know it can be done with for loop (i work with php)

Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — Raymond Nijland, May 01 '19 at 12:13

Rakesh Jakhar · Accepted Answer · 2019-05-01T12:37:45.607

3

You can use GROUP BY, GROUP_CONCAT

 SELECT p.*,GROUP_CONCAT(c.id,',',c.parent_id,',',c.name SEPARATOR ':') As subset
 FROM 
 parent_table p 
 JOIN child_table c ON p.id=c.parent_id GROUP BY p.id

For the subset you can use explode to separate them. DEMO

edited May 01 '19 at 12:37

answered May 01 '19 at 12:21

Rakesh Jakhar

6,380
2
11
20

score 0 · Answer 2 · answered May 01 '19 at 12:37

you can try something like this to get child rows and children count as well by using with rollup

SELECT parent_table.id as p_id,parent_table.name as p_name,child_table.id as c_id ,child_table.name as c_name,child_table.parent_id as cp_id, COUNT(child_table.id) as count_children FROM parent_table INNER JOIN child_table on parent_table.id=child_table.parent_id GROUP by parent_table.id, child_table.id WITH ROLLUP

how to select n record child on every single parent record in relation data in mysql

2 Answers2