{"id":325,"date":"2015-01-08T19:34:25","date_gmt":"2015-01-09T00:34:25","guid":{"rendered":"http:\/\/www.lektrikpuke.com\/blog\/?p=325"},"modified":"2015-02-03T11:19:50","modified_gmt":"2015-02-03T16:19:50","slug":"mysql-query-using-derived-temp-table-and-left-join-against-self","status":"publish","type":"post","link":"https:\/\/lektrikpuke.com\/blog\/2015\/01\/08\/mysql-query-using-derived-temp-table-and-left-join-against-self\/","title":{"rendered":"MySQL Query Using Derived Temp Table And Left Join Against Self"},"content":{"rendered":"<p>An example (for a personal project) where I&#8217;m using a derived temp table (designated &#8216;x&#8217;) and a table joined against itself, used to find the max timestamp.  Thanks, Martin!<\/p>\n<pre style=\"font-size:12px; line-height:1.1em; letter-spacing:.002em; word-spacing:.005em;\">\r\nselect tt.*, fkid, max(fldTimeStamp) as `maxTimeStamp`, (count(*)-1) as `calcCount`\r\n\tfrom tbl_todo tt\r\n\tleft join tbl_todo_stackable tts on tts.fkid = tt.id\r\n\twhere\r\n\t\tfldEnabled = 1\r\n\t\tand fldRecur in (0, 1)\r\n\tgroup by tts.fkid\r\n\thaving date(`maxTimeStamp`) <> date(now())\r\nunion    \r\n\tselect tt2.*, fkid, max(fldTimeStamp) as `maxTimeStamp`, 0 as `calcCount`\r\n\tfrom tbl_todo tt2\r\n\tleft join tbl_todo_stackable tts2 on tts2.fkid = tt2.id\r\n\twhere\r\n\t\tfldEnabled = 1\r\n\t\tand fldRecur in (0, 1)\r\n\tgroup by tts2.fkid\r\n\thaving max(fldTimeStamp) = fldCreateTimeStamp\r\nunion\r\n\tselect tt3.*, fkid, fldTimeStamp as `maxTimeStamp`, 0 as `calcCount`\r\n\tfrom tbl_todo tt3\r\n\tleft join tbl_todo_stackable tts3 on tts3.fkid = tt3.id\r\n\twhere \r\n\ttts3.pkid_todo_stackable in (\r\n\t\tselect \r\n\t\tpkid_todo_stackable \r\n\t\tfrom (\r\n\t\t\tselect s1.pkid_todo_stackable, s1.fkid, s1.fldTimeStamp, s2.fldTimeStamp as date2\r\n\t\t\tfrom tbl_todo_stackable s1 \r\n\t\t\tleft join tbl_todo_stackable s2 on s1.fkid = s2.fkid \r\n\t\t\tand s1.fldTimeStamp < s2.fldTimeStamp\r\n\t\t\twhere\r\n\t\t\ts1.fkid in (178, 179)\r\n                        -- hardcoded fkid's, but could use subquery\r\n\t\t\thaving isnull(date2)\r\n                        -- using Martin's tbl join against itself to get max timestamp rows\r\n\t\t) x\r\n\t)\r\n\tand (datediff(now(), fldCreateTimeStamp)) % 7 = 0\r\n\tand date(now()) <> date(fldTimeStamp)\r\norder by fldRecur desc, id;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>An example (for a personal project) where I&#8217;m using a derived temp table (designated &#8216;x&#8217;) and a table joined against itself, used to find the max timestamp. Thanks, Martin! select tt.*, fkid, max(fldTimeStamp) as `maxTimeStamp`, (count(*)-1) as `calcCount` from tbl_todo &hellip; <a href=\"https:\/\/lektrikpuke.com\/blog\/2015\/01\/08\/mysql-query-using-derived-temp-table-and-left-join-against-self\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[148],"tags":[5,89,155],"class_list":["post-325","post","type-post","status-publish","format-standard","hentry","category-mysql-computer-stuff","tag-mysql","tag-script","tag-sql-2"],"_links":{"self":[{"href":"https:\/\/lektrikpuke.com\/blog\/wp-json\/wp\/v2\/posts\/325","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/lektrikpuke.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/lektrikpuke.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/lektrikpuke.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/lektrikpuke.com\/blog\/wp-json\/wp\/v2\/comments?post=325"}],"version-history":[{"count":2,"href":"https:\/\/lektrikpuke.com\/blog\/wp-json\/wp\/v2\/posts\/325\/revisions"}],"predecessor-version":[{"id":339,"href":"https:\/\/lektrikpuke.com\/blog\/wp-json\/wp\/v2\/posts\/325\/revisions\/339"}],"wp:attachment":[{"href":"https:\/\/lektrikpuke.com\/blog\/wp-json\/wp\/v2\/media?parent=325"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/lektrikpuke.com\/blog\/wp-json\/wp\/v2\/categories?post=325"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/lektrikpuke.com\/blog\/wp-json\/wp\/v2\/tags?post=325"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}