MySQL Nested and Concat Two Columns?

Question

I have the following tables:

1) tbl_entries:

+----------+-------------+-------------------+
| entry_id | entry_title | entry_category_id |
+----------+-------------+-------------------+
| 10       | Entry 1     | 3                 |
| 20       | Entry 2     | 2                 |
| 30       | Entry 3     | 2                 |
| 40       | Entry 4     | 5                 |
+----------+-------------+-------------------+

2) tbl_categories

+-------------+---------------+
| category_id | category_name |
+-------------+---------------+
| 1           | Animal        |
| 2           | People        |
| 3           | Gadgets       |
| 4           | Entertainment |
| 5           | Business      |
+-------------+---------------+

2) tbl_categories_secondary

+-------------+---------------+
| cs_entry_id | cs_category_id|
+-------------+---------------+
| 10          | 5             |
| 20          | 1             |
| 20          | 3             |
| 20          | 5             |
| 40          | 1             |
| 40          | 4             |
+-------------+---------------+

I want to query all the entries together with its primary and secondary categories (alphabetized & comma separated), such that it will result in:

+-------------+---------------------------------+
| title       | categories                      |
+-------------+---------------------------------+
| Entry 1     | Business,Gadgets                |
| Entry 2     | Animal,Business,Gadgets,People  |
| Entry 3     | People                          |
| Entry 4     | Animal,Business,Entertainment   |
+-------------+---------------------------------+

This is what I have tried in almost a day without any luck:

SELECT tbl_entries.entry_title AS title, addCats.more_categories AS categories
FROM tbl_entries,
(
    SELECT cs_entry_id, GROUP_CONCAT(category_name SEPARATOR ',') AS more_categories
    FROM tbl_categories
    INNER JOIN tbl_categories_secondary
        ON tbl_categories.category_id = tbl_categories_secondary.cs_category_id
    GROUP BY cs_entry_id
) addCats
INNER JOIN categories
    ON tbl_entries.entry_category_id = tbl_categories.category_id

My problem right now is how to get the primary and all the secondary categories of an entry and mix/put them in one column as what I've illustrated above.

Any help will be greatly appreciated :)

Answer 1

try something like below. creted a alias for tbl_categories as tc1 and joined

select tbl_entries.entry_title, concat(group_concat(tbl_categories.category_name),group_concat(tc1.category_name)) inner join tbl_categories on tbl_entries.entry_category_id=tbl_categories.category_id inner join tbl_categories_secondary on tbl_categories_secondary.cs_entry_id =tbl_entries.entry_id inner join tbl_categories as tc1 on tc1.category_id =tbl_categories_secondary.cs_category_id group by tbl_entries.entry_title