1

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');

1 Answer 1

0

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

2
  • in PostgreSQL, we don't use 't', or 'f', ever. You should change that to use bool types. Jan 8, 2017 at 5:16
  • glad to see you got an answer though, make this as selected if you're happy w/ it Jan 8, 2017 at 5:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.