SQL Query to get entries with their categories

Question

I have the following query that is grabbing entries, but I would also like a 'categories' column that will show a pipe seperated list of the categories assigned to each entry.

I have tried using CONCAT_WS as below, but the results just duplicate each entry for every category assigned to that entry. I was wondering if someone could tell me where I'm going wrong?

SELECT exp_channel_data.entry_id AS entry_id, exp_channel_titles.title AS title, exp_channel_titles.url_title AS url_title, CONCAT_WS('|',cp.cat_id) AS categories
FROM exp_channel_data
LEFT JOIN exp_channel_titles ON exp_channel_titles.entry_id = exp_channel_data.entry_id
LEFT JOIN exp_category_posts AS cp ON exp_channel_data.entry_id = cp.entry_id
LIMIT 100

Answer 1

I believe this is what you're looking for:

SELECT exp_channel_data.entry_id AS entry_id, exp_channel_titles.title AS title, exp_channel_titles.url_title AS url_title, GROUP_CONCAT(cp.cat_id SEPARATOR '|') AS categories
FROM exp_channel_data
LEFT JOIN exp_channel_titles ON exp_channel_titles.entry_id = exp_channel_data.entry_id
LEFT JOIN exp_category_posts AS cp ON exp_channel_data.entry_id = cp.entry_id
GROUP BY entry_id
LIMIT 100