Click here to Skip to main content
Sign Up to vote bad
good
See more: MySQL
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.
 
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 8-Jan-13 9:10am
Edited 8-Jan-13 9:17am


2 solutions

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.
  Permalink  
SELECT DISTINCT will remove duplicates.
  Permalink  
Comments
Marcus Kramer - 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)

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Christian Graus 368
1 Prasad_Kulkarni 357
2 OriginalGriff 347
3 Ron Beyer 251
4 Prasad Khandekar 245
0 Sergey Alexandrovich Kryukov 7,061
1 Prasad_Kulkarni 4,046
2 OriginalGriff 3,749
3 _Amy 3,450
4 CPallini 3,114


Advertise | Privacy | Mobile
Web03 | 2.6.130619.1 | Last Updated 8 Jan 2013
Copyright © CodeProject, 1999-2013
All Rights Reserved. Terms of Use
Layout: fixed | fluid