Does this work? count = db.dog.id.count() rows=db().select(db.person.name, count, groupby=db.person.id, left=db.dog.on(db.person.id==db.dog.owner))
On Jul 23, 2:08 pm, mwolfe02 <michael.joseph.wo...@gmail.com> wrote: > The example under Grouping and Counting returns: > > Alex 2 > Bob 1 > > In other words, it performs an INNER JOIN not an OUTER JOIN. This is > a critical difference. In this simple example we don't get a row of > information for 'Carl' because he owns no dogs. It may not be a big > deal for such a simple, contrived example, but there are cases where > it is an absolute show-stopper. > > On Jul 23, 2:48 pm, "mr.freeze" <nat...@freezable.com> wrote: > > > Doesn't the example under Grouping and Counting do what you want? > > > On Jul 23, 1:41 pm, Michael Wolfe <michael.joseph.wo...@gmail.com> > > wrote: > > > > Expanding on the LEFT OUTER JOIN example from > > > here:http://web2py.com/book/default/section/6/6 > > > > How would I build a query using the DAL that would return the number > > > of dogs each owner has? > > > > In MS Access, it is done most easily using two queries: > > > > DogsByOwnerID: > > > SELECT dog.owner, Count(dog.name) AS NumberOfDogs FROM dog GROUP BY > > > dog.owner; > > > > DogsByOwnerName: > > > SELECT person.name, DogsByOwnerID.NumberOfDogs FROM person LEFT JOIN > > > DogsByOwnerID ON person.id=DogsByOwnerID.owner; > > > > Following the example I would expect to see: > > > Alex 2 > > > Bob 1 > > > Carl 0 > > > > Am I stuck doing the entire thing using one big .executesql statement? > > > > Thanks, > > > Mike > >
