CODEWITHSUNDEEP
mysql
Andy
Andy

Reputation: 3021

mysql distinct not working

I am trying to avoid picking any two of the same categories or entries using my code below...

Its working well but very occasionally it displays duplicate categories...

    SELECT DISTINCT
    exp_categories.cat_id, exp_categories.cat_name, exp_categories.cat_url_title
      ,exp_category_posts.entry_id, exp_channel_titles.status 
    FROM (exp_categories 
    LEFT JOIN exp_category_posts 
           ON exp_categories.cat_id = exp_category_posts.cat_id) 
    LEFT JOIN exp_channel_titles 
           ON exp_category_posts.entry_id = exp_channel_titles.entry_id 
    WHERE exp_categories.group_id = 2 
      AND exp_category_posts.entry_id IS NOT NULL 
      AND exp_channel_titles.status = 'open' 
    GROUP BY exp_categories.cat_id
    ORDER BY RAND() 
    LIMIT 2

Upvotes: 2

Views: 1964

Answers (2)

newtover
newtover

Reputation: 32094

First of all, you do not need outer JOINS here, since your condition exp_channel_titles.status = 'open' will never be true for NULLs in the right part of the JOINS.

SELECT
    c.cat_id,
    c.cat_name,
    c.cat_url_title,
    cp.entry_id,
    ct.status 
FROM exp_categories c
JOIN exp_category_posts cp 
   ON c.cat_id = cp.cat_id 
JOIN exp_channel_titles ct 
   ON cp.entry_id = ct.entry_id 
WHERE c.group_id = 2 
  AND ct.status = 'open' 
GROUP BY c.cat_id
ORDER BY RAND() 
LIMIT 2

Wihout grouping by cat_id, the query will obviously result in duplicate categories for different entry_id. Grouping should eliminate this, though the expected result is not clear to me.

Upvotes: 0

Michael Krelin - hacker
Michael Krelin - hacker

Reputation: 143279

SELECT DISTINCT is supposed to show you distinct rows, that is distinct combinations of all columns.

Upvotes: 2

Related Questions