Obtaining recursive effect with one mysql query

Question

I'm just trying to clear a doubt I have. I would like to know if I'm doing this right or if there is a better way to optimize my queries. I have 2 simple tables

Parents
------------------------
+ id +    title        +
------------------------
+ 1  +  title parent 1 +
------------------------
+ 2  +  title parent 2 +
------------------------
and so on...

Children
--------------------------------------------
+ id + parent_id +  child_text             +
--------------------------------------------
+ 1 +     1      +   some test             +
--------------------------------------------
+ 2 +     1      +   more text...          +
--------------------------------------------
+ 3 +     2      +  other text             +
--------------------------------------------
+ 4 +     2      +  some more other text...+
--------------------------------------------
and so on... you get it...

The result I want and get is:

Title parent 1
    some text
    more text

Title parent 2
    other text
    some more other text

But at the cost of looping a query into another with something like:

foreach(getParents() as $parent){ //getParents selects all the parents
    getChildren($parent['id']);  // selects all children where parent_id= $parent['id']    
}

I'm wondering if putting a query into a loop that way is bad practice or not and if there is a better way of doing this, maybe with one query only.

u can combine it to a single query using left join but while displaying u need to group them in the loop so that for each parent id the corresponding child is displayed. — Abhik Chakraborty, Mar 23 '14 at 07:09
For future reference, most people won't consider this a recursive query - recursive queries are where the `parent_id` refers to some other `id` _in the same table_ - MySQL lacks a feature that makes this easier, [so here's some resources](http://stackoverflow.com/questions/169817/is-it-possible-to-query-a-tree-structure-table-in-mysql-in-a-single-query-to-an). This is just a standard join with a many-to-one relationship. — Clockwork-Muse, Mar 23 '14 at 13:54
@Clockwork-Muse Thank you, this look most like what I am looking for. — A.M.P., Mar 23 '14 at 16:53

Ryan Vincent · Answer 1 · 2014-03-23T14:18:14.110

to append queries then use a union...

where clause is just an example to show that it needs to be of both queries to ensure correct rows are selected.

SELECT title,      id,        0 FROM parents
WHERE id < 10
UNION 
SELECT child_text, parent_id, id  FROM children
WHERE id < 10
ORDER BY 2, 3

to turn into a report with just the title do:

SELECT report.title 
FROM 
(SELECT title AS title,      id AS parent_id,        0 AS child_id 
FROM parents
UNION 
SELECT child_text, parent_id, id  FROM children
) report
ORDER BY report.parent_id, report.child_Id

or move the where clause outside

SELECT report.title 
FROM 
(SELECT title AS title,      id AS parent_id,        0 AS child_id 
FROM parents
UNION 
SELECT child_text, parent_id, id  FROM children) report
WHERE report.parent_id < 3
ORDER BY report.parent_id, report.child_Id

http://www.sqlfiddle.com/#!2/d63f4/8

Obtaining recursive effect with one mysql query

1 Answers1