I am not sure what is wrong with my MySQL GROUP_CONCAT query...can you solve it for me please?

Question

I have a MySQL problem which i can't figure out the solution.

I have 2 tables

Table 1-[Book] table

book id | categories | title       | 
   1       |    1,3,5   |  Book 1  | 
   2       |    2,4    |  Book 2   | 
   3       |    1,4    |  Book 3   |

Table 2-[Category] table

category id | category name 
    1       |     Technology
    2       |     Accounting
    3       |     Science
    4       |     Math
    5       |     Chemistry

I need the result to show up like this

RESULT

book id    | categories | title       |   category name
   1       |    1,3,5   |  Book 1  |   Technology,Science,Chemistry
   2       |    2,4     |  Book 2   |   Accounting,Math 
   3       |    1,4     |  Book 3   |   Technology,Math

I tried the below query but i'm not sure what's wrong with it.

SELECT DISTINCT t1.*,(SELECT GROUP_CONCAT(t2.categoryName) FROM `tbl_category` t2 WHERE t2.id IN (t1.categories)) catColumn FROM tbl_books t1 ORDER BY t1.id DESC

If I execute the below query, it is returning the correct values that I need:

SELECT GROUP_CONCAT(categoryName) FROM `tbl_category` t2 WHERE t2.id IN (1,3,5)

RESULT:

Technology,Science,Chemistry

In your query, t1.categories is a text, and not a list of integers. Technically the query is interpreted by `where t2.id IN ('1,3,5')` and not `where t2.id IN (1,3,5)`. But I recommend you to have a look on relational schema. — iguypouf, Jul 23 '20 at 23:39
See https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — Strawberry, Jul 23 '20 at 23:41
yes you're right...i just created a new table with just the bookid & categoryid...it's easier that way. thank you everyone. — Jepoy de Joya, Jul 25 '20 at 22:39

score 0 · Answer 1 · answered Jul 23 '20 at 23:39

Your first effort should go into fixing your schema. You should have a separate table to store the book/category relations, with each tuple in a separate table row. Storing delimited lists in a database table is bad design, and should be always be avoided: see this famous SO question for more details.

For your current set up though, I would recommend a correlated subquery with find_in_set():

select
    b.*,
    (
        select group_concat(c.category_name)
        from category c
        where find_in_set(c.id, b.categories)
    ) category_names
from book b

score 0 · Answer 2 · answered Jul 24 '20 at 00:13

To echo others, the first thing I would do is make a minor change to your table structure and de-couple books from categories, like this:

TABLE BOOK

book id | title       | 
   1       |  Book 1  | 
   2       |  Book 2   | 
   3       |  Book 3   | 

TABLE CATEGORY

category id | category name 
    1       |     Technology
    2       |     Accounting
    3       |     Science
    4       |     Math
    5       |     Chemistry

TABLE BOOK_CATEGORY

id | book_id | category_id
1  |   1     |   1
2  |   1     |   3
3  |   1     |   5
4  |   2     |   2
5  |   2     |   4
6  |   3     |   1
7  |   3     |   4

Finally, to achieve your desired result, execute the following query:

SELECT
   b.book_id,
   group_concat(bc.category_id order by bc.category_id) AS category_id,
   b.title,
   group_concat(c.category_name) AS category_name
FROM 
   book b 
      INNER JOIN 
   book_category bc ON b.book_id = bc.book_id 
      INNER JOIN 
   category c ON c.category_id = bc.category_id 
GROUP BY
   book_id;

yup! perfect! this is what i did. ;) thank you! i only used left join instead of inner joins though...cheers! — Jepoy de Joya, Jul 25 '20 at 22:40

I am not sure what is wrong with my MySQL GROUP_CONCAT query...can you solve it for me please?

2 Answers2