Using Distinct

Anon

I need some help on a photo database. I am trying to print out a list of all the surnames in 11 separate fields, and use the distinct command to keep from printing duplicates.
When I try:
$result = mysql_query ("SELECT DISTINCT lname,o1last FROM photo ");
it prints duplicates from the second field.

Is there any way I can compare the names from one field with another one, and eliminate duplicates?

Anon

I forgot to mention that I am using MySql and PHP 4.1
Thanks

mattr

DISTINCT will dump duplicate ROWS, not a particular column.

Try group by.

Anon

Distinct is not like select it doesn't apply to every column after it. So you would have to do something like:

select distinct lname, distinct o1last from photo.

But I would be cautious because this could yield some unexpected results. I'm not completely sure but I would think that the above query would return a union of the results. So you would miss where one was distinct and the other wasn't. Hopefully that makes sense. For example, if you had three entries: Sam A, George A, and Pam C. You might very well end up with only Pam C.

Anon

Samuel is wrong - DISTINCT pulls out the distinct rows, like a GROUP BY on all attributes in the SELECT. His example will give a syntax error.

Anon

The fact that you are trying to do this leads me to believe that your database is not designed correctly.

Anon

Your right, My dB probibly is not set up correct, but when entering thousands of antique photographs I need to list the complete name of everyone in each photo, as well as the date and location it was taken. My individual searches work fine, but what I am trying to do now is set up a hypertext page that will list all the surnames in this table. If I had only one person per photo, it would be easy, but since there are group photo's with 10 or more people, I had to have a way of listing each person in the photo. That is why I have over 10 different fields for last names.
I am new to all this and just didn't know a better way to set it up.