Click here to Skip to main content
15,885,767 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a query and I am trying to have it return results without duplicates. The problem I think is that when a restaurant is located in more than one city and/or it has many locations within one city. This is what I have so far.

Tables
tblRestaurant RestID, RestName
tblLocations LocationID, RestID
tblCities CityID, CityName
tblAreas AreaID, AreaName

The MySQL code.

SQL
SELECT 
    tblLocations.RestID
    , tblLocations.LocationID
    , tblRestaurants.RestName
    , tblCities.CityName
    , tblAreas.AreaName
FROM 
     tblAreas INNER JOIN 
     (
        tblCities INNER JOIN 
        (
            tblRestaurants INNER JOIN 
                tblLocations 
                ON 
                tblRestaurants.RestID = tblLocations.RestID
        )
       ON 
       tblCities.CityID = tblLocations.CityID
     )
     ON
     tblAreas.AreaID = tblLocations.AreaID
GROUP BY 
     tblLocations.RestID
     , tblLocations.LocationID
     , tblRestaurants.RestName
     , tblCities.CityName
     , tblAreas.AreaName
ORDER BY tblRestaurants.RestName;
Posted
Updated 8-Jan-13 9:17am
v2

Using SELECT DISTINCT should remove duplicates (as CG says) but if the Ids are different, this won't help as these would count as different records.
The join conditions look good (without the schema it is hard to tell) but you could try commenting out the joins to see if there is a bad condition and just selecting on the tblRestaurants table but I think this won't have any effect.

If you have to get distinct names you can just select on this field, but then you'll potentially lose rows that just happen to have the same name. You'll also have the problem of working out which of any duplicate rows is needed if the name is used for anything.
 
Share this answer
 
SELECT DISTINCT will remove duplicates.
 
Share this answer
 
Comments
fjdiewornncalwe 8-Jan-13 16:31pm    
+5. Of Course.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900