I have a joins select statement, what I want is, if I am having same tables in two left joins and with exclusive Data then in another table when we are joining Data in ON clause I want to be able to Join one or the other table to get the Data, maybe like a case statement but joining one table in condition over the other.
Here is the example:
<pre>DECLARE @AdminAddressTypeId int
SET @AdminAddressTypeId = (select top 1 AddType.PKAddressTypeLKPId from AddressTypeLKP AddType where AddType.Description = 'Administrative')
-- ,OW.Description as OwnershipType
--,OW.Code +'-'+ OW.Description as OwnershipType
--,MGMT.Description as ManagementType
,PRG.Description as ProgramType
--,C.County_Name as CountyName
,C.PK_Geographic_Location_Code + '-' + C.County_Name as CountyName
--,LET.Description as LegalEntityType
FROM [dbo].[LegalEntity] LE
-- left outer join [dbo].[OwnershipTypeLKP] OW ON LE.FKOwnershipTypeLKPId = OW.PKOwnershipTypeLKPId
-- left outer join [dbo].[ManagementTypeLKP] MGMT ON LE.FKManagementTypeLKPId = MGMT.PKManagementTypeLKPId
inner join [dbo].[ProgramTypeLKP] PRG on LE.FKProgramTypeLKPId = PRG.PKProgramTypeLKPId and PRG.Description = 'Mental Health'
left outer Join [dbo].County C on LE.FKCountyLKPId = C.PKCountyId
--left outer join [dbo].[LegalEntityTypeLKP] LET on LE.FKLegalEntityTypeLKPId = LET.PKLegalEntityTypeLKPId
Left outer join LegalEntityAddress LEA_Admin on LE.PKLegalEntityId = LEA_Admin.FKLegalEntityId and LEA_Admin.FKAddressTypeLKPId = @AdminAddressTypeId and LEA_Admin.IsValid = 1 and ( LEA_Admin.EffectiveDateTo is null or LEA_Admin.EffectiveDateTo > getdate())
--(case when @AAEffectiveDateTo ='' then 0 else @AAEffectiveDateTo end)
left Outer JOIN Address AA ON LEA_Admin.FKAddressId = AA.PKAddressId
Left outer JOIN County Admin_CY on Admin_CY.PKCountyId = AA.FK_County
Left outer join State Admin_S on Admin_S.PKStateId = AA.FK_State
where LE.IsCompleted = 1
What I want is I am checking for only LegalEntityAddress that are Admins, but if Admin Address is not available for that Legal Entity I want to display any address that has the latest closing Date, if it doesn't find any data for these two conditions then I want any first date that's available or the one with highest PK value.
If I can achieve this by having one time join of the table that will be nice if I can't get it by using one same table join or need same table to be joined multiple times, please help me how can I achieve it. If I need to have LegalEntityAddress multiple joins with different conditions then how can I handle that situation in further down tables which are using this LegalEntityAddress reference table to join.
Any help would be very helpful, thanks in advance.