SQL query to select attendance per student using sub query cant use row from parent query

Question

I have a problem in SQL, I'm trying to work out the student attendance for a class in a register system I'm building however it won't let me use the parent column in the sub query,

SELECT A.student_id, TRUNCATE((100 - ((100/B.reg_num) * C.abs_num)), 0) AS attendance FROM
students A
JOIN (
SELECT COUNT(*) AS reg_num
FROM students
JOIN seminargroup_student ON seminargroup_student.student_id = students.student_id
JOIN seminar_group ON seminar_group.seminar_group_id = seminargroup_student.seminar_group_id
JOIN modules ON modules.module_id = seminar_group.seminar_group_module_id
JOIN register_seminar ON register_seminar.seminar_id = seminar_group.seminar_group_id
JOIN registers ON registers.register_id = register_seminar.register_id
WHERE modules.module_id =1 AND students.student_id = A.student_id
) B
JOIN (
SELECT COUNT(*) AS abs_num
FROM students
JOIN seminargroup_student ON seminargroup_student.student_id = students.student_id
JOIN seminar_group ON seminar_group.seminar_group_id = seminargroup_student.seminar_group_id
JOIN modules ON modules.module_id = seminar_group.seminar_group_module_id
JOIN absence ON students.student_id = absence.student_id
WHERE modules.module_id =1 AND students.student_id = A.student_id
) C

This returns: #1054 - Unknown column 'A.student_id' in 'where clause'

Thanks for any help!

your sample doen`t contain 'A.student_id' and you use "students as A" and "students". Please post code that causes error — Nogard, Oct 23 '12 at 10:08
I can't find A.student_id in any WHERE clause. It's only in the SELECT clause. — Marlin Pierce, Oct 23 '12 at 10:08
There are also no `ON` clauses associated with any of the three joins in the outer query... — MatBailie, Oct 23 '12 at 10:10

Conrad Frix · Answer 1 · 2012-10-23T18:42:19.370

it won't let me use the parent column in the sub query

Generally speaking you don't need to reference the parent in a subquery that's in a FROM.

Instead you just add your joining fields to your SELECT and GROUP BY clauses in the subqueries and then join

e.g.

SELECT students.student_id, 
       Truncate(( 100 - ( ( 100 / b.reg_num ) * c.abs_num ) ), 0) AS attendance 
FROM   students 
       JOIN (SELECT a.studentid, 
                    Count(*) AS reg_num 
             FROM   students A 
                    JOIN seminargroup_student 
                      ON seminargroup_student.student_id = A.student_id 
                    JOIN seminar_group 
                      ON seminar_group.seminar_group_id = 
                         seminargroup_student.seminar_group_id 
                    JOIN modules 
                      ON modules.module_id = 
                         seminar_group.seminar_group_module_id 
                    JOIN register_seminar 
                      ON register_seminar.seminar_id = 
                         seminar_group.seminar_group_id 
                    JOIN registers 
                      ON registers.register_id = register_seminar.register_id 
             GROUP  BY a.studentid) A 
         ON students.studentid = a.student.id 
       JOIN (SELECT a.studentid, 
                    Count(*) AS abs_num 
             FROM   students aA 
                    JOIN seminargroup_student 
                      ON seminargroup_student.student_id = a.student_id 
                    JOIN seminar_group 
                      ON seminar_group.seminar_group_id = 
                         seminargroup_student.seminar_group_id 
                    JOIN modules 
                      ON modules.module_id = 
                         seminar_group.seminar_group_module_id 
                    JOIN absence 
                      ON a.student_id = absence.student_id 
             GROUP  BY a.studentid) b 
         ON students.studentid = b.student.id

As a side note you don't have to do two subqueries if you use a left joins and do a DISTINCT COUNT on the PK fields instead of on *

SELECT 
    A.student_id, 
    TRUNCATE((100 - ((100/counts.reg_num) * counts.abs_num)), 0) AS attendance
FROM
students A   
JOIN 
(SELECT 
    COUNT(DISTINCT absence.absence_id) AS abs_num , --OR whatever the PK is
    COUNT(DISTINCT registers.regeister_id) as reg_num,
    students.student_id


FROM   students 
       JOIN seminargroup_student 
         ON seminargroup_student.student_id = students.student_id 
       JOIN seminar_group 
         ON seminar_group.seminar_group_id = 
            seminargroup_student.seminar_group_id 
       JOIN modules 
         ON modules.module_id = seminar_group.seminar_group_module_id 

       LEFT JOIN register_seminar 
         ON register_seminar.seminar_id = seminar_group.seminar_group_id 
       LEFT JOIN registers 
         ON registers.register_id = register_seminar.register_id   


       LEFT JOIN absence 
         ON students.student_id = absence.student_id 
GROUP BY
    students.student_id) COUNTS
ON a.student_id = coutnts.student_ID

Your second suggestion is unreliable because of possible mini-Cartesian products, which would skew the results. `COUNT(DISTINCT)`s might fix that, but I'm not sure. — Andriy M, Oct 23 '12 at 18:34
@AndriyM Yes it does fix it. You can compare the first and second results of this [SQL Fiddle](http://sqlfiddle.com/#!2/03b34/7) Thanks — Conrad Frix, Oct 23 '12 at 18:47

score 0 · Answer 2 · edited May 23 '17 at 11:48

Try to rewrite you query as:

SELECT students.student_id, TRUNCATE((100 - ((100/B.reg_num) * C.abs_num)), 0) AS attendance FROM 
students 
JOIN ( 
SELECT COUNT(*) AS reg_num 
FROM students A
JOIN seminargroup_student ON seminargroup_student.student_id = A.student_id 
JOIN seminar_group ON seminar_group.seminar_group_id = seminargroup_student.seminar_group_id 
JOIN modules ON modules.module_id = seminar_group.seminar_group_module_id 
JOIN register_seminar ON register_seminar.seminar_id = seminar_group.seminar_group_id 
JOIN registers ON registers.register_id = register_seminar.register_id 
HAVING modules.module_id =1 AND students.student_id = A.student_id 
) B 
JOIN ( 
SELECT COUNT(*) AS abs_num 
FROM students A
JOIN seminargroup_student ON seminargroup_student.student_id = A.student_id 
JOIN seminar_group ON seminar_group.seminar_group_id = seminargroup_student.seminar_group_id 
JOIN modules ON modules.module_id = seminar_group.seminar_group_module_id 
JOIN absence ON A.student_id = absence.student_id 
HAVING modules.module_id =1 AND students.student_id = A.student_id 
) C

UPD: WHERE clause is replaced with HAVING and alias is moved inside sub-queries. Error may be caused due to WHERE is calculated before SELECT.

Also these questions may help you solve the issue:
1. Unknown Column In Where Clause
2. WHERE vs HAVING

Thanks for the reply, That worked but only for the first student, every row pulled out the same thing — Dan Reil, Oct 23 '12 at 17:21
edited my answer. Please take a look if this solves your problem — Nogard, Oct 23 '12 at 17:55

SQL query to select attendance per student using sub query cant use row from parent query

2 Answers2