I want to do a join between two tables - but there are three possible ways I can join them.
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 Visual FoxPro IIF's), but I was hoping for a prettier way involving fewer fields in the returned cursor.
Any suggestions from the experts out there?
COALESCE() function:
SELECT * FROM table1 join table2
on table2.primarykey=
COALESCE(
table1.foreignKey
- but if it doesn't, I'll create a join on another field (through an intermediate table)
,(SELECT * FROM intermediateTable where ???=anotherfield)
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.
,(try with another table)
)
