SELECT a.student_id, a.course_id, b.course_name, a.course_date, b.course_fee FROM student a LEFT OUTER JOIN Course b ON a.course_id = b.course_id AND b.effective_Date = (SELECT effective_Date FROM course WHERE effective_date <= a.course_date);
Try something this.If not getting let me know. On Thursday, 15 November 2012 00:20:47 UTC+5:30, SQL-Help wrote: > > 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