{"id":1389,"date":"2024-12-17T08:27:10","date_gmt":"2024-12-17T15:27:10","guid":{"rendered":"https:\/\/layne.mysyte.us\/?p=1389"},"modified":"2025-05-01T10:59:06","modified_gmt":"2025-05-01T17:59:06","slug":"multi-join-filemaker-executesql-query","status":"publish","type":"post","link":"https:\/\/layne.eqlik.us\/index.php\/archives\/1389","title":{"rendered":"Multi Join FileMaker ExecuteSQL Query"},"content":{"rendered":"\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<pre class=\"wp-block-code\"><code>ExecuteSQL ( \n\"SELECT DISTINCT(a.Name), SUM(ii.x_Days)\n\nFROM Area AS a\n\nINNER JOIN  Destination d ON d.\\\"_kf_area\\\" = a.\\\"__kp_Area\\\"\n\nINNER JOIN  Accommodation accom ON accom.\\\"_kf_Destination\\\" = d.\\\"__kp_Destination\\\"\n\nINNER JOIN  ItineraryItem ii ON ii.\\\"_kf_Accommodation\\\" = accom.\\\"__kp_Accommodation\\\"\n\nINNER JOIN  Trip t ON t.\\\"__kp_Trip\\\" = ii.\\\"_kf_trip\\\"\n\nWHERE t.\\\"__kp_Trip\\\" = ? \n\nGROUP BY a.Name\";\":\" ;\" \/ \" ; Trip::__kp_Trip )<\/code><\/pre>\n<\/blockquote>\n\n\n\n<p class=\"\">I have a client with a database that was over engineered.   Rather build a set of overly complex relationships to find this data I attempted my first multi inner join query with SQL.  The result was highly successful!   Greatly reducing the speeding on reporting pages.<\/p>\n\n\n\n<p class=\"\">The query finds country names, groups them by name, and sums the days the user is in country.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I have a client with a database that was over engineered. Rather build a set of overly complex relationships to find this data I attempted my first multi inner join query with SQL. The result was highly successful! Greatly reducing the speeding on reporting pages. The query finds country names, groups them by name, and sums the days the user is in country.<\/p>\n","protected":false},"author":120,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,139,6],"tags":[137,37,138],"class_list":["post-1389","post","type-post","status-publish","format-standard","hentry","category-computers","category-executesql","category-filemaker","tag-executesql","tag-filemaker-2","tag-inner-join"],"acf":[],"_links":{"self":[{"href":"https:\/\/layne.eqlik.us\/index.php\/wp-json\/wp\/v2\/posts\/1389","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/layne.eqlik.us\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/layne.eqlik.us\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/layne.eqlik.us\/index.php\/wp-json\/wp\/v2\/users\/120"}],"replies":[{"embeddable":true,"href":"https:\/\/layne.eqlik.us\/index.php\/wp-json\/wp\/v2\/comments?post=1389"}],"version-history":[{"count":3,"href":"https:\/\/layne.eqlik.us\/index.php\/wp-json\/wp\/v2\/posts\/1389\/revisions"}],"predecessor-version":[{"id":1392,"href":"https:\/\/layne.eqlik.us\/index.php\/wp-json\/wp\/v2\/posts\/1389\/revisions\/1392"}],"wp:attachment":[{"href":"https:\/\/layne.eqlik.us\/index.php\/wp-json\/wp\/v2\/media?parent=1389"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/layne.eqlik.us\/index.php\/wp-json\/wp\/v2\/categories?post=1389"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/layne.eqlik.us\/index.php\/wp-json\/wp\/v2\/tags?post=1389"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}