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 !

Sample data and desired results would help. MySQL doesn't support arrays, so that part of your question is unclear. — Gordon Linoff
– Gordon Linoff, Commented Jan 27, 2021 at 16:34
I edited my first post and I'm trying to find the more usable datas I have to explain my problem. I'm not using array as you can see, but more like stringified array like IN(10,3) — Nicolas Gruwe
– Nicolas Gruwe, Commented Jan 27, 2021 at 16:54
Sample data and desired results would help - please add those to the question — Nico Haase
– Nico Haase, Commented Jan 27, 2021 at 17:02
You are specifying the join but you miss from what table. Add the From statement before the first join — user2399035
– user2399035, Commented Jan 27, 2021 at 17:47

Moige · Accepted Answer · 2021-01-27 17:43:36Z

3

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.

edited Jan 27, 2021 at 17:43

answered Jan 27, 2021 at 17:01

Moige

1711 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nicolas Gruwe Over a year ago

Hi Ljisa ! Thank for your help ! This is not really what I need to, I tried to modified your query but with no success so I made tables in my initial post to show you what I really need as results. It look like I just need to splice your query in two, I'll give it a try

Nicolas Gruwe Over a year ago

Oh my god lisja ! This is absolutely what I was looking for ! You are exceptionnal thank you !

Collectives™ on Stack Overflow

Complex sql query with join and count

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related