困难的SQL查询

Question

i have 2 tables i.e. table 1 calls game and table 2 calls game_medias

structure table 1 (game)

-id
-genre_id
-title
-created

etc.

structure table 2 (game_medias)

-id
-game_id
-thumb

etc

example query:

select g.id,g.genre_id,g.title, gm.* 
from games g inner join game_medias as gm 
group by rand(g.id) limit 8;

is it possible to make a query with a extra like " where g.id = '2' " - get the genre_id from it and get into the random 8 only "items" with this genre_id ? i'm not sure if the join is the right solution - maybe someone knows a better way?

regards

Answer 1

There are many ways to do this but I believe this will work:

select g.id,g.genre_id,g.title, gm.* 
from games g inner join game_medias as gm 
where (g.genre_id = (SELECT genre_id FROM games WHERE id = 2))
group by rand(g.id) limit 8;

If there is an index on games.id then it should be quite fast.