CODEWITHSUNDEEP
mysqlsqldatabaseselecttags
user9855939
user9855939

Reputation: 11

Display a many to many relation in sql as list in SELECT

I have some tables that look like this:

CREATE TABLE Games (
    Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Title VARCHAR(100) NOT NULL
);

CREATE TABLE Tags(
    Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Tag VARCHAR(30) NOT NULL
);

CREATE TABLE GamesTags(
    GameId INTEGER NOT NULL,
    TagId INTEGER NOT NULL,
    PRIMARY KEY (GameId, TagId),
    FOREIGN KEY(GameId) REFERENCES Games(Id),
    FOREIGN KEY(TagId) REFERENCES Tags(Id)
);

How do I make a SELECT query that can output the title of the game in one column and all it's tags separated by comma in another. Example:

Title       | Tags                |
-----------------------------------
Fortnite    | survival, action    |
Super Mario | platform, adventure |

Upvotes: 1

Views: 1438

Answers (1)

cdaiga
cdaiga

Reputation: 4939

SELECT 
   A.Title, 
   GROUP_CONCAT(DISTINCT C.Tag ORDER BY C.Tag ASC SEPARATOR ',') Tags
FROM Games A 
LEFT JOIN GameTags B
ON A.Id=B.GameID
LEFT JOIN Tags C
ON B.TagId=C.Id
GROUP BY A.Title;

For insight see:

MySQL Group By

MySQL Join Made Easy For Beginners

MySQL GROUP_CONCAT Function

Upvotes: 2

Related Questions