CODEWITHSUNDEEP
mysqlexpressionengine
Adam George
Adam George

Reputation: 177

Mysql get unique entries with two left joins

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

Upvotes: 2

Views: 302

Answers (1)

Bohemian
Bohemian

Reputation: 425073

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.

Upvotes: 1

Related Questions