Re: SQL Query Question. Pls help

From: Steve Davis <spd_at_patrol.i-way.co.uk>
Date: Thu, 11 Jun 1998 20:38:50 GMT
Message-ID: <35804197.0@news.i-way.co.uk>

kkkong_at_ee.cuhk.hk (Alan Kong) wrote:

>Hi,

>I need help on the following problem.

<snip>

Alan:

SQL> select * from student_result;

STUDENT_NO YEAR SUBJECT RESULT

• ---- ------- ------

s1 1994 subj1 A

s1 1995 subj2 B

s2 1994 subj1 A

s2 1995 subj3 C

s3 1995 subj2 B

s3 1995 subj3 A

6 rows selected.

SQL> select * from sub_student;

STUDENT_NO

---

s1

s3

SQL> select * from sub_student_result;

no rows selected

SQL> insert into sub_student_result
  2 select ss.student_no,

  3         sr2.result,
  4         sr3.result
  5  from student_result sr2,
  6       student_result sr3,
  7       sub_student    ss
  8  where sr2.student_no (+) = ss.student_no
  9  and   sr2.subject    (+) = 'subj2'

 10 and sr3.student_no (+) = ss.student_no  11 and sr3.subject (+) = 'subj3';

2 rows created.

SQL> select * from sub_student_result;

STUDENT_NO SUBJ2 SUBJ3

• ----- -----

s1 B

s3 B A

Hope this helps

Note that this assumes that each student has only one result per subject. If a student has taken the same subject in more than one year, this will return multiple rows.

Steve Received on Thu Jun 11 1998 - 15:38:50 CDT