Left outer join with NVL as part of the join criteria
Hi,
I have query like this:
Select
Main.X_ID,
NVL(Option1.Y_ID, Option2.Y_ID),
Main.Z_ID
from TableMain Main
left join (Select X_ID, Y_ID from TableOption where type_cd = 'Type1') Option1
on Main.X_ID = Option1.X_ID
left join (Select X_ID, Y_ID from TableOption where type_cd = 'Type2') Option2
on Main.X_ID = Option2.X_ID
left join TableSub Sub
on Main.Z_ID = Sub.Z_ID
and Sub.Y_ID = NVL(Option1.Y_ID, Option2.Y_ID)
where Sub.Z_ID is null and Sub.Y_ID is null
Basically i want to show all in Z_IDs TableMain that are not in TableSub where the joining Y_ID is Type1 and if there is not a Y_ID for Type1, then use the Y_ID for Type2.
The query works if Type1 exists but doesnt if Type1 doesnt and Type2 does.
Is NVL the correct function to use in the join? Or is there a better way to write such a query?
Any help would be greatly appreciated. Thanks!!
FYI all IDs are NUMBERs.