Hello all,
I'm trying to perform a double left join, but can't seem to figure out the right syntax. Maybe someone could shed some light... This is what should be the resulting MySQL syntax: SELECT * FROM OnResource LEFT JOIN Contact Owner ON Owner.id = OnResource.Contact LEFT JOIN Contact Proxy ON Proxy.id = OnResource.Proxy ORDER BY Owner.LastName, Owner.FirstName I tried this DAL select syntax timesheet_owners = db().select(db.OnResource.ALL, db.Contact.with_alias('Owner').ALL, db.Contact.with_alias('Proxy').ALL, left=[db.Contact.with_alias('Owner').on(db.Contact.id == db.OnResource.Contact), db.Contact.with_alias('Proxy').on(db.Contact.id == db.OnResource.Proxy)], orderby=db.Contact.with_alias('Owner').LastName + db.Contact.with_alias('Owner').FirstName) But this results in following MySQL Query: SELECT OnResource.id, OnResource.Contact, OnResource.Proxy, OnResource.Manager, OnResource.CID, OnResource.UserID, OnResource.Category, OnResource.Department, OnResource.ResourcePool, OnResource.StartDay, OnResource.EndDay, OnResource.JobDescription, OnResource.EducationDegree, Owner.id, Owner.Title, Owner.Gender, Owner.FirstName, Owner.LastName, Owner.EMail, Owner.ContactLocation, Proxy.id, Proxy.Title, Proxy.Gender, Proxy.FirstName, Proxy.LastName, Proxy.EMail, Proxy.ContactLocation FROM OnResource CROSS JOIN Contact LEFT JOIN Contact AS Owner ON (Contact.id = OnResource.Contact) LEFT JOIN Contact AS Proxy ON (Contact.id = OnResource.Proxy) ORDER BY CONCAT(Owner.LastName,Owner.FirstName) Could you please advise how to get rid of the cross join? Thanks, Lieven -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.