Databases
StackOverflow2013
Postsdbo
POHow to achive this in a single SQL statement
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to achive this in a single SQL statement
    text
    copied!<p>please i have problem at hand,</p> <p>I have the following tables in my database MySQL </p> <pre><code>students studentID | fname | mname | lname | 1 | Wilson | Dale | Micheal| examtype examtypeID | name | 1 | ca 1 2 | ca 2 3 | ca 3 4 | exam first_term_result student_id | examtype_id | subject_id | mark 1 | 1 | 7 | 12 1 | 2 | 7 | 10 1 | 3 | 7 | 9 1 | 4 | 7 | 45 </code></pre> <p>currently i have this SQL statement </p> <pre><code>SELECT CONCAT(students.fname,' ',students.mname,' ',students.lname) AS student_name, students.studentID, exam_type.name, first_term_result.mark FROM students, exam_type, first_term_result WHERE students.studentID=first_term_result.student_id AND exam_type.exam_typeID=first_term_result.exam_type_id AND first_term_result.subject_id=7 </code></pre> <p>it works as i get a table where i can use to get an array like this</p> <p><strong>How i got it to that array</strong> using codeigniter framework :</p> <pre><code> $sheet = $this-&gt;teacher_model-&gt;spreadsheet(); $data = array(); foreach($sheet as $row) { $data[$row['student_name']]['studentID']=$row['studentID']; $data[$row['student_name']][$row['name']]=$row['mark']; } Array ( [Wilson Dale Micheal] =&gt; Array ( [studentID] =&gt; 1 [CA 1] =&gt; 12 [CA 2] =&gt; 10 [CA 3] =&gt; 9 [Exam] =&gt; 45 ) ) </code></pre> <p>another SQL statement as follows:</p> <pre><code>SELECT CONCAT(students.fname,' ',students.mname,' ',students.lname) AS students_name, students.studentID, SUM(first_term_result.mark) as Total FROM students, first_term_result WHERE students.studentID=first_term_result.student_id AND first_term_result.subject_id=7 group by students_name </code></pre> <p>gives something like this:</p> <pre><code>Array ( [Wilson Dale Micheal] =&gt; Array ( [studentID] =&gt; 1 [Total] =&gt; 76 ) ) </code></pre> <p>Merging the two array i get my result, which is:</p> <p>this is how i merged merged the two, the function array_merge() didnt work so i used:</p> <pre><code> $data = array(); foreach($sheet as $row) { $data[$row['student_name']]['studentID']=$row['studentID']; $data[$row['student_name']][$row['name']]=$row['mark']; } foreach($total as $tol) { $data[$tol['student_name']]['Total']=$tol["Total"]; } Array ( [Wilson Dale Micheal] =&gt; Array ( [studentID] =&gt; 1 [CA 1] =&gt; 12 [CA 2] =&gt; 10 [CA 3] =&gt; 9 [Exam] =&gt; 45 [Total] =&gt; 76 ) ) </code></pre> <p><strong>But i am wondering if i can achieve all these with a single SQL statement so that i will be able to make ORDER BY Total DESC, so the student with the highest score will come first and so on. The current statement doesnt sort the Total as DESC though i have the array just like i want to.</strong></p> <p>Thanks </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload