Mysql - select a random row from one table and join it to three rows in another

I have two tab开发者_StackOverflowles.

One is holding the titles of games. Another is holding the rankings for the users playing those games.

SELECT r.game, u.username, r.userid, r.points
FROM tbl_game g
INNER JOIN tbl_ranking r ON r.game = g.name
INNER JOIN tbl_users u ON u.id = r.userid
WHERE g.active = '1'
AND r.type = '1'
ORDER BY rand( ) , r.points
LIMIT 3 

This is my attempt.

What I am trying to do is select a random row from the tbl_game table and then join it to the three best users in the tbl_ranking table.

For example, if Madden was randomly selected, it would find the best 3 users for that game and return them.

Any idea how I can do this in one query? Is it possible?

Thanks

EDIT I have now attempted what I think is the correct way to do this. I have syntax errors, if someone could help?

SELECT userid, points(
     SELECT name
     FROM tbl_game
     WHERE active = '1'
     ORDER BY rand( )
     LIMIT 1
     ) AS randgame
FROM tbl_ranking
WHERE TYPE = '1'
AND game = randgame
ORDER BY points DESC LIMIT 3

EDIT...need to add more into the query

There is the need to select Xbox 360 or Playstation 3 from tbl_format and ensure only users on either are compared.

SELECT r.game, u.username, r.userid, r.points
FROM tbl_ranking r
INNER JOIN tbl_users u ON u.id = r.userid
WHERE r.type = '1'
AND r.game = (
       SELECT name
       FROM tbl_game
       WHERE active = '1'
       ORDER BY rand( )
       LIMIT 1 )
AND u.format = (
       SELECT format
       FROM tbl_format
       WHERE active = '1'
       ORDER BY rand( )
       LIMIT 1 )
ORDER BY r.points DESC
LIMIT 3 

The idea is that you join to the user table where they have a field format and they either are ps3 or xbox360. So when you run the query you select either ps3 or xbox360 from the tbl_format table, compare that to the usernames and select three users on one console or the other from a randomly selected game!

Any ideas?

Thanks

---

SELECT userid, points
FROM tbl_ranking
WHERE TYPE = '1'
AND game = (SELECT name
     FROM tbl_game
     WHERE active = '1'
     ORDER BY rand( )
     LIMIT 1
     )
ORDER BY points DESC LIMIT 3

I think this is what you want

---

start with SELECT userid, points, (add a comma after points).