Re: gl_db sql convert where to join

null@example.com (apmuthu) — Fri, 14 Mar 2014 13:04:22 +0000

Then INNER JOIN would be a good replacement for the last join as it mimics the WHERE very well and uses the indices when available.

Re: gl_db sql convert where to join

null@example.com (itronics) — Fri, 14 Mar 2014 09:39:27 +0000

No, this is not good idea. Left join is right when record in the table on the right of join is otpional. This is not the case here.
Janusz

gl_db sql convert where to join

null@example.com (apmuthu) — Wed, 12 Mar 2014 15:14:55 +0000

--- gl/includes/db/gl_db_bank_trans.inc    Mon Mar 10 16:17:11 2014
+++ gl/includes/db/gl_db_bank_trans.inc    Sat Jan 04 02:23:22 2014
@@ -66,17 +66,17 @@
 function get_bank_trans($type, $trans_no=null, $person_type_id=null, $person_id=null)
 {
     $sql = "SELECT bt.*, act.*,
-        IFNULL(abs(dt.ov_amount), IFNULL(ABS(st.ov_amount), bt.amount)) settled_amount,
-        IFNULL(abs(dt.ov_amount/bt.amount), IFNULL(ABS(st.ov_amount/bt.amount), 1)) settle_rate,
-        IFNULL(debtor.curr_code, IFNULL(supplier.curr_code, act.bank_curr_code)) settle_curr
+        IFNULL(ABS(dt.ov_amount), IFNULL(ABS(st.ov_amount), bt.amount)) AS settled_amount,
+        IFNULL(ABS(dt.ov_amount/bt.amount), IFNULL(ABS(st.ov_amount/bt.amount), 1)) AS settle_rate,
+        IFNULL(debtor.curr_code, IFNULL(supplier.curr_code, act.bank_curr_code)) AS settle_curr
 
         FROM ".TB_PREF."bank_trans bt
-                 LEFT JOIN ".TB_PREF."debtor_trans dt ON dt.type=bt.type AND dt.trans_no=bt.trans_no
-                 LEFT JOIN ".TB_PREF."debtors_master debtor ON debtor.debtor_no = dt.debtor_no
-                 LEFT JOIN ".TB_PREF."supp_trans st ON st.type=bt.type AND st.trans_no=bt.trans_no
-                 LEFT JOIN ".TB_PREF."suppliers supplier ON supplier.supplier_id = st.supplier_id,
-             ".TB_PREF."bank_accounts act
-        WHERE act.id=bt.bank_act ";
+                LEFT JOIN ".TB_PREF."debtor_trans dt ON dt.type=bt.type AND dt.trans_no=bt.trans_no
+                LEFT JOIN ".TB_PREF."debtors_master debtor ON debtor.debtor_no = dt.debtor_no
+                LEFT JOIN ".TB_PREF."supp_trans st ON st.type=bt.type AND st.trans_no=bt.trans_no
+                LEFT JOIN ".TB_PREF."suppliers supplier ON supplier.supplier_id = st.supplier_id
+                LEFT JOIN ".TB_PREF."bank_accounts act ON act.id = bt.bank_act
+        WHERE  1 ";
     if ($type != null)
         $sql .= " AND bt.type=".db_escape($type);
     if ($trans_no != null)
@@ -85,7 +85,7 @@
         $sql .= " AND bt.person_type_id = ".db_escape($person_type_id);
     if ($person_id != null)
         $sql .= " AND bt.person_id = ".db_escape($person_id);
-    $sql .= " ORDER BY trans_date, bt.id";
+    $sql .= " ORDER BY bt.trans_date, bt.id";
 
     return db_query($sql, "query for bank transaction");
 }

Converting the WHERE static clause to a LEFT JOIN would be elegant. Joe, can you add this please.

FrontAccounting forum — gl_db sql convert where to join

Re: gl_db sql convert where to join

Re: gl_db sql convert where to join

gl_db sql convert where to join