>I'm trying to build an updateable, parameterized, local view from three source tables, where one of the tables is used twice, and my (limited) SQL experience isn't enough. I've made several attempts that were wrong, including FORCE, so it's time to ask for help.
>
>LEADS iLeadPrimaryKey, iSourceKey, iNoteKey
>SOURCE iSourcePrimaryKey, iNoteKey
>NOTES iNotePrimaryKey
>
>LEADS should join SOURCE on Leads.iSourceKey=Source.iSourcePrimaryKey
>LEADS should join NOTES on Leads.iNoteKey = Notes.iNotePrimaryKey
>^ This works fine.
>NOTES should join SOURCE on Source.iNoteKey = Notes.iNotePrimaryKey
>^ Adding this gives either an empty result set, or with Notes being duplicate values, depending. It should give a different set of Notes.
If you're using the same twice as if it was two tables, you should give this second instance of the table a different alias. If I understood well,
select * from leads ;
{left/full/right} join leads on Leads.iSourceKey=Source.iSourcePrimaryKey;
... join NOTES on Leads.iNoteKey = Notes.iNotePrimaryKey ;
... join source s2 on S2.iNoteKey = Notes.iNotePrimaryKey