TSQL Msg 1013 “Use correlation names to distinguis

问题:

I looked trough many suggestions and can't figure how to solve this one for the last two hours.

SET DATEFORMAT DMY

DECLARE @Source DATETIME = '01/01/2001'
DECLARE @Destenaition DATETIME = '01/01/2020'

SELECT ST.[Group],
       ST.Shop,
       SUM(ST.Purchased) AS Total,
       CHG.Charged
FROM   (SELECT Personals.Groups.[Name]      AS 'Group',
               Cards.vPurchases.PersonalID,
               Personals.Registry.[Name],
               SUM(Cards.vPurchases.Ammont) AS Purchased,
               Cards.vPurchases.ShopName    AS Shop
        FROM   Cards.vPurchases
               INNER JOIN Personals.Registry
                 ON Personals.Registry.Id = Cards.vPurchases.PersonalID
               INNER JOIN Personals.Groups
                 ON Personals.Registry.[Group] = Personals.Groups.Id
               INNER JOIN Personals.Groups
                 ON Personals.Groups.Id = CHG.GroupID
        WHERE  Cards.vPurchases.[TimeStamp] >= @Source
               AND Cards.vPurchases.[TimeStamp] <= @Destenaition
        GROUP  BY Cards.vPurchases.PersonalID,
                  Personals.Registry.[Name],
                  Personals.Groups.[Name],
                  Cards.vPurchases.ShopName) ST,
       (SELECT PG.Id                      AS GroupID,
               SUM(Cards.vCharges.Amount) AS Charged
        FROM   Cards.vCharges
               INNER JOIN Personals.Registry
                 ON Personals.Registry.Id = Cards.vCharges.PersonalID
               INNER JOIN Personals.Groups AS PG
                 ON Personals.Registry.[Group] = PG.Id
        WHERE  Cards.vCharges.[TimeStamp] >= @Source
               AND Cards.vCharges.[TimeStamp] <= @Destenaition
        GROUP  BY Personals.Groups.[Name]) AS CHG
GROUP  BY ST.Shop,
          ST.[Group]  

And then I get this error:

Msg 1013, Level 16, State 1, Line 6 The objects "Personals.Groups" and "Personals.Groups" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

Thanks.

回答1:

You are using the table Personals.Groups two times in the first sub query. If you really mean to have the table Personals.Groups you need to give them an alias that you then use instead of the table names in the rest of the query.

INNER JOIN Personals.Groups as PG1

and

INNER JOIN Personals.Groups as PG2

If you only need one you can combine the on clauses to use just one instead.

INNER JOIN Personals.Groups 
  ON Personals.Registry.[Group] = Personals.Groups.Id and
     Personals.Groups.Id = CHG.GroupID