Joining 3 tables Syn-Key troubles

[JimTheNoob]

Hi All,

I'm experiencing a bit of a mental block.  I've tried Left, Right, Inner, Concatenate and Keep in different combinations to no avail.   

I simply want to create row where the OptionSelected = OptionNumber.   And all other combinations can be removed.  I assume a resident SQL might be in order?  Qvw attached

[Lisa_P]

Try this:

OptionTable:
Load *, EventID&'|'&OptionNumber as EvOpt_Key;
LOAD* Inline [EventID,OptionNumber, OPTIONTEXT, OPTIONACTION
Event2, 1, CashOption, CASH
Event2, 2, StockOption, STOK
Event2, 3, SplitOption, SPLIT
Event2, 4, NoAction, NOAC
Event1, 1, StockOption, STOK
Event1, 2, CashOption, CASH
Event1, 3, CashOption, Split
Event1, 4, NoAction, NOAC
]
;

//AccountTable:
AccTemp:
LOAD* Inline [EventID, ACCOUNTID
Event2, Account2
Event2, Account1
Event1, Account2
Event1, Account1] ;

//ElectionTable:
Left Join(AccTemp)
LOAD* Inline [EventID, ACCOUNTID, OptionSelected
Event2, Account2, 1
Event1, Account1, 1
Event1, Account2, 3
Event2, Account1, 1] ;

AccountTable:
Load ACCOUNTID, OptionSelected,
EventID&'|'&OptionSelected as EvOpt_Key
Resident AccTemp;

Drop Table AccTemp;