I want to do a join between two tables - but there are three possible ways I can join them.

If the FK exists, I want to join on that - but if it doesn't, I'll create a join on another field (through an intermediate table) and if there's no corresponding record in the intermediate table, I'll try a join on another field before finally giving up and returning a NULL.

I know I can do three JOIN ... AS ... statements and just have a seperate result field for each option (which I can subsequently check in code with nested VFP IIF's), but I was hoping for a prettier way involving fewer fields in the returned cursor.

Any suggestions from the experts out there?

TIA