Sheldon
05-28-2009, 07:33 PM
Hi,
I am trying the below query, I want to get a field of a value, and a total of the same field where the value is < 3,
I need to pass in the 'student_id' in to my subselect or maybe some one can see a better way to run this ?
SELECT student_tt.id AS student_id, students.firstname AS firstname, students.lastname AS lastname, student_tt.form AS form_class, student_tt.level AS level, COUNT(student_key_competencies.id) AS records, (SELECT COUNT(student_key_competencies.id) FROM student_key_competencies WHERE student_tt_id IN (SELECT student_tt.id FROM student_tt LEFT JOIN students ON students.id = student_id WHERE students.school_id = '$school_id' AND student_id = PARENT_STUDENT_ID)) FROM student_key_competencies LEFT JOIN student_tt ON student_tt.id = student_key_competencies.student_tt_id LEFT JOIN students ON student_tt.student_id = students.id WHERE student_key_competencies.data < 3 AND students.school_id = '$school_id' GROUP BY student_key_competencies.student_tt_id
I am trying the below query, I want to get a field of a value, and a total of the same field where the value is < 3,
I need to pass in the 'student_id' in to my subselect or maybe some one can see a better way to run this ?
SELECT student_tt.id AS student_id, students.firstname AS firstname, students.lastname AS lastname, student_tt.form AS form_class, student_tt.level AS level, COUNT(student_key_competencies.id) AS records, (SELECT COUNT(student_key_competencies.id) FROM student_key_competencies WHERE student_tt_id IN (SELECT student_tt.id FROM student_tt LEFT JOIN students ON students.id = student_id WHERE students.school_id = '$school_id' AND student_id = PARENT_STUDENT_ID)) FROM student_key_competencies LEFT JOIN student_tt ON student_tt.id = student_key_competencies.student_tt_id LEFT JOIN students ON student_tt.student_id = students.id WHERE student_key_competencies.data < 3 AND students.school_id = '$school_id' GROUP BY student_key_competencies.student_tt_id