Home How to combine four select queries which select an equal number of rows randomly into one?

Questions

How to combine four select queries which select an equal number of rows randomly into one?

November 29, 2021

The following is my db table:

 id category_id name
--------------------
  1     4        A
  2     5        B
  3     6        C

I have four simple select queries which pull 15 rows by random from specific categories:

select * from table where category_id = 4 order by rand() limit 15;
select * from table where category_id = 5 order by rand() limit 15;
select * from table where category_id = 6 order by rand() limit 15;
select * from table where category_id = 7 order by rand() limit 15;

I want to combine them into a single query rather than four separate queries. I’ve tried using the UNION operator but it wasn’t pulling 15 rows EQUALLY from each category:

(
    select * from table where category_id = 4 
    union 
    select * from table where category_id = 5
    union 
    select * from table where category_id = 6
    union 
    select * from table where category_id = 7 
) order by rand() limit 60;

How can I achieve this? Or, do I have to run separate queries?

^{I’ve tagged Laravel because I’m using Laravel as the backend and maybe Eloquent has a smarter way to achieve this.}

>Solution :

Have you tried this one?:

    (select * from table where category_id = 4 ORDER BY rand() LIMIT 15)
    union all
    (select * from table where category_id = 5 ORDER BY rand() LIMIT 15)
    union all 
    (select * from table where category_id = 6 ORDER BY rand() LIMIT 15)
    union all
    (select * from table where category_id = 7 ORDER BY rand() LIMIT 15)