Search code examples
sqlleft-joindistinct

Left join with SELECT DISTINCT in table 2


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