GreenTriangle GreenTriangle - 3 months ago 8
SQL Question

Can you group by multiple rows in SQL?

Say I have a database tracking my videogame collection. My goal is to retrieve rows and convert them to JavaScript objects in this form:

{
title: "Dark Souls",
releaseYear: 2011,
genres: ["RPG", "Action"],
platforms: ["Windows", "Xbox 360", "PlayStation 3"],
completed: false
}


I want to store this data in a SQLite database. My instinct is to organise it like this.

Table: games



title : String
releaseYear : Int
completed : Int (0/1 in lieu of boolean type)


Table: platforms



name : String


Table: genres



name : String


Table: games_genres_link



genres_id : Int
games_id: Int


Table: games_platforms_link



games_id : Int
platforms_id : Int


And I issue a query like

SELECT games.title,
games.releaseYear,
games.completed,
platforms.name as platforms,
genres.name as genres
FROM games
JOIN games_platforms_link on games_platforms_link.games_id = games.rowid
JOIN platforms on games_platforms_link.platforms_id = platforms.rowid
JOIN games_genres_link on games_genres_link.games_id = games.rowid
JOIN genres on games_genres_link.genres_id = genres.rowid
GROUP BY platforms


Hoping to get at least "Windows, Xbox 360, PlayStation" as the platforms field, which I can split later on, just as a starting point. But I still get two separate rows.

I'm reading up in my books and online about this, but I guess I don't quite know the terminology to even search for, because I'm struggling a little. I would really appreciate any tips anyone could offer, even something like letting me know what terms I could search to find relevant material.

Answer

Try with the below query, if you wanted to group the platforms and genres based on title,year and completed

SELECT games.title,
       games.releaseYear,
       games.completed,
       GROUP_CONCAT(platforms.name) as platforms,
       GROUP_CONCAT(genres.name) as genres
FROM games
JOIN games_platforms_link on games_platforms_link.games_id = games.rowid
JOIN platforms on games_platforms_link.platforms_id = platforms.rowid
JOIN games_genres_link on games_genres_link.games_id = games.rowid
JOIN genres on games_genres_link.genres_id = genres.rowid
GROUP BY games.title,games.releaseYear,games.completed