EXCELLENT! I can make it work with this little command. Fabulous, Fabio!
>
>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)
>)
>
Kogo Michael Hogan
"Pinky, are you pondering what I'm pondering?"
I think so Brain, but "Snowball for Windows"?
Ideate Web Site