How to put a multivalued attribute in one column in a query?
database, mysql, sql
Solution
You can use GROUP BY Clause to aggregate results and do string append. So something like:
SELECT bk.Book_Title, GROUP_CONCAT(bt.Book_Id SEPARATOR ', ') FROM BOOK_TAGS bt
JOIN BOOKS bk ON bk.ID = bt.Book_Id
JOIN TAGS t ON t.ID = bt.Tags_Id
GROUP BY bt.Book_Id
Problem
For example, in my database I have 3 tables, - the Books table for storing data about books - the Tags table for storing data about tags - and a link table (the Book_Tags table) for storing information about a book having multiple tags. Its illustrated in the image below... ("M" above the arrow means the many side of the relationship) ``` BOOKS BOOK_TAGS TAGS +----+---------+ +---------+----------+ +----+---------------+ |ID | Title | |Book_Id | Tags_Id | |ID | Tag_Name | +----+---------+ +---------+----------+ +----+---------------+ |1 | Book_1 | 1 M |1 | 1 | M 1 |1 | Tag_Name_1 | |1 | Book_1 |<--------|1 | 2 |-------->|2 | Tag_Name_2 | +----+---------+ |1 | 3 | |3 | Tag_Name_3 | |2 | 1 | +----+---------------+ |2 | 3 | +---------+----------+ ``` My question is, how do i query my database so that i would get a result something like ``` +---------------------------------------------------------+ |ID | Book_Title | Tags | +---------------------------------------------------------+ |1 | Book_1 | Tag_Name_1, Tag_Name_2, Tag_Name_3| |2 | Book_2 | Tag_Name_1, Tag_Name_3 | +---------------------------------------------------------+ ``` And not something like ``` +----------------------------------+ |ID | Book_Title | Tags | +----------------------------------+ |1 | Book_1 | Tag_Name_1 | |2 | Book_1 | Tag_Name_2 | |3 | Book_1 | Tag_Name_3 | |4 | Book_2 | Tag_Name_1 | |5 | Book_2 | Tag_Name_3 | +----------------------------------+ ```