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.

Answer 1

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