Conditional statements in ON clause of joins

simpledeveloper

Hi all, 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:

DECLARE @AdminAddressTypeId int
SET @AdminAddressTypeId = (select top 1 AddType.PKAddressTypeLKPId from AddressTypeLKP AddType where AddType.Description = 'Administrative')
SELECT LE.LegalEntityName
,LE.PKLegalEntityId
,LE.FederalTaxId
,LE.LegalEntityNbr
-- ,LE.LegalEntityBgnDt
-- ,LE.LegalEntityEndDt
-- ,LE.CreatedDate
-- ,LE.CreatedBy
-- ,LE.ModifiedDate
-- ,LE.ModifiedBy
-- ,OW.Description as OwnershipType
--,OW.Code +'-'+ OW.Description as OwnershipType
--,LE.FKOwnershipTypeLKPId
--,MGMT.Description as ManagementType
--,LE.FKManagementTypeLKPId
,PRG.Description as ProgramType
,LE.FKProgramTypeLKPId
--,C.County_Name as CountyName
,C.PK_Geographic_Location_Code + '-' + C.County_Name as CountyName
,LE.FKCountyLKPId
,AA.StreetAddress1
,AA.City
,Admin_S.State_Name
,AA.Zip1
-- ,LE.IsCompleted
--,LE.Comments
--,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.

Mycroft Holmes

I think you are going to need multiple joins to the same table and use nested ISNULL conditions. However your requirement to pick the latest valid address due to different conditions is going to make this a nightmare in a single pass. I would split that logic out to a view that encapsulates the rules and supplies a single address for each entity. Then simply inner join to the view. Make the view generic and you have it available for any procedure ;P

Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP

simpledeveloper

Yeah I did it using table valued functions - thanks for giving me the idea, here is my script for it.

create FUNCTION udf_GetLegalEntityAddress(@leId int)
returns @T table(PKAddressId int,
StreetAddress1 varchar(75),
StreetAddress2 varchar(75),
City varchar(75),
FK_County int,
FK_State int,
Zip1 int,
Zip2 int,
CreatedDate datetime,
CreatedBy varchar(500),
ModifiedDate datetime,
ModifiedBy varchar(500),
MigrationNotes varchar(50),
FKProgramTypeLKPId int,
OldSystemId int,
ValidFlag bit)
AS
begin
DECLARE @AdminAddressTypeId int=0, @AddressId int=0, @effectivedate datetime, @PKLegalEntityAddressId int
SET @AdminAddressTypeId = (select top 1 AddType.PKAddressTypeLKPId from AddressTypeLKP AddType where AddType.Description = 'Administrative')

SET @AddressId = 
(select top 1 FKAddressId from LegalEntityAddress where FKAddressTypeLKPId= @AdminAddressTypeId and FKLegalEntityId=@leId and IsValid = 1)

if ((@AddressId is null) or (@AddressId=0))
begin
	SET @AddressId = 
	(select top 1 FKAddressId from LegalEntityAddress where (EffectiveDateTo is null) and FKLegalEntityId=@leId and IsValid = 1)		
end

if ((@AddressId is null) or (@AddressId=0))
begin
	set @effectivedate = (select max(EffectiveDateTo) from LegalEntityAddress where FKLegalEntityId=@leId and (IsValid = 1))
	if ((@effectivedate is null) or (@effectivedate<='1900-01-01'))
	begin
	 set @PKLegalEntityAddressId = (select max(PKLegalEntityAddressId) from LegalEntityAddress where (FKLegalEntityId=@leId) and (IsValid = 1))
	 SET @AddressId = 
	 (select top 1 FKAddressId from LegalEntityAddress where PKLegalEntityAddressId=@PKLegalEntityAddressId) 
	end
	else
	begin
	 SET @AddressId = 
	 (select top 1 FKAddressId from LegalEntityAddress where FKLegalEntityId=@leId and EffectiveDateTo=@effectivedate) 		 
	end
end
if ((@AddressId is null) or (@AddressId=0))
begin
	SET @AddressId = 
	(select top 1 FKAddressId from LegalEntityAddress where FKLegalEntityId=@leId and IsValid = 1)
end

	insert into @T (
	PKAddressId,
	StreetAddress1,
	StreetAddress2,
	City,
	FK\_County,
	FK\_State,
	Zip1,
	Zip2,
	CreatedDate,
	CreatedBy,
	ModifiedDate,
	ModifiedBy,
	MigrationNotes,
	FKProgramTypeLKPId,
	OldSystemId,
	ValidFlag)
	select top 1 
	PKAddressId,
	StreetAddress1,
	StreetAddress2,
	City,
	FK\_County,
	FK\_State,
	Zip1,
	Zip2,
	CreatedDate,
	CreatedBy,
	ModifiedDate,
	ModifiedBy,
	MigrationNotes,
	FKProgramTypeLKPId,
	OldS

Mycroft Holmes

Neat solution, does it make any difference if your function if you function simply returns the AddressID and then you join the to that ID - I'm not advocating it just curious.

Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP

simpledeveloper

Thank you, sorry late but least I understood it now, what you are saying is right, but if that gives me extra performance I am sure I would do it, but what I thought was that, I will need another join for it - I am not sure which one is better in performance wise, if you are familiar please let me know my friend. Thanks a lot my friend :)