Geek asks and answers

Join two tables in MySQL with random rows from the second

I have two tables

First with 5 lines

The second with 800 lines

I am using this query:

SELECT * 
FROM table1 t1 
JOIN (SELECT * FROM table2 ORDER BY RAND() LIMIT 5) t2

But I get 5 rows from the first table for each result of the second table.

I do not need a condition when connecting, I just want to get 5 random results from the second table to join the 5 results from the first.

Example:

--------------------------------------------------------
|table1 (always with same order)| table2(random order) |
--------------------------------------------------------
    item1                       |       item4
    item2                       |       item2 
    item3                       |       item5 
    item4                       |       item1
    item5                       |       item3
+3
source share
1 answer

Do you mean UNION?

SELECT * FROM table1
UNION SELECT * FROM table2 ORDER BY RAND() LIMIT 5;

Update : revised answer after changing your question:

SELECT field1 FROM table1
UNION SELECT field2 FROM table2 ORDER BY RAND() LIMIT 5;

As far as I understand, you only need one field from each table. If you need several, you can list them: field2, field2, ... as long as the number of fields is the same in both SELECT.

2: , , , . () , , - :

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;
+2

More articles:


All Articles