{"id":2053,"date":"2014-12-10T07:21:11","date_gmt":"2014-12-10T15:21:11","guid":{"rendered":"http:\/\/www.wellgolly.com\/?p=2053"},"modified":"2015-04-07T08:46:13","modified_gmt":"2015-04-07T15:46:13","slug":"mysql-working-with-multiple-tables","status":"publish","type":"post","link":"https:\/\/www.wellgolly.com\/?p=2053","title":{"rendered":"MySQL: Working with Multiple Tables"},"content":{"rendered":"<p>As mentioned earlier, I\u2019m re-reading the Visual QuickStart Guide to SQL and working out some examples. I\u2019m using phpMyAdmin, so the examples here have backticks. I\u2019m using two tables. The first is a table of words and definitions. The second is examples of word usage from the internet. <\/p>\n<p>In this query, I\u2019m looking for the words that have the most usage citations. It turns out that the top three are egregious with 21 citations, interlocutor with 15, and trepidation with 15.<\/p>\n<p><pre><code class=\"preserve-code-formatting\">\nSELECT `words`.`word`, COUNT(*)\nFROM `words` \nINNER JOIN `usage`\nON `words`.`word` = `usage`.`word`\nGROUP BY `words`.`word`\nORDER BY COUNT(*) DESC\nLIMIT 20\n<\/code><\/pre><\/p>\n<p>Find all the words that are not in the definition portion &#8211; mostly alternate spellings and plurals. This table has the number of rows that are in the usage table.<br \/>\n<pre><code class=\"preserve-code-formatting\">\nSELECT `words`.`word`, `usage`.`word` \nFROM `words` \nRIGHT OUTER JOIN `usage`\nON `words`.`word` = `usage`.`word`\nORDER BY `words`.`word`&nbsp;&nbsp;ASC\n<\/code><\/pre><\/p>\n<p>After I cleaned up the tables so that every word has at least one usage example and every example is associated with a word I ran the counts again. Just for fun I ran a RIGHT OUTER JOIN or a LEFT OUTER JOIN instead of an INNER JOIN. As you can probably guess, I got the same result with all three methods. <\/p>\n<p>You can also use WHERE syntax and in this case it yields the same result as well.<br \/>\n<pre><code class=\"preserve-code-formatting\">\nSELECT `words`.`word`, `usage`.`word` \nFROM `words`,`usage`\nWHERE `words`.`word` = `usage`.`word`\nGROUP BY `words`.`word`\n<\/code><\/pre><\/p>\n<p>Simulate a FULL OUTER JOIN. In this case, since we cleaned up the database in the previous step, it has the same number of rows as words.<br \/>\n<pre><code class=\"preserve-code-formatting\">\nSELECT `words`.`word`, `usage`.`word`\nFROM `words`\nLEFT OUTER JOIN `usage`\nON `words`.`word` = `usage`.`word`\n\nUNION\n\nSELECT `words`.`word`, `usage`.`word`\nFROM `words`\nRIGHT OUTER JOIN `usage`\nON `words`.`word` = `usage`.`word`\n<\/code><\/pre><br \/>\nBIG difference between UNION and UNION ALL. UNION ALL doesn\u2019t remove duplicates. <\/p>\n<p>While I was cleaning up tables, I looked at my products and product details tables to see if there were any orphaned product details. This query finds the rows in product_details that are not in product:<br \/>\n<pre><code class=\"preserve-code-formatting\">\nSELECT `product`.`name`, `product_details`.`product_id`\nFROM `product`\nRIGHT OUTER JOIN `product_details`\nON `product`.`id` = `product_details`.`product_id`\n<\/code><\/pre><br \/>\nI already know that there are no products without details, otherwise the website would be broken. But this query checks for that.<br \/>\n<pre><code class=\"preserve-code-formatting\">\nSELECT `product`.`name`, `product_details`.`product_id`\nFROM `product`\nLEFT OUTER JOIN `product_details`\nON `product`.`id` = `product_details`.`product_id`\n<\/code><\/pre><\/p>\n","protected":false},"excerpt":{"rendered":"<p>As mentioned earlier, I\u2019m re-reading the Visual QuickStart Guide to SQL and working out some examples. I\u2019m using phpMyAdmin, so the examples here have backticks. I\u2019m using two tables. The first is a table of words and definitions. The second is examples of word usage from the internet. In this query, I\u2019m looking for the &hellip; <a href=\"https:\/\/www.wellgolly.com\/?p=2053\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">MySQL: Working with Multiple Tables<\/span><\/a><\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27],"tags":[],"class_list":["post-2053","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/2053","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2053"}],"version-history":[{"count":0,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/2053\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2053"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2053"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2053"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}