Doesn't the example under Grouping and Counting do what you want?
On Jul 23, 1:41 pm, Michael Wolfe <[email protected]> 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
