Multiple doubled JOIN to the same table

Question

Example 1 (works fine, selects assignedagent):

select cr.callid, cr.callstart, cr.callend, cr.waittime, cr.origin,
cr.origdestination, cr.waitresolution, cr.contacttype, cr.termtype,
u.userlastname as assignedagentname,
"" as answeringagentname from callrecord cr
left join (agentrecord ar left join users u on ar.agentkey=u.userkey)
on (cr.callid=ar.callid and cr.assignedagent=ar.sequencenumber);

Example 2 (works fine, selects answeringagent):

select cr.callid, cr.callstart, cr.callend, cr.waittime, cr.origin,
cr.origdestination, cr.waitresolution, cr.contacttype, cr.termtype,
"" as assignedagentname,
u.userlastname as answeringagentname from callrecord cr
left join (agentrecord ar left join users u on ar.agentkey=u.userkey)
on (cr.callid=ar.callid and cr.answeringagent=ar.sequencenumber);

Example 3 (produces an error, selects both):

select cr.callid, cr.callstart, cr.callend, cr.waittime, cr.origin,
cr.origdestination, cr.waitresolution, cr.contacttype, cr.termtype,
u1.userlastname as assignedagentname,
u2.userlastname as answeringagentname from callrecord cr
left join (agentrecord ar left join users u1 on ar.agentkey=u1.userkey)
on (cr.callid=ar.callid and cr.assignedagent=ar.sequencenumber)
left join (agentrecord ar left join users u2 on ar.agentkey=u2.userkey)
on (cr.callid=ar.callid and cr.answeringagent=ar.sequencenumber);

Examples 1 and 2 work fine and returns true results. In example 3 I have followed advice about aliasing for multiple joins found in this forum, but without success. I work with Informix DB via ODBC driver.

Answer 1

Try removing the parentheses and being sure that all aliases are unique:

select cr.callid, cr.callstart, cr.callend, cr.waittime, cr.origin,
       cr.origdestination, cr.waitresolution, cr.contacttype, cr.termtype,
       u1.userlastname as assignedagentname,
       u2.userlastname as answeringagentname
from callrecord cr left join
     agentrecord ar1
     on cr.callid = ar1.callid and
        cr.assignedagent = ar1.sequencenumber left join
     users u1
     on ar.agentkey = u1.userkey left join
     agentrecord ar2 
     on cr.callid = ar2.callid and 
        cr.answeringagent = ar2.sequencenumber left join
     users u2 on ar2.agentkey = u2.userkey