can I join table records RANDOMLY?

[sfullman]

everyone knows this query:

select * FROM
table1 left join table2 ON {condition}

but is it possible to left join one row from table 2 to a row in table 1 on a random basis? this would be helpful for password updates and a few other things.

In short, I know I can select random records but am not sure if can do random joins

[dagon]

its an anathema for a relational database to do something like that. I can't imagine how it would be helpful for password updates - or anything else.

[sfullman]

so, no..

[dagon]

yes, but I would love to know how it would be

sfullman;10945528 wrote:

this would be helpful for password updates and a few other things.

??😕

[sfullman]

two tables, list of people and list of books. Randomly join one book to one person for a "surprise review"; but hey that may be against the law.

I'm not sure how useful finding the trillionth digit of pi is but some people ask those questions 🙂

[dagon]

ok i can see a few uses for that. a little google latter:

SELECT u.login, a.filename FROM users u 
JOIN (SELECT filename, user_id, id AS rand_id FROM assets ORDER BY RAND()) a
ON u.id = a.user_id 
WHERE a.filename IS NOT NULL
GROUP BY user_id
ORDER BY u.login;

should be easy to modify to match your tables.