I want to be able to create a conditional join between two tables - e.g.
(bit pseudo codeish!).........
from table1 t1
innner join (select case when t3.category = 'RM QUOTE then t3.assigned_dept
else t4.name end as phil
from table3 t3
inner join table4 t4 on t3.name = t4.name) test
on t1.name = test.phil
I know the above does not work - any ideas how to?