[SOLVED] Join two tables in MySQL with random rows from the second

Issue

I have two tables

The first with only 5 rows

The second with 800 rows

I’m using this query:

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

But I’m getting 5 rows from the first table for each result of the second table.

I don’t need a condition when joining, I just want 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

Solution

Do you mean UNION ?

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

---

Update: revised answer after modification of your question:

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

To my understanding, you just need one field from each table. If you need several ones, you can list them: field2, field2, … as long as the number of fields is the same in both SELECTs.

---

Update 2: ok, I think I see what you mean now. Here is a (dirty) way to do it, I’m quite confident someone can come with a more elegant solution though:

SET @num1=0, @num2=0;

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

Answered By – BenMorel

Answer Checked By – Mary Flores (BugsFixing Volunteer)