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