I have the following mysql query, which is producing a list of entries by category. However, some of the entries produced are duplicates (duplicate entry_id - because they appear in more than one category). How can ensure there are no duplicates? I have attempted to use other variations of GROUP BY and DISTINCT, but without success. Thank you.
SELECT ct.entry_id, ct.title, c.cat_name
FROM exp2_categories c
LEFT JOIN exp2_category_posts cp ON (cp.cat_id = c.cat_id)
LEFT JOIN exp2_channel_titles ct ON (ct.entry_id = cp.entry_id)
WHERE c.group_id = '4'
GROUP BY c.cat_id
ORDER BY ct.entry_date DESC
EDIT:
The first answer is great, except it produces multiple entries per category. I need one unique entry per category. Sorry, I should have been more specific in my original question.
For example, what it produces - Entries are unique, but there are too many entries:
entry_id_1 Title 1 Category_1, Category_2
entry_id_3 Title 3 Category_2
entry_id_345 Title 345 Category_3
entry_id_123 Title 123 Category_4, Category_3, Category_1
entry_id_678 Title 678 Category_4
Desired result - Entries are unique and there is only one entry per category:
entry_id_1 Title 1 Category_1
entry_id_3 Title 3 Category_2
entry_id_345 Title 345 Category_3
entry_id_123 Title 123 Category_4
The reason you are getting "duplicates" is that you are selecting category too, so if an entry is in multiple categories, you'll get multiple rows.
What need is the group_concat() aggregate function:
SELECT ct.entry_id, ct.title, group_concat(c.cat_name) as cat_names
FROM exp2_categories c
LEFT JOIN exp2_category_posts cp ON (cp.cat_id = c.cat_id)
LEFT JOIN exp2_channel_titles ct ON (ct.entry_id = cp.entry_id)
WHERE c.group_id = '4'
GROUP BY ct.entry_id, ct.title
ORDER BY ct.entry_date DESC
This will create one row for each entry, with a comma delimited list of categories it's in.