-1
select first_name, middle_name, last_name 
from students 
where student_id = any (select student_id 
                        from entries 
                        where subject_id = (select subject_id 
                                           from subjects 
                                            where exam_board = "OCR"));

Subquery returns more than one row. I feel sure I'm missing something obvious, but if anyone has an answer I'd be glad to hear it!

  • Change = to IN? select first_name, middle_name, last_name from students where student_id IN (select student_id from entries where subject_id IN (select subject_id from subjects where exam_board = "OCR")); – Gareth Lyons Feb 28 '17 at 14:43
  • 1
    Notice you have more than one subquery. – mustaccio Feb 28 '17 at 15:02
2

If you are expecting multiple values to be returned from a sub query, you need to use an IN statement:

select first_name, middle_name, last_name 
from students 
where student_id IN (select student_id 
                        from entries 
                        where subject_id IN (select subject_id 
                                           from subjects 
                                            where exam_board = "OCR"));

However, looking at the data provided, you can actually join these tables together

select first_name, middle_name, last_name
from   students inner join
       entries on students.student_id = entries.student_id inner join
       subjects on entries.subject_id = subjects.subject_id
where  exam_board = "OCR";
0

You subquery is returning student_id's from the entries table that have the subject_id equal to the subject_id associated with the exam_board, "OCR" from the subjects table.

Your subquery can and will return zero, one or more student_id values. ANY will return true if student.student_id matches any of the student_id values (returned by your subquery) and the first_name, middle_name, last_name associated with that student_id will be returned.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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