Outer join or dynamic query, which is the best way to go?

Question

I have the following tables (I've simplified the data contained in the tables).

RateTable - rate_table_id [int] - rate_table_name [nvarchar(50)]

RateTableUsed - rate_used_id [int] - rate_table_id [int] (key to RateTable.rate_table_id) - customer_id [int]

RateTableExtra - rate_table_extra_id [int] - rate_ extra_id [int] (key to RateExtra.rate_table_id) - rate_used_id [int] (key to RateTableUsed.rate_used_id)

RateExtra - rate_ extra_id [int] - rate_extra_name [nvarchar(50)]

select rate_table_name, rate_table_extra_id, rate_extra_name from RateTableUsed rtu 
    innerjoin RateTable rt on rtu.rate_table_id = rt.rate_table_id 
    innerjoin RateTableExtra rte on rte.rate_table_id = rt.rate_table_id
    innerjoing RateExtr re on rte.rate_extra_id = re.rate_extra_id

The RateExtra contains only 3 values with key's 1, 2 & 3 and names' petrol surcharge, management fee and GST.

This is working fine for it's current purpose. A list of values is being displayed with matching records and the RateExtra is queried only for the rate_extra_name.

So I may have the following results:

• Ratetable1, 1, PetrolSurcharge
• Ratetable1, 2, ManagementFee
• Ratetable2, 3, PetrolSurcharge
• Ratetable4, 4, GST
• Ratetable6, 5, PetrolSurcharge

I've been asked to modify this so that each record that returns now includes records for each value in the RateExtra table. If there are no matching records then data from my RateTableExtra table should come back as NULL. So my data should come back as:

• Ratetable1, 1, PetrolSurcharge
• Ratetable1, 2, ManagementFee
• Ratetable1, NULL, GST
• Ratetable2, 3, PetrolSurcharge
• Ratetable2, NULL, ManagementFee
• Ratetable2, NULL, GST
• Ratetable4, NULL, PetrolSurcharge
• Ratetable4, NULL, ManagementFee
• Ratetable4, 4, GST
• Ratetable6, 5, PetrolSurcharge
• Ratetable6, NULL, ManagementFee
• Ratetable6, NULL, GST

I've tried OUTER joins but they don't seem to be working I'm assuming because the RateExtra data is linked to the RateTableExtra which would return null. I'm now considering creating a dynamic query that will get my original result set, iterate over it checking for rate_extra_id and, if it's not already in the resultset, appending a new row to the results with NULL data where I need it. I'm assuming this would work but I've got a feeling it'd be a killer on performance.

Is there any better way to do this? Hope someone can help, it'd be really appreciated.

Answer 1

Try this:

select 
    rate_table_name, 
    rate_table_extra_id, 
    rate_extra
from 
    RateTableUsed rtu
    inner join RateTable rt on
        rtu.rate_table_id = rt.rate_table_id
    cross join RateExtra re
    left outer join RateTableExtra rte on 
        rte.rate_table_id = rt.rate_table_id
        and rte.rate_extra_id = re.rate_extra_id
order by rt.rate_table_name, re.rate_extra_id

What you're getting with this query is, essentially, a Cartesian Join between RateTable and RateExtra, with a left join to find the values of RateTableExtra that match those pairs. Essentially, this query is bringing back all possible combinations of RateTable and RateExtra, and showing you which ones you have in your RateTableExtra table quickly.

Be careful with Cartesian Joins. They can get out of hand very quickly with reasonably sized tables! Enjoy!