mysql - How to join multiple random values of different tables in a single query? -

i have 3 tables (called results,users,games), first 1 data others, primary auto-increment id this:

results

id      | idusers | idgames --------+---------+----------         |         |          |         |          |         |

users

id      | name    |  --------+---------+ 1       | todd    |  2       | mario   |  3       | luigi   |

games

id      | play    |  --------+---------+ 1       | game1   |  2       | game2   |  3       | game3   |

i wish randomize users id , games id, , join inside results table this:

results

id      | idusers | idgames --------+---------+---------- 1       |  3      |  2 2       |  1      |  1 3       |  2      |  1 (also duplicates ok)

i know randomize id it's supposed use select * users order newid() , join different tables need use inner join,

but how make working in 1 single query?

one method in mysql use rand() , correlated subquery:

select idusers,        (select id games order rand() limit 1) idgames users;

if have reasonable amount of data (more few thousand rows), there more efficient methods.

Macro