• 0 Vote(s) - 0 Average
• 1
• 2
• 3
• 4
• 5
 Workload calculation Problem El Forum Guest   09-30-2014, 12:02 AM [eluser]Bigil Michael[/eluser] In a school management system, I want to calculate the workload of each teacher in that school. I have created 3 tables, given below 1. staff table stf_id stf_firstname 1 A 2 B 3 C 2. taff_subjects table id stf_id subjects 1 1 1 2 1 2 3 1 3 4 2 1 5 2 3 3. timetable table in this table i am saving only subjects id period monday tuesday wednesday thursday friday saturday 1 2 1 ..... 2 1 2 ...... 3 3 .... 4 2 5 1 I want the result like this staff name Monday Tuesday wednesday thursday friday saturday total A 3 periods 2prds 5prds ..................... 25 prds B 1 prds 6 prds ........................... 22 prds written the query for monday only Code:```\$this->db->select('S.stf_firstname as teacher, count(T.monay) as monday_count');     \$this->db->from('staffs as S');     \$this->db->join('staff_subjects as SS','SS.stf_id = S.stf_id','LEFT');   \$this->db->join('timetable as T','T.monday = SS.subjects OR T.tuesday = SS.subjects OR T.wednesday = SS.subjects OR T.thursday = SS.subjects OR T.friday = SS.subjects OR T.saturday = SS.subjects ','LEFT');   \$this->db->group_by('S.stf_id');     \$result_courses = \$this->db->get();     return \$result_courses->result_array();``` but it did not print the correct count. can any one solve this query or suggest an alternative method to solve this issue.. El Forum Guest   10-01-2014, 12:42 AM [eluser]Bigil Michael[/eluser] I have modified the table like this and instead of subjects teachers id has been saved. id Monday Tuesday wednesday thursday friday saturday 1 2 3 1 5 6 2 1 4 3 2 5 Now I want to take count of each days written query like this Code:```SELECT `S`.`stf_firstname` as teacher, count(T.monday) as monday_prd  FROM (`staffs` as S) LEFT JOIN `timetable` as T ON `T`.`monday` = `S`.`stf_id` GROUP BY `S`.`stf_id````This code running fine and prints count of monday Code:```SELECT `S`.`stf_firstname` as teacher, count(T.monday) as monday_prd,count(T1.tuesday) as tue_period  FROM (`staffs` as S) LEFT JOIN `timetable` as T ON `T`.`monday` = `S`.`stf_id` LEFT JOIN `timetable` as T1 ON `T1`.`tuesday` = `S`.`stf_id` GROUP BY `S`.`stf_id````It prints wrong count can any one help to print multiple counts from same table with single left join

Theme © 2014 iAndrew