Re: Left Join Question

On Nov 18, 2008, at 5:48 PM, Ryan Wells wrote:

> While looking through our data layer code today, I ran across this
> query:
>
> SELECT
> tasks.*,
> clients.FirstName,
> clients.LastName,
> clients.MiddleInitial,
> iteminfo.CreatedBy,
> iteminfo.StationId,
> iteminfo.CreatedDate,
> changelog.LastModified,
> changelog.LastModifiedBy,
> changelog.LastModifiedAt,
> ticklers.Due,
> ticklers.Reminder
>
> 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.)
>
> The basic data structure is that we have a todo list that contains a
> list of tasks which may or may not be associated with clients,
> items, log entries, or ticklers (scheduled reminders).
>
> 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?
>
> Thanks!
> Ryan

To answer this better we need to know the exact relations between each
table.
For example, does each task have 0 or more clients, or 1 or more
clients?
If it's 1 or more, then you can properly use a JOIN instead of a LEFT
JOIN, this would make the operation faster I believe.

I hope I did explain myself correctly.

Ries