Click here to Skip to main content
12,070,030 members (60,792 online)
Rate this:
 
Please Sign up or sign in to vote.
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 10:10am
Edited 8-Jan-13 10:17am
Keith Barrow156.4K
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web04 | 2.8.160208.1 | Last Updated 8 Jan 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100