MySQL Join And Select Random Field

Question

I want to join two tables and return a random field from the second table because there are multiple matches when joining.

For example. I have a users table and a user_posts table. I want to select each user's id, and a random post id and the post's message that they have in the user_posts table. Each user can have multiple posts in the user_posts table.

This answer explains what I'm trying to do, but it's not working. Here's my query:

SELECT user_id, post_id, message FROM (
    SELECT users.id AS user_id, user_posts.id AS post_id, message
    FROM users INNER JOIN user_posts ON users.id = user_id
    ORDER BY RAND()
) AS a GROUP BY user_id

For testing, I added two rows in the user_posts table for user with id of 1 but it's retrieving the same post every time.

RoMEoMusTDiE · Answer 1 · 2017-04-21T03:58:33.973

0

using cross apply to get random one post and join to users to display

 select u.id,RandomPostbyUser.* 
     from users u
     cross apply (     
        SELECT  user_posts.id AS post_id, message
        FROM users u1 INNER JOIN user_posts ON u1.id = u.id
        order by RAND()
        LIMIT 1) RandomPostbyUser

edited Apr 21 '17 at 03:58

answered Apr 21 '17 at 02:59

RoMEoMusTDiE

4,739
1
17
26

I don't understand, this doesn't even compile? – Altherat Apr 21 '17 at 03:24
Working now, but it returns all posts for each user. I just want one random one. – Altherat Apr 21 '17 at 03:38
can you test again. since you only want 1 – RoMEoMusTDiE Apr 21 '17 at 03:42
Sorry, I want one random post for each user, not just one user's random post. – Altherat Apr 21 '17 at 03:48
try again mate. – RoMEoMusTDiE Apr 21 '17 at 03:59
I'm using MySQL which doesn't have cross apply. Do you know of how to get it working with MySQL? – Altherat Apr 21 '17 at 05:07
You can check this link http://stackoverflow.com/questions/36869221/cross-outer-apply-in-mysql – RoMEoMusTDiE Apr 21 '17 at 06:04

MySQL Join And Select Random Field

1 Answers1