Get all values from intermediate table

I have a mySQL three-table many-to-many setup with link table, simplified here:

categories
  category_id
  category_name

categories2entries
  category_id,
  entry_id

entries
  entry_id
  entry_text

I need to return all entries for a given category name but I want to include a list of all the categories a given entry is attached to. For example, if a search is for Addictions and the entry is also listed in categories Mental health and Young people, I want an output field for each result with all three categories listed.

How do I do this?

Answer

You need direct lookup for to get the list of entries, and backward lookup for to get categories list for each entry. Backward lookup must use another tables copies.

SELECT e.entry_text,
       GROUP_CONCAT(c2.category_name) belongs_to
FROM categories c1
JOIN categories2entries ce1 ON c1.category_id = ce1.category_id
JOIN entries e ON ce1.entry_id = e.entry_id
JOIN categories2entries ce2 ON ce2.entry_id = e.entry_id
JOIN categories c2 ON c2.category_id = ce2.category_id
WHERE c1.category_name = 'Category Name'
GROUP BY e.entry_text

If you need the same for more than one category (maybe even all) then

SELECT c1.category_name,
       e.entry_text,
       GROUP_CONCAT(c2.category_name) belongs_to
FROM categories c1
JOIN categories2entries ce1 ON c1.category_id = ce1.category_id
JOIN entries e ON ce1.entry_id = e.entry_id
JOIN categories2entries ce2 ON ce2.entry_id = e.entry_id
JOIN categories c2 ON c2.category_id = ce2.category_id
  /* WHERE c1.category_name IN ({Category Names list}) */
GROUP BY c1.category_name,
         e.entry_text