That seems to work for this simple example. The actual query I'm trying to write is much more involved, but I'll see if I can apply the concept to my specific problem. You have certainly gotten me a step closer to a solution.
Thanks so much for your help. On Jul 23, 3:22 pm, "mr.freeze" <nat...@freezable.com> wrote: > 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
