How can I write a SQL query which will return me all categories of an item?

Question

I have a SQL query:

SELECT b . * , CONCAT( GROUP_CONCAT( c.name ) ) categories, CONCAT( GROUP_CONCAT( c.id ) ) cids
FROM books b
JOIN categories_of_books cb ON b.id = cb.book_id
JOIN categories c ON c.id = cb.category_id
GROUP BY b.id

And it returns to me in categories alias all categories of books and in cids all categories ids.

And when i adding WHERE clause like this:

WHERE c.id = 10

In categories I`ll have only one category and it is clear. But how can i write SQL query which will return me all categories of books in categories alias with limiting condition:

WHERE c.id = 10

[EDIT 1]

Only variant:

SELECT b . * , GROUP_CONCAT( c.name ) categories, GROUP_CONCAT(CAST(c.id AS CHAR)) cid
FROM books b
JOIN categories_of_books cb ON b.id = cb.book_id
JOIN categories c ON c.id = cb.category_id
WHERE b.id in (SELECT categories_of_books.book_id FROM categories_of_books join categories on categories_of_books.category_id = categories.id WHERE categories.id = 10)
GROUP BY b.id

Do you know other better variants?

Answer 1

What you're asking for, which is basically (if I've interpreted your requirements correctly):

• For all the records in the books table, return one row
• If a book has more than one category assigned, for the column categories to contain "category_1, category_2, category3", i.e. the concatenation of all category names
• Only if one of the categories is categories.id = 10

What you need to do is start by selecting all books that are in the category with id = 10:

SELECT cb.book_id 
FROM   categories_of_books cb
WHERE  cb.category_id = 10

Now you want all books where they're in that subset:

SELECT b.*
FROM   books b
WHERE  b.id IN
(
    SELECT cb.book_id 
    FROM   categories_of_books cb
    WHERE  cb.category_id = 10
)

Now you can transform it back into your complicated grouping query:

SELECT b.*, 
       CONCAT(GROUP_CONCAT(c.name)) categories, 
       CONCAT(GROUP_CONCAT(c.id)) cids
FROM   books b
JOIN   categories_of_books cb 
       ON b.id = cb.book_id
JOIN   categories c 
       ON c.id = cb.category_id
WHERE  b.id IN
(
    SELECT cb.book_id 
    FROM   categories_of_books cb
    WHERE  cb.category_id = 10
)
GROUP BY b.id

There's really no tidier/simpler way that I can think of to achieve this. As an aside, it's worth taking the time to lay your code out using whitespace as I've done above as it's generally considered easier to read that way, anyone who has to maintain your code in the future will probaly thank you for it =)