Tuesday, July 21, 2009

Nested LEFT JOINs to link three or more tables

Posted on Monday 2005.08.15 at 7:38 pm in Everything, Technology

By Ryan McElroy

Note: This post is over 3 years old. It may contain outdated information and broken links. Please temper your expectations accordingly.

I ran into another dilemma in SQL-land today. Working on a money-management application, I had three tables that I wanted to gather information from. This normally leads to some relatively straightforward SQL:

SELECT,, date, amount, type
FROM transactions, accounts, contacts
WHERE account_id =
AND contact_id =;

However, this returned results only for transactions that had both matching accounts and matching contacts, while what I wanted was a single record for each transaction, regardless of matching accounts or contacts. For two tables, this would be straightforward with a LEFT JOIN:

SELECT, date, amount, type
FROM transactions LEFT JOIN accounts
ON account_id =;

Which would return a row for every entry in the transactions table even if no matching account was found. I tried extrapolating that two three tables, like this:

SELECT,, date, amount, type
FROM FROM transactions LEFT JOIN accounts, contacts
WHERE account_id =
AND contact_id =;

However, this didn’t do what I wanted, and I was getting multiple entries for each transaction. So I googled for help, and Google delivered: Left Joins to link three or more tables. From the information in this article, I was able to develop the SQL code that did exactly what I wanted:

SELECT `accounts`.`name` AS account_name,
`contacts`.`name` AS contact_name,
`date`, `amount`, `type`, `detail`, `memo`, `cleared`
FROM (`transactions` LEFT JOIN `accounts` ON `accounts`.`id` = `account_id`)
LEFT JOIN `contacts` ON `contacts`.`id` = `contact_id`
WHERE `parent_id` = 0

This returns a single row for each entry in transactions, even if matching accounts and contacts are not found. Esentially, it is a nested LEFT JOIN. First, MySQL left joins transactions and accounts, then it takes this result and similarly left joins contacts. This way, every row in the leftmost table (transactions) is preserved. I might have simply linked to the site, but I didn’t like the naming scheme in the examples on that site (crypic names like bdg and dom don’t earn accolades from me), and I came up with a better article title, “Nested”.