Optional Inner Join

From: we7313 (we7313_at_discussions.microsoft.com)
Date: 11/27/04 

Date: Sat, 27 Nov 2004 13:03:02 -0800

I have a select proc that will take a bunch or criteria parameters. Based on
how many are not null I would like to decide how many inner joins to do.
for example:

select H1.Priceid as HotelPriceId,H2.Priceid as AirPriceId,
H1.VendorPackageId from
(
select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join
(
select VendorPackageId from ValidVendorPackages
where Vendor = 'SBT'
and Sitecode = 'system'
and PackageType = 'AHCF'
)HB on HA.VendorPackageId = HB.VendorPackageId
and
(
CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = 'LAS'
and LengthOfStay = 5
and Ages = 'A2'
and ComponentType = @ComponentType_1
and ValidItemType = @ValidItemType_1
and ItemValue = @ItemValue_1
)
)

)H1 INNER JOIN
(
select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join
(
select VendorPackageId from ValidVendorPackages
where Vendor = 'SBT'
and Sitecode = 'system'
and PackageType = 'AHCF'
)HB on HA.VendorPackageId = HB.VendorPackageId
and
(
CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = 'LAS'
and LengthOfStay = 5
and Ages = 'A2'
and ComponentType = @ComponentType_2
and ValidItemType = @ValidItemType_2
and ItemValue = @ItemValue_2
)
)
)H2 on H1.Priceid = H2.priceId Inner Join
(
select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join
(
select VendorPackageId from ValidVendorPackages
where Vendor = 'SBT'
and Sitecode = 'system'
and PackageType = 'AHCF'
)HB on HA.VendorPackageId = HB.VendorPackageId
and
(
CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = 'LAS'
and LengthOfStay = 5
and Ages = 'A2'
and ComponentType = @ComponentType_3
and ValidItemType = @ValidItemType_3
and ItemValue = @ItemValue_3
)
)
)H3 on H2.Priceid = H3.priceId

if values are only passed in from
@ComponentType_1,@ValidItemType_1,@ItemValue_1 I dont want to do any inner
joins.

If its passed in for @ComponentType_1,@ValidItemType_1,@ItemValue_1 &
@ComponentType_2,@ValidItemType_2,@ItemValue_2 I want to do the first Inner
Join.

and of course if I get all 3 sets of criteria I want to do both the inner
joins.
I know I can cut and past this thing 3 times with an if statement but that
isn't going to be practical. 

-- 
will