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
