避免重复查询 [英] Avoiding Duplicates Query

问题描述

我有一个查询，我试图让它返回没有重复的结果。我认为的问题是，当一家餐馆位于一个以上的城市和/或它在一个城市内有许多地点时。这就是我到目前为止。



桌子

tblRestaurant RestID，RestName

tblLocations LocationID，RestID

tblCities CityID，CityName

tblAreas AreaID，AreaName



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;

推荐答案

使用SELECT DISTINCT应该删除重复项（如CG所说）但如果Ids不同，这不会有帮助这些将被视为不同的记录。

连接条件看起来很好（没有架构很难说）但你可以尝试注释连接以查看是否存在不良情况并且只需选择 tblRestaurants 表但我认为这没有任何效果。



如果你必须得到不同的名字，你可以选择这个字段，但是你可能会丢失恰好具有相同名称的行。如果名称用于任何事情，你还会遇到解决任何重复行的问题。

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.

SELECT DISTINCT将删除重复项。

SELECT DISTINCT will remove duplicates.

这篇关于避免重复查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！