hi, this appears to be an old issue already discussed and marked as solved: http://groups.google.com/group/web2py/browse_thread/thread/d7f5e5820176813/4d990c3c7475c48b http://groups.google.com/group/web2py/browse_thread/thread/f4ef82fd34371863/8e7a741d676cea6e but, I got it again :-(
Here is my model: db.define_table('first', Field('f11')) db.define_table('second', Field('f12'), Field('r12_first_id', db.first)) db.define_table('third', Field('f13'), Field('r13_first_id', db.first)) db.define_table('fourth', Field('f14'), Field('r14_second_id', db.second)) # and the problem: db((db.first.id==db.second.r12_first_id) ).select(db.first.ALL, db.second.ALL, db.third.ALL, db.fourth.ALL, left= [db.fourth.on(db.fourth.r14_second_id==db.second.id), db.third.on(db.third.r13_first_id==db.first.id)]) bombs with the postgress error: ProgrammingError: invalid reference to FROM-clause entry for table "second" LINE 1: ...first LEFT JOIN fourth ON (fourth.r14_second_id = second.id)... ^ HINT: There is an entry for table "second", but it cannot be referenced from this part of the query. The inspected sql: SELECT first.id, first.f11, second.id, second.f12, second.r12_first_id, third.id, third.f13, third.r13_first_id, fourth.id, fourth.f14, fourth.r14_second_id FROM second, first LEFT JOIN fourth ON (fourth.r14_second_id = second.id) LEFT JOIN third ON (third.r13_first_id = first.id) WHERE (first.id = second.r12_first_id); The sql which works for me: SELECT * FROM first JOIN second ON second.r12_first_id=first.id LEFT JOIN fourth ON (fourth.r14_second_id = second.id) LEFT JOIN third ON (third.r13_first_id = first.id) ; In my case swapping first and second in sql does not help (I have both as part of LEFT JOIN) I could use executesql, but is there an easy way to reconnect the result of executesql into the rows returned by db(...).select(...)? Is it only a postgress issue? It worked ok with sqlite. Am I doing something fundamentally wrong or the old issue is not 100% fixed? Pawel REF: http://stackoverflow.com/questions/187146/inner-join-outer-join-is-the-order-of-tables-in-from-important