Nested LEFT JOINs to link three or more tables
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”.
August 19th, 2005 at 06:49:18 pm
I’m unclear on why you would be interested in the transaction information along with the accounts and contacts information but not care if they match up. The only reason I can think of to include the accounts and contacts information is if it matches up with the rows you’re fetching from transactions.
If you don’t care about accounts and contacts information, simply leave those two tables out of the query.
There’s gotta be an easier way to do what you’re doing, but I can’t quite figure out the reasoning behind what you’re doing in the first place ;)
I’ve had to query some pretty crazy data before and have never had to do the kind of thing you’re demonstrating here.
August 21st, 2005 at 07:58:56 am
Bernie, I think you misunderstand the information I am going after. Here is the situation: I have a long list of transactions, which may or may not have associated contact people and account information. I want the transactions to display even if the contact person is not defined, and even if the account is not defined. In other words, I want to display all of the transactions, no matter what. But if aditional information happensto be associated with a transaction, I’d like that information as well. In a two-table case, this is a normal straightforward LEFT JOIN. The result is that the table on the left returns all of its rows, even if there is no matching entry in the table on the right. Let me give you an example of where this is neccesary:
Lets say you have a database of students at a school. You have all of the student names in one table, ‘students’. Then you have a table called ‘absences.’ In that table, you store a date and a student_id – this information can be combined to return how many absences each student has:
SELECT name, absences FROM students, absences WHERE student_id = students.id GROUP BY students.id;This would return a list of all students that have absences. But what if I wanted a list of all students including those that have no absences? Then I use a LEFT JOIN:
SELECT name, absences FROM students LEFT JOIN absences ON student_id = students.id GROUP BY students.id;This nifty query returns a row for every student, even if there are no matching records in the absences table. Clearly, LEFT JOIN serves a useful purpose.
However, I wanted to take it a step further. In my table, I have transactions that may or may not have an associated account, and that may or may not have an associated contact. I want all the transactions, even if the account is not defined, and even if the contact is not defined. But in the case that the account or the contact is defined, I want that information too. So the nested LEFT JOIN makes this possible.
I hope that clears things up.
August 21st, 2005 at 09:14:37 pm
I understand now. I was letting the actual data being queried get in the way of the problem. Thanks for clarifying.
August 22nd, 2005 at 09:25:28 am
It’s funny how these things just seem to pop up at the same time…
Database Joins
August 22nd, 2005 at 12:33:38 pm
You shouldn’t have had to nest them to do what you wanted. The following should do the same:
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
October 12th, 2008 at 09:02:38 am
can i use it in MySQL queries?
October 14th, 2008 at 02:29:08 pm
Yes, that’s where I did this query.