title: SQL query to select attendance per student using sub query cant use row from parent query
body: <p>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,</p>
  
  <pre><code>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
  </code></pre>
  
  <p>This returns: #1054 - Unknown column 'A.student_id' in 'where clause'</p>
  
  <p>Thanks for any help!</p>