Complex sql query with join and count

Question

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 !

Answer 1

I'm assuming you're asking for a COUNT(*) over a GROUP BY query.
You also need to unite a couple result tables.
Something like

SELECT name, COUNT(*)
FROM
(SELECT name
FROM videos_games
INNER JOIN game_tags
ON game_tags.game_id = videos_games.id
WHERE game_tags.id IN(10, 3)
UNION ALL
SELECT name
FROM videos_games
INNER JOIN games_genres
ON games_genres.game_id = videos_games.id
WHERE games_genres.id IN(17, 22)) AS nameslist
GROUP BY NAME
ORDER BY COUNT(*) DESC ;

See example here https://sqltest.net/#1191095

Edit: I see, then to keep the lists of common tags and genres separate, as well as their counts, you'll need something like this

SELECT name, COALESCE (count_tags, 0), common_tags,
 COALESCE (count_genres, 0), common_genres
FROM videos_games
LEFT OUTER JOIN
 (SELECT game_id, COUNT(*) count_tags, GROUP_CONCAT(id SEPARATOR ', ') common_tags
 FROM game_tags
 WHERE id IN (2, 3)
 GROUP BY game_id) AS tag_tab
ON tag_tab.game_id = videos_games.id
LEFT OUTER JOIN
 (SELECT game_id, COUNT(*) count_genres, GROUP_CONCAT(id SEPARATOR ', ') common_genres
 FROM games_genres
 WHERE id IN (1)
 GROUP BY game_id) AS genre_tab
ON genre_tab.game_id = videos_games.id

If you want to ORDER BY the sum of counts, make sure they are numbers.