Re: Left Join Question

Ryan Wells wrote:
>
> FROM tasks
> LEFT JOIN clients ON tasks.ClientId = clients.ClientId
> LEFT JOIN iteminfo ON tasks.Id = iteminfo.ItemId
> LEFT JOIN changelog ON tasks.Id = changelog.ItemId
> LEFT JOIN ticklers ON tasks.Id = ticklers.RelatedId
> WHERE tasks.Id = '123456';
>
> (I've cleaned it up so it's easier to read.)

Thanks Ryan - that always makes it easier.

> The query works as intended: it returns a result-set with all the
> necessary data to display in the todo list. The performance is not a
> major concern, although it can be slow for large lists.
>
> Since it works, my question is really more about principles: Given that
> each of the tables in question will contain tens of thousands of rows,
> is a nested join really the best way to approach this?

For a single query, I don't think you've got any alternative.

For the case when you have large numbers of results from iteminfo etc.
it might well be quicker to do separate queries. That's simple enough
since you're joining straight to tasks.id, but is obviously more fiddly
and when there aren't many rows returned would probably be slower (you'd
have 5 lots of query parsing/execute overhead). That might be a
trade-off that makes sense to you though.

Some other systems allow you to define stored procedures that return
multiple record-sets, but the closest PG can do at the moment is to
return multiple cursors (see plpgsql and refcursor in the manuals) and
that's a bit fiddly to use.

HTH
--
Richard Huxton
Archonet Ltd