lundi 6 mai 2019

How to incorporate an if statement within a join statement?

I am attempting to join two tables based on five criteria, however there are cases when it may only match on the first 3 criteria. So I want those outcomes if it doesn't match by all 5.

I've checked the right table for duplicates already, I should get the same number of rows as in the left table. I've attempted the following or statement between the 5 criteria and 3 criteria. Can this be modified or can an if statement be incorporated in the code. I am really new to SQL.

select  *
from [dbo].[SecondJoinwithCLFC] as c
left join [dbo].[FALC_long] as d
    on (c.[ST_clfc] = d.[ST] and c.[CountyCode] = d.[CNTY] and c.CROPNAME_clfc = d.CROP and c.[Township] = d.[TWP] and c.[Range] = d.[RGE]) or (c.[ST_clfc] = d.[ST] and c.[CountyCode] = d.[CNTY] and c.CROPNAME_clfc = d.CROP)

There are some cases in my right table d. where TWP AND RGE are 0000,0000 respectively and in these cases when trying to join on all 5 criteria it gives a null, but in this case it does match on the first 3 criteria; this is what I want to output.

Aucun commentaire:

Enregistrer un commentaire