PostgreSQL – Subquery Uses Ungrouped Column from Outer Query

group byjoin;postgresql

I have the following tables:

  • 1 teacher can have 1 or more students
  • 1 student can have 1 or more homeworks

I would like to know in a list of teachers, for each teacher if it has any student with any homework assigned.

Query I tried:

select t.teacher_id as id,
       t.name as name,
       (select count(*) 
          from homework 
            where s.student_id = h.student_id ) as teacherHasHomeworkToVerify
from teacher t
       left join student s on t.teacher_id = s.teacher_id
       left join homework h on s.student_id = h.student_id
       group by t.teacher_id
       order by t.name COLLATE "en_US"

Error:

ERROR: subquery uses ungrouped column "s.student_id" from outer query

SQL Fiddle link: http://sqlfiddle.com/#!15/a00ac/4

Schema:

create table teacher (
    teacher_id serial primary key,
    name text
);

insert into teacher (teacher_id, name)
values (1, 'bob'),
       (2, 'mary');

create table student (
    student_id serial primary key,
    teacher_id bigint,
    name text
);

insert into student (student_id, teacher_id, name)
values (1, 1, 'smith'),
       (2, 2, 'jimmy'),
       (3, 2, 'james');

create table homework (
    homework_id serial primary key,
    student_id bigint,
    subject text
);

insert into homework (homework_id, student_id, subject)
values (1, 3, 'math');

Best Answer

I found the issue, don't need the subquery, just a simple case statement and count.

Fixed query:

select t.teacher_id as id, 
       t.name as name,
       case when 
           count(h.student_id) > 0 
         then 't' 
         else 'f' 
       end as teacherHasHomeworkToVerify 
from teacher t 
       left join student s on t.teacher_id = s.teacher_id 
       left join homework h on s.student_id = h.student_id 
       group by t.teacher_id
       order by t.name collate "en_US"

Fixed SQL Fiddle version link: http://sqlfiddle.com/#!15/a00ac/5

Related Question