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 account.name, contact.name, date, amount, type
FROM transactions, accounts, contacts
WHERE account_id = accounts.id
AND contact_id = contacts.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 account.name, date, amount, type
FROM transactions LEFT JOIN accounts
ON account_id = accounts.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 account.name, contact.name, date, amount, type
FROM FROM transactions LEFT JOIN accounts, contacts
WHERE account_id = accounts.id
AND contact_id = contacts.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”.
