{"id":375,"date":"2005-08-15T19:38:26","date_gmt":"2005-08-16T02:38:26","guid":{"rendered":"\/?p=375"},"modified":"2005-08-15T19:38:38","modified_gmt":"2005-08-16T02:38:38","slug":"nested-left-joins-to-link-three-or-more-tables","status":"publish","type":"post","link":"https:\/\/arcanius.silverfir.net\/blog\/nested-left-joins-to-link-three-or-more-tables\/","title":{"rendered":"Nested LEFT JOINs to link three or more tables"},"content":{"rendered":"<p>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:<br \/>\n<code><br \/>\nSELECT account.name, contact.name, date, amount, type<br \/>\nFROM transactions, accounts, contacts<br \/>\nWHERE account_id = accounts.id<br \/>\nAND contact_id = contacts.id;<br \/>\n<\/code><br \/>\nHowever, 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:<br \/>\n<code><br \/>\nSELECT account.name, date, amount, type<br \/>\nFROM transactions LEFT JOIN accounts<br \/>\nON account_id = accounts.id;<br \/>\n<\/code><br \/>\nWhich 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:<br \/>\n<code><br \/>\nSELECT account.name, contact.name, date, amount, type<br \/>\nFROM FROM transactions LEFT JOIN accounts, contacts<br \/>\nWHERE account_id = accounts.id<br \/>\nAND contact_id = contacts.id;<br \/>\n<\/code><br \/>\nHowever, this didn&#8217;t do what I wanted, and I was getting multiple entries for each transaction. So I googled for help, and Google delivered: <a href=\"http:\/\/www.wellho.net\/solutions\/mysql-left-joins-to-link-three-or-more-tables.html\">Left Joins to link three or more tables<\/a>. From the information in this article, I was able to develop the SQL code that did exactly what I wanted:<br \/>\n<code><br \/>\nSELECT `accounts`.`name` AS account_name,<br \/>\n    `contacts`.`name` AS contact_name,<br \/>\n    `date`, `amount`, `type`, `detail`, `memo`, `cleared`<br \/>\n    FROM (`transactions` LEFT JOIN `accounts` ON `accounts`.`id` = `account_id`)<br \/>\n    LEFT JOIN `contacts` ON `contacts`.`id` = `contact_id`<br \/>\n    WHERE `parent_id` = 0<br \/>\n<\/code><br \/>\nThis 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&#8217;t like the naming scheme in the examples on that site (crypic names like bdg and dom don&#8217;t earn accolades from me), and I came up with a better article title, &#8220;Nested&#8221;.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[8],"class_list":["post-375","post","type-post","status-publish","format-standard","hentry","category-everything","tag-technology"],"_links":{"self":[{"href":"https:\/\/arcanius.silverfir.net\/blog\/wp-json\/wp\/v2\/posts\/375","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/arcanius.silverfir.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/arcanius.silverfir.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/arcanius.silverfir.net\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/arcanius.silverfir.net\/blog\/wp-json\/wp\/v2\/comments?post=375"}],"version-history":[{"count":0,"href":"https:\/\/arcanius.silverfir.net\/blog\/wp-json\/wp\/v2\/posts\/375\/revisions"}],"wp:attachment":[{"href":"https:\/\/arcanius.silverfir.net\/blog\/wp-json\/wp\/v2\/media?parent=375"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/arcanius.silverfir.net\/blog\/wp-json\/wp\/v2\/categories?post=375"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/arcanius.silverfir.net\/blog\/wp-json\/wp\/v2\/tags?post=375"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}