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