Join two tables in MySQL with random rows from the second

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

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:=@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;

Try use subquery in select. The subquery part pick an id for each row of table1.

SELECT
  id AS table1_id,
  (
    SELECT id FROM table2 ORDER BY RAND() LIMIT 1
  ) AS table2_id
FROM table1

The query result would be like this:

You may join with table2 to select other table2 column:

SELECT table1.*, table2.*
FROM (
  SELECT
    id AS table1_id,
    (
      SELECT id FROM table2 ORDER BY RAND() LIMIT 1
    ) AS table2_id
  FROM table1
) t
JOIN table1 on t.table1_id = table1.id
JOIN table2 on t.table2_id = table2.id