how to Join two tables in MySQL with random row from the second table

Question

I have two tables, The first tables with 10 rows, The second with 50 rows

example table 1 :

--------------------------------------------------------
|    Name                                image
--------------------------------------------------------
    jhony                       |      asset/jhony.jpg
    luis                        |      asset/luis.jpg 
    diego                       |      asset/diego.jpg
    carlos                      |      asset/carlos.jpg
    ferry                       |      asset/ferry.jpg

....

Table 2

   --------------------------------------------------------
    |    color                              percent
    --------------------------------------------------------
        red                          |      35%
        red                          |      40%
        red                          |      45%
        green                        |      80% 
        green                        |      90% 
        green                        |      95% 
        green                        |      75%
        yellow                       |      60%
        yellow                       |      65%
.....

i want to join this tables so it will look like this

  ---------------------------------------------------------------------
    |    Name                  image             color         percent
  ---------------------------------------------------------------------
        jhony       |      asset/jhony.jpg    |   red     |    35%
        luis        |      asset/luis.jpg     |   red     |    45%
        diego       |      asset/diego.jpg    |   red     |    35%
        carlos      |      asset/carlos.jpg   |   yellow  |    70%
        ferry       |      asset/ferry.jpg    |   green   |    85%
....

its okay if the tables 2 have a duplicated row on join , but i dont want duplicate name on table 1 , Any idea how to do this query ? Some advice would also be great.

Thanks for your help.

Answer 1

There is a way to join using random values and row numbers. The query is:

select
  t1.name,
  t1.image,
  t1.randval,
  t2.rownum,
  t2.color,
  t2.percent
from
  (
    select *,
      floor(1+rand()*(select count(*) from table2)) randval
    from table1
  ) t1
  join
  (
    select *,
      row_number() over (order by color) rownum
    from table2
  ) t2
  on t1.randval = t2.rownum;

The result is, for example:

+--------+------------------+---------+--------+--------+---------+
| name   | image            | randval | rownum | color  | percent |
+--------+------------------+---------+--------+--------+---------+
| jhony  | asset/jhony.jpg  |       7 |      7 | red    | 45%     |
| luis   | asset/luis.jpg   |       9 |      9 | yellow | 65%     |
| diego  | asset/diego.jpg  |       3 |      3 | green  | 95%     |
| carlos | asset/carlos.jpg |       7 |      7 | red    | 45%     |
| ferry  | asset/ferry.jpg  |       8 |      8 | yellow | 60%     |
+--------+------------------+---------+--------+--------+---------+

If you need a result with no duplicate rows in tale2, the query is:

select
  t1.name,
  t1.image,
  t1.rownum,
  t2.rownum,
  t2.color,
  t2.percent
from
  (
    select *,
      row_number() over (order by name) rownum
    from table1
  ) t1
  join
  (
    select *,
      row_number() over (order by rand()) rownum
    from table2
  ) t2
  on t1.rownum = t2.rownum;