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)
(SELECT * FROM B ORDER BY RAND())
limit 4) a
Use CTEs so that you can get the counts of the rows that were selected from tables A and B. Like this:
with a_rows as ( select * from table_a order by random() limit 2 ), b_rows as ( select * from table_b order by random() limit 6 - (select count(*) from a_rows) ), c_rows as ( select * from table_c order by random() limit 10 - (select count(*) from b_rows) select * from a_rows union select * from b_rows union select * from c_rows;
Note that CTEs are a relatively recent addition to MySQL's feature set.
Alternatively, you could use separate queries instead of CTEs.