Left join with SELECT DISTINCT in table 2

August 4, 2022

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’.

>Solution :

 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