question

looking deep down at the query i can see problem is below lef t join on Masterlocation table. Any suggestions how to avoid left join to same table without impacintg the query output?

left join MasterLocations ml1 ON ml1.MarketingSubRegion = PL.MarketingSubRegion
left JOIN MasterLocations ml2 ON ml2.SubRegionISO = PL.SubRegionISOCode
left JOIN MasterLocations ml3 ON ml3.RegionName = PL.Region

[IndexPageNumber] = (
SELECT TOP 1 NumberOfIndexPages FROM [EditionBrief]
INNER JOIN [EditionBriefRegionalIndexPages] ON [EditionBriefRegionalIndexPages].[fkEditionBriefId] = [EditionBrief].[EditionBriefId]
WHERE [fkBoxId] = [P].[ProductId] AND fkMarketingRegionsId =

CASE
WHEN cntry.CountryId <> @BoxCountryId THEN mr2.MarketingRegionId
WHEN (@BoxCountryId = @ItalyCountryId AND BU.UniverseId IN (@WellnessUniverseId, @GastronomyUniverseId)) THEN
CASE PL.City
WHEN 'Roma' THEN @RomaMarketingRegionId
WHEN 'Milano' THEN @MilanoMarketingRegionId
ELSE mr1.MarketingRegionId
END
ELSE mr1.MarketingRegionId
END
AND EditionBrief.fkSpecimenId = @BoxCodeS
),
[BoxFormat] = STUFF((SELECT ',' + [Format].[Name] FROM [AvailableProductFormat] INNER JOIN [Format] ON [Format].[FormatId] = [AvailableProductFormat].[fkFormatId] WHERE [fkProductId]=[P].[ProductId] ORDER BY [PriorityForConcept] ASC FOR XML PATH('')),1,1,''),
[IndexLayout] = (SELECT TOP 1 edb.IndexLayout FROM EditionBrief as edb WHERE fkBoxId = @BoxId AND fkSpecimenId = @BoxCodeS),
[PartnerPagesLayout] = (SELECT TOP 1 edb.PartnerPagesLayout FROM EditionBrief as edb WHERE fkBoxId = @BoxId),
[BrandPartnership] = (SELECT ISNULL([BrandPartnership].[Name],'none') FROM [Product] LEFT JOIN [BrandPartnership] ON [BrandPartnership].[BrandPartnershipId] = [Product].[fkBrandPartnershipId] WHERE [Product].[ProductId] = [P].[ProductId])
Into #BoxExtract2
From Product P
Inner Join RelatedProduct RP with (nolock) on RP.fkProductId = P.ProductId and RP.Active = 1
Inner Join PartnerProduct PP with (nolock) on PP.fkProductId = RP.fkProduct1Id and PP.Active = 1
Inner Join Partner PR with (nolock) on PR.PartnerId = PP.fkPartnerId and PR.PartnerStatus = 1
Inner Join PartnerLocation PL with (nolock) on PL.fkPartnerId = PR.PartnerId
-- BEGINNING Modification DB
inner join Country cntry ON cntry.CountryName = PL.Country
left join MasterLocations ml1 ON ml1.MarketingSubRegion = PL.MarketingSubRegion
left JOIN MasterLocations ml2 ON ml2.SubRegionISO = PL.SubRegionISOCode
left JOIN MasterLocations ml3 ON ml3.RegionName = PL.Region

left join MarketingRegionClassification mrc on mrc.fkUniverseId = @BoxUniverse and mrc.fkBoxCountryId = @BoxCountryId AND mrc.fkMasterLocationId IS NULL AND mrc.fkPartnerCountryId = cntry.CountryId

left join MarketingRegionClassification mrc2 on mrc2.fkUniverseId = @BoxUniverse and mrc2.fkBoxCountryId = @BoxCountryId
AND mrc2.fkMasterLocationId = isnull(ml1.MasterLocationsId,isnull(ml2.MasterLocationsId,ml3.MasterLocationsId)) AND mrc2.fkPartnerCountryId = cntry.CountryId

left join MarketingRegionClassification mrc3 on mrc3.fkUniverseId = @BoxUniverse and mrc3.fkBoxCountryId = @BoxCountryId AND mrc3.fkMasterLocationId is null AND mrc3.fkPartnerCountryId = cntry.CountryId
left join MarketingRegion mr1 on mr1.MarketingRegionId = mrc2.fkMarketingRegionId
left join MarketingRegion mr2 on mr2.MarketingRegionId = mrc3.fkMarketingRegionId