mySQL select from a list from another select

Question

A have a table so :

id | name | idparent | info
 1 | AA   |          | x
 2 | BB   |          | 
 3 | CC   |          | x
 4 | DD   | 1        |
 5 | EE   | 1        |
 6 | FF   | 2        |
 7 | GG   | 2        |
 8 | HH   | 3        |
 8 | HH   | 4

and what i want to do is done like this with mySQL/PHP :

SELECT id FROM table WHERE info LIKE 'x'

and in PHP

for i in each id result of the first request :
  SELECT id, name FROM table where idparent = i ORDER BY RAND() LIMIT 1;
endFor

for instance, the result could be :

4 |DD
8 |HH

and because of the RAND(), the result could be too :

5 |EE
8 |HH

but it is not so beautiful, is there a possibility to do this in just one request ?

I have tried several idea but without success, i don't enumerate here, in order not to pollute the comprehension my subject :)

Thank's in advance for your answer

Answer 1

Here is a solution using sub-queries, which is only valid for MySQL, since the GROUP BY behavior of MySQL is a extension for SQL standard.

MySQL solution using GROUP BY:

SELECT  t.id, t.name 
FROM (
    SELECT id, name, idparent
    FROM table 
    where idparent IN (SELECT id FROM table WHERE info LIKE 'x')
    ORDER BY RAND()
) t
GROUP BY t.idparent;

Solution for non-MySQL, by allocation of group rank with user variable:

SELECT * 
FROM (
    SELECT 
        id, name, idparent,
        IF(idparent = @last_idparent, @grp_rank := @grp_rank + 1, @grp_rank := 1) as grp_rank
    FROM table CROSS JOIN (SELECT @grp_rank := 1, @last_idparent := NULL) param
    where idparent IN (SELECT id FROM table WHERE info LIKE 'x')
    ORDER BY RAND()
) t
WHERE t.grp_rank = 1;

Answer 2

Try this:

SELECT id, name FROM table natural join (SELECT id as idparent FROM table WHERE info LIKE 'x') as T ORDER BY RAND() LIMIT 1;

I hope this will resolve the issue.