Search the Community

Hi, Here is my SQL, its build with a loop, and looks correct to what I think it should be. I believe the output "$data" below should only be 4 records.. though its a lot more, and some data is duplicated and shown twice in the array. Lot of stuff removed for simplicity. SELECT usr.usr_login, app.app_name, acs.acs_read, acs.acs_add, acs.acs_edit, acs.acs_delete, acs.acs_admin FROM acs LEFT JOIN usr ON usr.ID = usr.ID LEFT JOIN app ON app.ID = app.ID if it matters here's the code that generated the statement above from an array. <?php // ===================================================================================================== // preperation // ===================================================================================================== foreach($fldarray as $fld){ // get readable names and stick em into an array $nm[] = $fld['human']; // Field name, and joins (if the join is using the option table) if($fld['opt'] == 1 and ($fld['opt_table'] == "" or $fld['opt_field'] == "")){ $jn[] = array('table' => "opt", 'field' => "opt_value"); $fd[] = "opt.opt_value"; // Field name, and joins (if the join is using a table other than options) }elseif($fld['opt'] == 1 and ($fld['opt_table'] <> "" and $fld['opt_field'] <> "")){ $jn[] = array('table' => $fld['opt_table'], 'field' => $fld['opt_field']); $fd[] = $fld['opt_table'].".".$fld['opt_field']; // a field directly written no foreign keys.. }else{ $fd[] = $ap.".".$fld['fieldname']; } $sa[] = array('human' => $fld['human'], 'field' => $fld['fieldname'], 'opt_table' => $fld['opt_table'], 'opt_field' => $fld['opt_field']); } // ===================================================================================================== // Start building the SQL // ===================================================================================================== $sql = "SELECT ".implode(", ", $fd)." FROM ".$ap; // ===================================================================================================== // Add the joins if any.. // ===================================================================================================== if (isset($jn)){ foreach($jn as $j){ $sql .= " LEFT JOIN ".$j['table']." ON ".$j['table'].".ID = ".$j['table'].".ID"; } } // ===================================================================================================== $stmt = $db->query($sql); $data = $stmt->fetchAll(); echo $sql; echo "<pre>"; print_r($data); echo "</pre>"; // ===================================================================================================== ?> Tables acs ID | acs_usr | acs_app | acs_read | acs_add | acs_edit | acs_delete | acs_admin ======================================================================================== 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 1 | 1 | 1 | 1 | 0 | 1 | 1 | 0 app ID | app_name ============= 1 | Applications 2 | Users usr ID | login ============= 1 | joe 2 | fred 3 | carlie 4 | lisa outputs this array.. Array ( [0] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [1] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [2] => Array ( [usr_login] => fred [0] => fred [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [3] => Array ( [usr_login] => fred [0] => fred [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [4] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [5] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [6] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [7] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [8] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [9] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [10] => Array ( [usr_login] => fred [0] => fred [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [11] => Array ( [usr_login] => fred [0] => fred [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [12] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [13] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [14] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [15] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [16] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Permissions [1] => Permissions [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [17] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Permissions [1] => Permissions [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [18] => Array ( [usr_login] => fred [0] => fred [app_name] => Permissions [1] => Permissions [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [19] => Array ( [usr_login] => fred [0] => fred [app_name] => Permissions [1] => Permissions [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [20] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Permissions [1] => Permissions [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [21] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Permissions [1] => Permissions [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [22] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Permissions [1] => Permissions [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [23] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Permissions [1] => Permissions [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [24] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Benchmark [1] => Benchmark [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [25] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Benchmark [1] => Benchmark [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [26] => Array ( [usr_login] => fred [0] => fred [app_name] => Benchmark [1] => Benchmark [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [27] => Array ( [usr_login] => fred [0] => fred [app_name] => Benchmark [1] => Benchmark [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [28] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Benchmark [1] => Benchmark [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [29] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Benchmark [1] => Benchmark [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [30] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Benchmark [1] => Benchmark [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [31] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Benchmark [1] => Benchmark [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [32] => Array ( [usr_login] => Joe [0] => Joe [app_name] => News [1] => News [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [33] => Array ( [usr_login] => Joe [0] => Joe [app_name] => News [1] => News [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [34] => Array ( [usr_login] => fred [0] => fred [app_name] => News [1] => News [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [35] => Array ( [usr_login] => fred [0] => fred [app_name] => News [1] => News [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [36] => Array ( [usr_login] => carlie [0] => carlie [app_name] => News [1] => News [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [37] => Array ( [usr_login] => carlie [0] => carlie [app_name] => News [1] => News [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [38] => Array ( [usr_login] => lisa [0] => lisa [app_name] => News [1] => News [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [39] => Array ( [usr_login] => lisa [0] => lisa [app_name] => News [1] => News [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [40] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Passwords [1] => Passwords [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [41] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Passwords [1] => Passwords [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [42] => Array ( [usr_login] => fred [0] => fred [app_name] => Passwords [1] => Passwords [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [43] => Array ( [usr_login] => fred [0] => fred [app_name] => Passwords [1] => Passwords [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [44] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Passwords [1] => Passwords [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [45] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Passwords [1] => Passwords [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [46] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Passwords [1] => Passwords [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [47] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Passwords [1] => Passwords [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [48] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Fields [1] => Fields [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [49] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Fields [1] => Fields [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [50] => Array ( [usr_login] => fred [0] => fred [app_name] => Fields [1] => Fields [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [51] => Array ( [usr_login] => fred [0] => fred [app_name] => Fields [1] => Fields [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [52] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Fields [1] => Fields [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [53] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Fields [1] => Fields [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [54] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Fields [1] => Fields [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [55] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Fields [1] => Fields [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [56] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Settings [1] => Settings [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [57] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Settings [1] => Settings [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [58] => Array ( [usr_login] => fred [0] => fred [app_name] => Settings [1] => Settings [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [59] => Array ( [usr_login] => fred [0] => fred [app_name] => Settings [1] => Settings [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [60] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Settings [1] => Settings [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [61] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Settings [1] => Settings [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [62] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Settings [1] => Settings [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [63] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Settings [1] => Settings [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) )

I've got two tables user table - id - username - fname - lname - group_id event Table - id - user_id - event_time - event_date - event ('Arrival','Departure','Break',etc..) My problem is getting all user by group and joining other table even date, but it should be base on 'event_date' I use `LEFT JOIN` and `LEFT OUTER JOIN` but because I have to specify the `event_date` not all user can be shown because some are absent. I made this SELECt * FROM user as u LEFT OUTER JOIN event as e ON u.id = e.user_id WHERE u.group_id = 6 AND ( e.event_date = '2016-07-05' AND e.event = 'Arrival' ) GROUP BY u.id but it only show the user who has an event on the date specified. I tried to make an VIEW table but it still has the condition base on event_date. Although I made this on PHP by condition but it takes time to load as I select event on every user. is there a way to get it using SQL? it should be like this:

I want to fetch the titles from the Courses table, but I only want the Course Title to return once when I join Events. Courses CID | CourseTitle 1 | Course A 2 | Course B 3 | Course C Events EID | CID | EventDate 1 | 1 | 2016-02-22 2 | 1 | 2016-02-23 3 | 2 | 2016-02-24 4 | 3 | 2016-02-25 5 | 3 | 2016-02-26 If I use a JOIN, SELECT Courses.CourseTitle FROM Courses LEFT JOIN Events on (Events.CID = Courses.CID) then I get Course A Course A Course B Course C Course C But what I want is Course A Course B Course C Because ultimately, I'm going to select an Event date range, and I want to see just the courses with the event date range. Thanks!

I am creating a table that creates many rows based on what is in the equipment table. There is a second table that has information pertaining to an order that has already been made. equipment table is as follows: equipment_id equipment_name 1 Book 2 Dog 3 cat 4 hat equipment order table is as follows: job_id equipment_id equipment_quantity 10 1 100 10 3 100 There are 20 things in the equipment table. When the page loads, i would like it to show all of the items from the equipment table but i would like the rows where there is a quantity defined in the equipment_order table to be populated. So i should see Eauipment Quantity Book 100 Dog cat 100 hat The sql that i have used is this SELECT a.equipment_id, equipment_name, equipment_category, b.equipment_quantity FROM ssm_equipment a LEFT JOIN ssm_equipment_order b ON a.equipment_id = b.equipment_id Quite rightly this select anything with a quantity regardless of whether it relates to the current job or not. I have a variable $current_job_id that needs to be used to only get the values for the current job I tried the following SELECT a.equipment_id, equipment_name, equipment_category, b.equipment_quantity FROM ssm_equipment a LEFT JOIN ssm_equipment_order b ON a.equipment_id = b.equipment_id WHERE $current_job_id = 10 but this only get the rows where the ids match. I tried moving the WHERE clause but i cant seem to figure this out with out getting many errors. I have also tried the followin to no avail. SELECT a.equipment_id, equipment_name, equipment_category, b.equipment_quantity, b.job_id FROM ssm_equipment a LEFT JOIN ssm_equipment_order b ON a.equipment_id = b.equipment_id where b.job_id = '24' or b.job_id='NULL' The above just shows row with 24 I have talked about this in another thread but as this is very different to the origional question, i have moved it. Origional questions is below

Hello all. I dont know how to go about this. I have a table (Transactions) that contains transactions of users. Another table (Confirmed) contains details of every confirmed user. I want to do a select statement that will display all the confirmed user with only the last of their transaction. But so far all it does is replicate the user and their date of transaction and that is not what i want. My intention is to get something like: Firstname Surname Date Registered Last Transaction andrews john 12-12-2014 10-10-2015 doe andy 12-12-2010 12-12-2014 But i'm getting something like: Firstname Surname Date Registered Last Transaction andrews john 12-12-2014 10-10-2015 andrews john 12-12-2014 10-11-2015 doe andy 12-12-2010 12-12-2014 doe andy 12-12-2010 01-12-2014 doe andy 12-12-2010 12-12-2013 Thanks $stm=$pdo->query("select * from confirmed left join transaction on confirmed.user_id = transaction.user_id where confirmed.status='confirmed' order by date"); while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo $row['firstname']; echo $row['surname']; echo $row['regDate']; echo $row['lastTrans']; }

Hi I am trying to demonstrate a random number college project by code. I have a query which works fine getting data from table: SELECT id,Date, GROUP_CONCAT(ball ORDER BY ball) balls FROM ( SELECT id,Date,Ball1 ball FROM history eh UNION SELECT id,Date,Ball2 ball FROM history eh UNION SELECT id,Date,Ball3 ball FROM history eh UNION SELECT id,Date,Ball4 ball FROM history eh UNION SELECT id,Date,Ball5 ball FROM history eh ) x WHERE ball IN (6, 24, 32, 48, 50) GROUP BY id HAVING COUNT(*) >= 2 This will give 3 columns result: id,Date,balls which match any 2 or more of above numbers I want to join the result with another table starballs which has the same id as above and stores two colums of data Star1 Star2 i.e. id,Star1,Star2 I assume this will be inner join? to keep the data of top query and join it with starballs table. I have tried all sorts and cannot get it working. Can someone help me with this please. Thanks

Basically i want to match 2 columns from table1 to 1 column in table2. Here's my code. How do I do the joins properly? $get_earnings = $db->prepare("SELECT users.*, member_earnings.* FROM member_earnings LEFT JOIN users ON member_earnings.sent_by = users.user_id LEFT JOIN users ON member_earnings.guest_id = users.user_id WHERE record_id = :record_id AND status = :status ORDER BY earning_id DESC LIMIT 20"); $get_earnings->bindParam(':record_id', $url_id); $get_earnings->bindValue(':status', 1); $get_earnings->execute();

This seems like something I've done several times a year for years, but I can't seem to get it. I can't find a solution on Google that works, although the question was asked many times and answered, too. Also, I'm low on sleep right now. Let's say I have two tables: USERS USERS_LOGINS I want to join the tables and get one row per user, with only the latest login row. Assume "UserId" is the pk/fk and there's some sort of LoginTime in the USERS_LOGINS table. So I will get results like: - Brian 8/4 - Mitch 8/3 - Jerry 8/2 I appreciate the help.