Left join with SELECT DISTINCT in table 2-CodePudding

I am trying to do a left join. Table 1 has unique Financekey, and in Table 2 all the rows are duplicated (let's say the query returns 10k rows, but I want 5k). I want to drop duplicates in table 2 when joining. The code above gives an error, but I cannot figure out why - all the solutions I found online have the same code, I can't spot an error. Any ideas?

SELECT 
[XXX].[yyyyy].[Financekey]
      ,[XXX].[yyyyy].[CustomerKey]
      ,[XXX].[yyyyy].[ProfitCenterKey]

      ,[AAA].[bbbbb].[CompanyKey]
      ,[AAA].[bbbbb].[CompanyName]
      ,[AAA].[bbbbb].[ProfitCenterKey]


FROM [XXX].[yyyyy]
    LEFT JOIN (SELECT DISTINCT [CompanyKey]
      ,[CompanyName]
      ,[ProfitCenterKey]
      FROM [AAA].[bbbbb]) 
    ON [XXX].[yyyyy].[ProfitCenterKey]=[AAA].[bbbbb].[ProfitCenterKey]

Msg 103010, Level 16, State 1, Line 1
Parse error at line: 43, column: 2: Incorrect syntax near 'ON'.

CodePudding user response：

 SELECT 
   [XXX].[yyyyy].[Financekey]
  ,[XXX].[yyyyy].[CustomerKey]
  ,[XXX].[yyyyy].[ProfitCenterKey]

  ,zz.[CompanyKey]
  ,zz.[CompanyName]
  ,zz.[ProfitCenterKey]


 FROM [XXX].[yyyyy]
  LEFT JOIN 
  (
   SELECT DISTINCT [CompanyKey]
  ,[CompanyName]
  ,[ProfitCenterKey]
  FROM [AAA].[bbbbb]
 ) zz
ON [XXX].[yyyyy].[ProfitCenterKey]=zz.[ProfitCenterKey]

I would suggest you next time to obfuscate your query more. It is very interesting and entertaining to go through aaa,bb,xx,qq

CodePudding user response：

Looks like you need to provide an alias for your subquery. Something like:

SELECT 
[XXX].[yyyyy].[Financekey]
      ,[XXX].[yyyyy].[CustomerKey]
      ,[XXX].[yyyyy].[ProfitCenterKey]

      ,[AAA].[bbbbb].[CompanyKey]
      ,[AAA].[bbbbb].[CompanyName]
      ,[AAA].[bbbbb].[ProfitCenterKey]


FROM [XXX].[yyyyy]
    LEFT JOIN (SELECT DISTINCT [CompanyKey]
      ,[CompanyName]
      ,[ProfitCenterKey]
      FROM [AAA].[bbbbb]) AS [SSS]
    ON [XXX].[yyyyy].[ProfitCenterKey]=[SSS].[ProfitCenterKey]

I didn't test this.