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

Dylan Su · Accepted Answer · 2016-04-03T13:06:50.543

11

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;

edited Apr 03 '16 at 13:06

answered Apr 02 '16 at 15:23

Dylan Su

5,975
1
16
25

It works, and i was surprised of this, but now i understand why : http://stackoverflow.com/questions/1225144/why-does-mysql-allow-group-by-queries-without-aggregate-functions Thank's ! – Naeco Apr 02 '16 at 15:57
Juste for curiosity, have you a solution for a other SQL database, because the behaviour of "group by" exist just for mySQL. – Naeco Apr 02 '16 at 15:59
it's easy. I am on the phone. One way is to allocate 1....N for each group in the inner sub query, and filter out 1 in the outer query – Dylan Su Apr 02 '16 at 16:09
Coul'd you give me an example for this idea with an allocation ? Thank's ! – Naeco Apr 03 '16 at 08:38
probably, with rank function ( http://stackoverflow.com/questions/3333665/rank-function-in-mysql ), but in order to use "IN", the subrequest should return just one row.... – Naeco Apr 03 '16 at 08:46
Updated the answer with the allocation method. pls try if it works. I have no sample data to test for this solution. – Dylan Su Apr 03 '16 at 13:07

geeksal · Answer 2 · 2016-04-02T15:44:28.600

1

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.

edited Apr 02 '16 at 15:44

answered Apr 02 '16 at 15:28

geeksal

4,856
3
24
47

this proposition return just one line, it was one of my try :/ – Naeco Apr 02 '16 at 15:38
@user3671363 actually it is very diifficult to test without sample data. Can you please post a sqlfiddle. Mean while i will try to figure out something else. – geeksal Apr 02 '16 at 15:41
@user3671363 just check my updated solution and let me know if it works. Just to know if i am correct......I know that you have already accepted the answer. – geeksal Apr 02 '16 at 15:59
1

thank's to try. Sorry, it's don't work because the LIMIT limits the result for the totality not by idparent. – Naeco Apr 02 '16 at 16:09
@user3671363 so if i remove `LIMIT 1` will it work? – geeksal Apr 02 '16 at 16:14
no, but it give me an idea for the upgrade of the question, i give it after the upgrade – Naeco Apr 02 '16 at 17:32

mySQL select from a list from another select

2 Answers2

MySQL solution using GROUP BY:

MySQL solution using `GROUP BY`: