{"id":7,"date":"2005-02-27T22:53:10","date_gmt":"2005-02-28T02:53:10","guid":{"rendered":"http:\/\/hoogervorst.dyndns.org\/~arthur\/wordpress\/?p=7"},"modified":"2005-02-27T22:53:13","modified_gmt":"2005-02-28T02:53:13","slug":"left-outer-joins","status":"publish","type":"post","link":"http:\/\/www.hoogervorst.ca\/arthur\/?p=7","title":{"rendered":"Left Outer Joins"},"content":{"rendered":"<p><span class=\"dropcap\">M<\/span>ore database stuff:  the most exciting stuff of databases is actually analyzing the data afterwards (after the regular integrity checks, naturally). It&#8217;s a goldmine, except for that you need to know how to actually capture that data.  Here&#8217;s a tip for the math lovers: if you&#8217;re not sure how your query should be built, think <em><a href=\"http:\/\/mathforum.org\/library\/drmath\/view\/52397.html\">unions<\/a><\/em>. Remember those junior years when you were wasting time drawing circles of collections of apples and pears? They&#8217;re back!\n<\/p>\n<p><!--more--><\/p>\n<p>We all know how easy it&#8217;s to link from one table to another table using plain AND and OR conditions. After doing hundreds of these queries you&#8217;ll find out the following:  when you join multiple tables using primary (or secundary) keys, any data not available in any of the joined tables will not be included in the end result.\n<\/p>\n<p>Sometimes you do want to see all the results. There are a couple of ways you can solve this, but the neatest way is to join tables using a <em>LEFT OUTER JOIN<\/em> statement (Postgres\/Oracle\/MS SQL compatible).\n<\/p>\n<p>Example:  We have two tables, a customer table (<em>ourcustomers<\/em>) and an order table (<em>ourorders<\/em>). The order table is linked to a customer number in the customer number. The customer table has all the regular fields, like address, name and province\/state. For a specified month, we want to see all the orders that went to a specific destination. However we want to see it in a table with every known destination in the <em>ourcustomers<\/em> table\n<\/p>\n<p><code><br \/>\nSELECT c.province, c.country,<br \/>\n  sum(i.total_price) as total_price<br \/>\nFROM ourcustomers c<br \/>\nLEFT OUTER JOIN ourorders i on<br \/>\n  (ourorders.customer_number = c.customer_number<br \/>\n   AND i.date >= '1\/01\/2005'<br \/>\n   AND i.date < = '1\/31\/2005'\n  )\ngroup by c.province, c.country\norder by total_price DESC, c.province, c.country\n<\/code><br \/>\n<\/code><\/p>\n<p>What basically happens is that <em>first<\/em> all the states\/provinces are listed and then for each of these states\/provinces, the right data is found. If the query didn&#8217;t find values for a specific destination, it would just return &#8216;NULL&#8217; for the total_price value.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>More database stuff: the most exciting stuff of databases is actually analyzing the data afterwards (after the regular integrity checks, naturally). It&#8217;s a goldmine, except for that you need to know how to actually capture that data. Here&#8217;s a tip &hellip; <a href=\"http:\/\/www.hoogervorst.ca\/arthur\/?p=7\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[15],"tags":[],"_links":{"self":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/posts\/7"}],"collection":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=7"}],"version-history":[{"count":0,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/posts\/7\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=7"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=7"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}