Translate

Saturday, June 29, 2013

SQL - Doing a JOIN With a Case Statement

I needed to do a join between two different tables on a text field. The problem was that the names in the text fields did not always match exactly to each other and some names were very similar to each other. The only solution I could think of match the tables was to do a join using a CASE statement.

Here is the JOIN with CASE statements that I used:

  LEFT OUTER JOIN
  test.dbo.aVend on ((SUBSTRING(test.dbo.aVend.Description, 1, 8) = SUBSTRING(dbo.SubOrganization.Organization, 1, 8))
     and
 (CASE 
      WHEN len(test.dbo.aVend.Description) > 18 and ( SUBSTRING(test.dbo.aVend.Description, 1, 18) = SUBSTRING(dbo.SubOrganization.Organization, 1, 18)) THEN 1 
      WHEN len(test.dbo.aVend.Description) > 18 and ( SUBSTRING(test.dbo.aVend.Description, 1, 18) <> SUBSTRING(dbo.SubOrganization.Organization, 1, 18)) THEN 0
      else 1
   END = 1)
     and
 (CASE 
      WHEN len(test.dbo.aVend.Description) > 14 and ( SUBSTRING(test.dbo.aVend.Description, 1, 14) = SUBSTRING(dbo.SubOrganization.Organization, 1, 14)) THEN 1 
      WHEN len(test.dbo.aVend.Description) > 14 and ( SUBSTRING(test.dbo.aVend.Description, 1, 14) <> SUBSTRING(dbo.SubOrganization.Organization, 1, 14)) THEN 0
   END = 1)
     and
 (CASE 
      WHEN len(test.dbo.aVend.Description) > 12 and ( SUBSTRING(test.dbo.aVend.Description, 1, 12) = SUBSTRING(dbo.SubOrganization.Organization, 1, 12)) THEN 1 
      WHEN len(test.dbo.aVend.Description) > 12 and ( SUBSTRING(test.dbo.aVend.Description, 1, 12) <> SUBSTRING(dbo.SubOrganization.Organization, 1, 12)) THEN 0
   END = 1)
     and
 (CASE 
      WHEN len(test.dbo.aVend.Description) > 11 and ( SUBSTRING(test.dbo.aVend.Description, 1, 11) = SUBSTRING(dbo.SubOrganization.Organization, 1, 11)) THEN 1 
      WHEN len(test.dbo.aVend.Description) > 11 and ( SUBSTRING(test.dbo.aVend.Description, 1, 11) <> SUBSTRING(dbo.SubOrganization.Organization, 1, 11)) THEN 0
   END = 1)         
   )
   where test.dbo.aVend.[Comp ID] not in (501255, 501221, 500342) or test.dbo.aVend.[Comp ID] is null

No comments:

Post a Comment

Thank you for commenting!