CODEWITHSUNDEEP
phpmysql
Graham
Graham

Reputation: 1473

MySQL SELECT DISTINCT is outputting duplicates

This query is set to SELECT DISTINCT. However, I get duplicates. It's supposed to show two random page titles / images:

    SELECT DISTINCT
    `$page_type`.title AS title,
    page_images.image_loc AS images
    FROM `$page_type`, page_images
    WHERE page_images.url_title = `$page_type`.url_title AND
    `$page_type`.url_title != '$spot'
    ORDER BY RAND() LIMIT 2

Any idea why this is outputting duplicates?

Upvotes: 0

Views: 333

Answers (2)

Miles Gillham
Miles Gillham

Reputation: 294

Without knowing the contents of your tables or seeing the output it's hard to see where the duplication is coming from. You could have unique url_titles but happen to have a title that is the same for different url_titles, for example. (I'm assuming you don't have a typo and mean 'url_title' rather than 'title' in the 2nd line).

To come at the query a different way (and to eliminate duplicate titles) you could refactor the query to be nested and look like this for both clarity and efficiency and see what you get. I think it might solve the symptoms of your problem. The sub-query gets you unique titles, the main query pairs up the images for you.

SELECT
    `$page_type`.title AS title,
    page_images.image_loc AS images
    FROM `$page_type`, page_images
    WHERE page_images.url_title = `$page_type`.url_title AND
    `$page_type`.title IN

(SELECT DISTINCT
    `$page_type`.title
    FROM `$page_type`
    WHERE `$page_type`.url_title != '$spot'
    ORDER BY RAND() LIMIT 2)

Doing the sub-query in this way eliminates RAND() running over the table join. Generally ORDER BY RAND() is inefficient for large tables, be warned (but we love it just the same).

Upvotes: 0

Raymond Hettinger
Raymond Hettinger

Reputation: 226231

Usually when it looks like SELECT DISTINCT is returning duplicates, there are subtle differences between lines that look the same (because of whitespace or because of encoding issues such as combining pairs).

Upvotes: 1

Related Questions