JohnHC JohnHC - 3 months ago 14
SQL Question

Joining on optional

I have 2 tables:

Table1 - Criteria
Office_ID Bus_Stream Bus_Criteria Crit_Value
1 ABC 0 20
1 ABC 1 21
1 ABC 2 7
2 ABC 0 15
2 ABC 1 12
2 ABC 2 21
3 XYZ 1 17
3 XYZ 2 3


Table2 - Limit
Bus_Stream GroupID TypeID SubgroupID Bus_Limit
ABC 20 21 7 50
ABC 15 12 21 100
XYZ 99 17 3 120


I need to create a join that allows me to pull back:

Result
Bus_Stream Office_ID GroupID TypeID SubgroupID Bus_Limit
ABC 1 20 21 7 50
ABC 2 15 12 21 100
XYZ 3 (null) 17 3 120


Essentially, I need to join Table1.Crit_Value based on the following:

Table1.Bus_Criteria Table2
0 GroupID
1 TypeID
2 SubGroupID


with the added complication that if one or two of the 0/1/2 values from Bus_Criteria is missing, the joins will still occur on the remaining criteria.

I have tried a number of combinations of AND/OR on the join to no avail.

Any ideas folks?

Answer

This may be what you're after.. use a case statement on the join.

The problem here is your data in t2 isn't normalized, you could also unpivot the 3 data columns in t2 so the join is more natural.

SELECT T2.Bus_Stream, T1.Office_ID, T2.GroupID, T2.TypeID, T2.SubGroupId, T2.bus_Limit
FROM T1
INNER JOIN T2
 on T1.Bus_Stream = T2.Bus_Stream
and T1.Crit_value = case when T1.Bus_Critiera = 0 then T2.GroupID
                         when T1.Bus_Critiera = 1 then T2.TypeID
                         when T1.Bus_Critiera = 2 then T2.SubGroupID
                    end