solving sub-query error ORA-01427 with join

Question

I have a small sub-query in a lengthy select statement and I'm out of ideas (I get the same ORA-01427 error whether I'm working with the entire select statement or if I segregate out the sub-query. I have following:

NAME table
student_id_number field

TERM table
term_id field
student_id_number field

TEST view
test_id field
test_element field
student_id_number field
test_date field
score field

For a unique term_id value I want to select a score when the test_id field = A and the test_element field = COMP. I realize that it's possible for there to be more than one valid combination of these elements per each unique term ID and student_id_number field, but the test_date field will still be unique for multiple rows and I suspect using a max(value) for score or test_date might get around the problem. But I'm not certain how to do this.

Trying to insert ROWNUM = 1 in the where statement returns the first score from the table for all selected records. Using max(score) in the select line returns the maximum score value for all selected records.

select
 a.term_id,
 a.student_id_number,
 (select ts.score
          from test_score ts
          left join term a on ts.student_id_number = a.student_id_number
          where ts.test_id = 'A' and ts.test_element = 'COMP') 
 from term a
 where a.term_id = '201701'

I want to see columns for term_id, student_id_number and score but I get the dreaded ORA-01427 error, perhaps (probably) because there may be multiple rows with matching student_id_number, test_id and test_element, however only the most recent score is relevant (or the highest score would also be relevant as opposed to the most recent score). This is beyond the complexity of anything I've previously done and I'm not certain how (if) I can get around this problem. Any help or advice for this inexperienced coder is appreciated. Thanks.

Scott

Answer 1

You don't need a join in the subquery. You need a correlation clause -- something that connects the result in the subquery to the row in the outer query:

select t.term_id, t.student_id_number,
      (select ts.score
       from test_score ts
       where ts.student_id_number = t.student_id_number and
             ts.test_id = 'A' and
             ts.test_element = 'COMP' and
             rownum = 1
      ) 
from term t
where t.term_id = '201701';

I added WHERE rownum = 1 to limit the result set to one row. You could also use aggregation -- MIN(), MAX(), AVG(), and LISTAGG() might all be appropriate.

Also, I changed the table aliases so they are abbreviations for the tables. Arbitrary letters make the query harder to read.