Fix My Mysql Query
I need someone to provide me with the proper MySQL syntax for the query described below.
When running my current query code (listed below), I’m receiving the following error in MySQL:
[Err] 1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘) and entry_horse.entryID = T2.entryID(+) and entry_horse.entryID = T3.entryID(‘ at line 13
### My query code:
SELECT student.studentID, student_name, count_of_tests_started_by_student, count_of_tests_failed_by_student,
count_of_tests_passed_by_student, count_of_tests_currently_incomplete_by_student
FROM
( SELECT COUNT(*) AS count_of_tests_started_by_student, studentID FROM test GROUP BY studentID ) AS T1,
( SELECT COUNT(*) AS count_of_tests_failed_by_student , studentID FROM test WHERE test_result = ‘failed’ GROUP BY studentID ) AS T2,
( SELECT COUNT(*) AS count_of_tests_passed_by_student , studentID FROM test WHERE test_result = ‘passed’ GROUP BY studentID ) AS T3,
( SELECT COUNT(*) AS count_of_tests_incomplete_by_student , studentID FROM test WHERE test_result = ‘incomplete’
GROUP BY studentID ) AS T4 ,
student
where
student.studentID = T1.studentID(+) and student.studentID = T2.studentID(+) and student.studentID = T3.studentID(+)
and student.studentID = T4.studentID(+)
Here’s the details for what I need:
i have two myisam mysql tables (student and test). i need the query to return a row for every student in the student table and each returned row should contain the following columns:
* studentID
* student_name
* count_of_tests_started_by_student
* count_of_tests_failed_by_student
* count_of_tests_passed_by_student
* count_of_tests_currently_incomplete_by_student
The STUDENT table contains:
* studentID primary key (1234)
* student_name (john doe)
The TEST table contains:
* testID primary key (1234)
* studentID (1234)
* test_result (“passed”, “failed”, “incomplete”)
Each student may have any number of TEST table records (zero or more).
In my quest to debug the error in my query code, I’ve tried to get the following simpler query code to work. But, I can not even get the following query to work without getting the MySQL syntax error message:
SELECT student.studentID, student_name
FROM
test ,
student
where
student.studentID = test.studentID(+)