Geek asks and answers

Join two tables in MySQL, but randomize only the second table

Possible duplicate:
Join two tables in MySQL with random rows from the second

So, I have two tables:

The first table has 5 rows, I want to select all 5 rows in the same order as in the table.

The second table has 800 rows, select only 5 rows with random order.

And then Join the two tables, but keep the order of the first table, just randomize the results from the second table.

I tried different queries but cannot get the order of the results of the first table when I join two tables.

+1
source share
2 answers

To literally do what you ask for:

select * from first left join (select * from second order by rand() limit 5) on first.??=second.??;

, , ( ).

+1

, . , , !

SET @num1=0, @num2=0;

SELECT t1.field1, t2.field2
FROM (
    SELECT field1, @num1:=@num1+1 AS num
    FROM table1
) AS t1
INNER JOIN (
    SELECT field2, @num2:=@num2+1 AS num
    FROM (
        SELECT field2
        FROM table2
        ORDER BY RAND()
        LIMIT 5
    ) AS t
) AS t2
ON t1.num = t2.num;
0

More articles:


All Articles