I think your mistake is the following : db(where clauses).select(tables and join)
So your query should read like this : rows=db(WHERE CLAUSE IF YOU HAVE SOME OTHER THAN THE JOIN).select(db.parent.ALL, db.child.ALL, db.grandchild.ALL, join=db.child.on(db.child.**parent_id== db.parent.id), left=db.grandchild.on(db.**child.id <http://db.child.id/> ==db.grandchild.child_**id)) Richard On Thu, Apr 25, 2013 at 8:38 AM, Philip Kilner <p...@xfr.co.uk> wrote: > Hi, > > I have three tables, say "parent", "child" and "grandchild", and want to > do an inner join on parent and child, and left outer join on child & > grandchild. So, all parents with children, and all grandchildren if they > exist. > > However, I can't accomplish this in a single query, and have been staring > at it long enough to need some input! > > I want to do something like: - > > selected=db(db.parent.ALL, db.child.ALL, db.grandchild.ALL, > join=db.child.on(db.child.**parent_id==db.parent.id), > left=db.grandchild.on(db.**child.id <http://db.child.id> > ==db.grandchild.child_**id) > ) > > ...which isn't right but hopefully gets across what I'm after. > > How should I be combining the two types of join? > > > -- > > Regards, > > PhilK > > > e: p...@xfr.co.uk - m: 07775 796 747 > > 'work as if you lived in the early days of a better nation' > - alasdair gray > > -- > > --- 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+unsubscribe@**googlegroups.com<web2py%2bunsubscr...@googlegroups.com> > . > For more options, visit > https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out> > . > > > -- --- 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/groups/opt_out.