mySql unknown column
I have the following query
SELECT
s.name,
s.surname,
s.id_nr,
s.student_nr,
s.createdate,
s.enddate,
(SELECT count(*) FROM Student_Attendance WHERE absent = 1) AS count_absent
FROM
Student AS s,
Student_Contact AS sc,
Student_Payment AS p,
Student_Courses AS scou,
Modules AS m,
Student_Certificate AS scer,
Student_Results AS sr,
Lecturer_Profile AS l,
Lecturer_Comments AS lc,
Student_Attendance AS sa,
Student_Training AS t
WHERE s.s_id = sc.s_id
AND s.s_id = p.s_id
AND s.s_id = scou.s_id
AND scou.c_id = m.c_id
AND s.s_id = scer.s_id
AND s.s_id = sr.s_id
AND s.s_id = lc.s_id
AND lc.l_id = l.l_id
AND s.s_id = sa.s_id
AND LOWER(s.name) = 'andile'
AND LOWER(s.surname) = ' orson vulture'
AND s.id_nr = 8403125062671
AND LOWER(sc.race) = 'w开发者_StackOverflow社区hite'
AND sc.gender = 1
AND LOWER(sc.area) = 'gauteng'
AND p.payment_type = 1
AND s.student_nr = 203087506
AND scou.c_id = 1 AND sc.age = 23
AND scer.certificate_number = 3424234
AND sr.result = 32
AND l.l_id= 1
AND count_absent = 3
AND LOWER(s.branch) = 'pretoria'
AND LOWER(s.campus_name) = 'pretoria'
AND LOWER(sc.kin_name) = 'self'
AND t.s_id = s.s_id
AND t.sp_id = 1
and I get the following error
CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'count_absent' in 'where clause'
Do not know how to solve this since the column is in the select statement
your derived column count_absent is processed in the SELECT clause, which is processed AFTER the WHERE clause, and so is therefore not available. It would be available if you wrapped the whole query in an outer SELECT, to be able to access the derived column.
i.e.
select * from (
SELECT
s.name,
s.surname,
s.id_nr,
s.student_nr,
s.createdate,
s.enddate,
(SELECT count(*) FROM Student_Attendance WHERE absent = 1) AS count_absent
FROM
Student AS s,
Student_Contact AS sc,
Student_Payment AS p,
Student_Courses AS scou,
Modules AS m,
Student_Certificate AS scer,
Student_Results AS sr,
Lecturer_Profile AS l,
Lecturer_Comments AS lc,
Student_Attendance AS sa,
Student_Training AS t
WHERE s.s_id = sc.s_id
AND s.s_id = p.s_id
...
AND t.s_id = s.s_id
AND t.sp_id = 1
) as x where count_absent = 3
I'm not sure what you're trying to accomplish here, but note that the embedded query that gives count_absent uses no criteria other than "absent=1". So it doesn't matter which student you're looking at, you will always get a count of all student_attendance records with absent=1. I suspect you want to limit this to student_attendance records for the selected student.
Also, you might investigate the "join" clause. This would make your queries much easier to make sense of then the old style joining with where.
精彩评论