MySQL Forums
Forum List  »  Newbie

Re: Running a 'Distinct' query returns some results TWICE !?
Posted by: Rob De Marco
Date: August 30, 2004 08:19AM

Hi Ram and thanks for helping.

My create table code (as retrieved by MySQLcc) is:

CREATE TABLE `photos` (
`ID` smallint(6) unsigned NOT NULL auto_increment,
`Title` varchar(100) default '',
`Description` blob,
`Country` varchar(100) default '',
`Place` varchar(100) default '',

Now, the query:

SELECT distinct(country)
FROM `photos`

this works perfectly well [for country]. However, when I substiture 'country' with 'place', then the problem with the double results occurs.

The content of this photo-database is simply the photo's ID, a Title for the photo, a brief description, then the Country and Place (town) where it was taken (overseas holiday).
The reason for wanting the distinct to work is that on my webpage (php) I can allow users to pick from two drop down menus. One to select a Country or the other to select a Place. Then, when they click on Submit, the database only shows photos based on the selection. (this actually works fine, except that where TWO results are returned in the drop down menu, some photos will be available under the first entry and the others under the second one.

Some more info in case it helps your diagnosis (but not sure if relevant):

At some stage during [trying] to resolve the issue I noticed that when I made changes to the title or description fields, then that particular photo would then be displayed under the second result as opposed to the first one. I continued to make changes to the text of the remining photos and sure enough, they all started to show under the second entry of the place (eg. London). Once I made changes to the last remaing photo, the second entry dissapeard completely. However, a) I cannot even replicate that behaviour anymore with other places that show twice, and b) I'm dying to know what actually goes on ... :-)

Again, I appreciate you looking into it

Options: ReplyQuote

Written By
Re: Running a 'Distinct' query returns some results TWICE !?
August 30, 2004 08:19AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.