I have a STUDENT table that has
Student_id
Student_name
Course_id
Course_date

So my records look like

123 Kathy Smith  A103    12/01/2011
123 Kathy Smith  A102    12/01/2011
124 Chris Jones   A103    06/01/2011
124 Chris Jones   A102    06/01/2011

The I have another table COURSE
Course_id
Course_name
Course_fee
Effective_date

A103 Accounting1  200.00 04/01/2011
A103 Accounting1  210.00 06/01/2011
A103 Accounting1  220.00 10/01/2011
A102 Writing101    150.00  04/01/2011


what I need is

Student_id, Student_name, Course_id, Course_name, course_date and
Course_fee

(based on the date a particular student signed up for the course,
course fee should be determined)

so for

123 Kathy smith   A103  Accounting1  12/01/2011    220.00
124 Chris Jones   A103  Accounting1   06/01/2011   210.00
123 Kathy Smith  A102  Writing102     12/01/2011   150.00
124 Chris Jones    A102 Writing 102    04/01/2011   150.00



This is how I tried and it doesn't work

select a.student_id, a.course_id, b.course_name, a.course_date,
b.course_fee
from student a left outer join on Course b where a.course_id =
b.course_id
and b.effective_Date = (select effective_Date from course where
effective_date <= a.course_date)

This is not for my school homework..working as a junior developer (so
I created a scenario similar to my issue at work and am asking for
help).
Thank you

-- 
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en

Reply via email to