MySQL LIMIT up to a certain number - depending on previous subquery

I am trying to build an SQL query that randomly selects entries from different tables, up to a certain number.

Let's say I have 3 tables A,B and C. I want to select 10 rows total from A, B and C combined.

Now i want to randomly select 2 entries from A, up to 6 entries from B (depending on how many were retrieved from A. If A does not have any rows, I want to get 6 rows from B. If A returned 1 row, i want to get 5 rows from B and so on..). Then I want to fill the remaining entries (between 0 and 10, depending on the previous subqueries) with randomly selected rows from C. So far I have tried:

select * from (
(SELECT * FROM A  ORDER BY RAND() LIMIT 2)
UNION
(SELECT * FROM B ORDER BY RAND())
limit 4) a

Unfortunately, as soon as I union both subqueries the random ordering from the second query is lost and I always retrieve the same first 4 rows from B. Is this possible using just SQL or do I have to do multiple queries and union them programmatically?

Best regards

Solution

You can do it with a query like this:

SELECT * FROM (
    SELECT * FROM (
        SELECT ab.* FROM (
            SELECT * FROM ( SELECT 1 as level,a.* FROM table_a AS a ORDER BY RAND() LIMIT 2) atmp
            UNION
            SELECT * FROM ( SELECT 2 as level ,b.* FROM table_b AS b ORDER BY RAND() LIMIT 6 ) btmp
        ) AS ab ORDER BY ab.level LIMIT 6
    ) AS totab
UNION ALL
    SELECT * FROM ( SELECT 3 as level,c.* FROM table_c AS c ORDER BY RAND() LIMIT 10) ctmp
) AS totabc ORDER BY totabc.level LIMIT 10;

Sample Tables

mysql> select * from table_a;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0,00 sec)

mysql> select * from table_b;
+----+
| id |
+----+
| 11 |
| 22 |
| 33 |
| 44 |
| 55 |
| 66 |
| 77 |
+----+
7 rows in set (0,00 sec)

mysql> select * from table_c;
+--------+
| id     |
+--------+
|    111 |
|    222 |
|    333 |
|    444 |
|    555 |
|    666 |
|    777 |
|    888 |
|    999 |
| 101010 |
| 111111 |
+--------+
11 rows in set (0,00 sec)

Sample Query

mysql> SELECT * FROM (
    ->     SELECT * FROM (
    ->         SELECT ab.* FROM (
    ->             SELECT * FROM ( SELECT 1 as level,a.* FROM table_a AS a ORDER BY RAND() LIMIT 2) atmp
    ->             UNION
    ->             SELECT * FROM ( SELECT 2 as level ,b.* FROM table_b AS b ORDER BY RAND() LIMIT 6 ) btmp
    ->         ) AS ab ORDER BY ab.level LIMIT 6
    ->     ) AS totab
    -> UNION ALL
    ->     SELECT * FROM ( SELECT 3 as level,c.* FROM table_c AS c ORDER BY RAND() LIMIT 10) ctmp
    -> ) AS totabc ORDER BY totabc.level LIMIT 10;
+-------+--------+
| level | id     |
+-------+--------+
|     1 |      1 |
|     1 |      4 |
|     2 |     11 |
|     2 |     33 |
|     2 |     66 |
|     2 |     55 |
|     3 |    333 |
|     3 | 111111 |
|     3 |    777 |
|     3 |    888 |
+-------+--------+
10 rows in set (0,00 sec)

mysql>