I'm having trouble doing a query of a mySQL database (see the query below). The query I'm using combines several tables, and I'm getting duplicate entries. When I perform the query without using the City and State columns, the data works fine. BUT, when I add the City and State columns to the query, I get duplicate entries (because some people have more than 1 address).
##############
QUERY
##############
SELECT
distinct newrecords.Participant_Auto_Num,
newrecords.EVENTNAME,
newrecords.YR,
newrecords.FINAL_TIME,
participant_name.Lname,
participant_name.Fname,
addresses.City,
addresses.State
FROM
newrecords,
participant_name,
contact_address,
contact_participant_nav,
addresses
WHERE
newrecords.Participant_Auto_Num = participant_name.Participant_Auto_Num
AND
newrecords.EVENTNAME='GHRS'
AND
newrecords.Participant_Auto_Num = contact_participant_nav.Participant_Auto_Num
AND
contact_participant_nav.Contact_Auto_Num = contact_address.Contact_Auto_Num
AND
contact_address.Address_Auto_Num = addresses.Address_Auto_Num
ORDER BY
newrecords.FINAL_TIME ASC LIMIT 0,10
#######################
EXAMPLE OF WHAT's WRONG
#######################
Bob 1990 New York, NY 00:12
Wilma 1990 Stamford, CT 00:13
Wilma 1990 New York, NY 00:13
Suzie 1992 Las Vegas, NV 00:14
Josh 1994 Stamford, CT 00:18
(Wilma appears twice for 1990, but with two different addreses)
#######################
EXAMPLE OF WHAT I NEED
#######################
Bob 1990 New York, NY 00:12
Wilma 1990 Stamford, CT 00:13
Suzie 1992 Las Vegas, NV 00:14
Wilma 1994 Stamford, CT 00:18
Phil 1995 Manhasset, NY 00:25
(wilma appears twice, but the year is different, so that is ok)
