Complex sql query with join and count

I'm currently encountering a problem in writing my SQL query,

I have 3 tables :

• video_games

id name
1 Halo
2 Hulk

• games_genres

id game_id
1 1 (genre id 1 to game id 1)
2 1 (genre id 2 to game id 1)
2 2 (genre id 2 to game id 2)
3 2 (genre id 3 to game id 2)

• games_tags

id game_id
2 1 (tag id 2 to game id 1)
2 2 (tag id 2 to game id 2)
1 2 (tag id 1 to game id 2)

HERE is what I'd like to have as result , looking for game with most genre IN(2,3) and most tags IN(1)

videos_games.name count_tags common_tags count_genres common_genre
Hulk 1 1 2 2,3
Halo 0 empty 1 2

I currently have this, which is a syntax error

SELECT name,id,
COUNT(distinct games_genres.id) as num_genres, COUNT(distinct
games_tags.id) as num_genres INNER JOIN games_genres ON
(videos_games.id = games_genres.game_id) INNER JOIN games_tags ON
(videos_games.id = games_tags.game_id)
WHERE
games_to_genres.genre_id IN(3,10) games_tags.genre_id IN(31,7)
ORDER BY
deus_games.num_genres DESC


If anyone ever done that, I'll be reaaally thankfull !

Continue reading...