1

I have 3 identically structured tables called low, med, high. I would like to randomly choose one table from these tables, then query that table.

visual example:

SELECT * FROM RAND(low,med,high)

Garrison
  • 17
  • 5
  • 4
    `I have 3 identically structured tables called low, med, high.` looks like you should rather have table with additional column for your low, med, high instead – Marcin Orlowski Dec 13 '20 at 06:16

3 Answers3

1

You may use a union approach which assigns a computed value of 1, 2, or 3, to each of the three tables, then randomly choose the records from just one table:

SELECT *
FROM
(
    SELECT *, 1 AS label FROM low UNION ALL
    SELECT *, 2 FROM med UNION ALL
    SELECT *, 3 FROM high
) t
WHERE label = FLOOR(RAND()*3+1);  -- random number between 1 and 3 inclusive
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
1

You used the PHP tag, so in that language it can be like this:

$tables = ['low', 'med', 'high'];
$randomTable = $tables[mt_rand(0, 2)];
$query = 'SELECT * FROM '.$randomTable;

$mysqli = new mysqli("host","my_user","my_password","my_db");
$result = $mysqli -> query( $query);

In MySQL, in addition to the other solutions, it can be like this:

Select the table randomly:

set @randomNumber = FLOOR(RAND()*3+1);

set @table = IF(@randomNumber = 1, 'low', IF(@randomNumber = 2, 'med', 'high'));

or

set @table = CASE
    WHEN @randomNumber = 1 THEN 'low'
    WHEN @randomNumber = 2 THEN 'med'
    ELSE 'high'
END;

Build and run the query:

SET @s = CONCAT('select * from ', @table); 
PREPARE stmt1 FROM @s; 
EXECUTE stmt1; 
DEALLOCATE PREPARE stmt1;

How to select from MySQL where Table name is Variable

MySQL get a random value between two values

eniel.rod
  • 855
  • 8
  • 12
  • I chose this answer because the script is faster. Tim Biegeleisen's answer worked also. But I am trying to add additional queries using UNION and I must be missing something. Here is the code: $tables = ['low', 'med', 'high']; $randomTable = $tables[mt_rand(0, 2)]; $query2 = ' (SELECT * FROM low ORDER BY RAND() LIMIT 0,3) UNION (SELECT * FROM med ORDER BY RAND() LIMIT 0,1) UNION (SELECT * FROM '.$randomTable.' ORDER BY RAND() LIMIT 0,1) '; – Garrison Dec 13 '20 at 19:35
  • Thank you. Please, can you explain what you expect from that query? And what is wrong with it? Does it throw an error, or an unwanted result? – eniel.rod Dec 14 '20 at 06:59
  • The code above works but I added some WHERE clauses that caused a syntax error. I found the issue. Thanks – Garrison Dec 15 '20 at 22:56
0
select * from high
union all
select * from med
union all
select * from low
order by rand()
limit 1

SQLFiddle demo

juergen d
  • 201,996
  • 37
  • 293
  • 362