Updated version select a.student_id, a.course_id, b.course_name, a.course_date, b.course_fee from student a inner join couse on a. Course_id where a.course_Date <=(select max(effective_Date) from course where effective_date <= a.course_date) On Nov 17, 2012 1:01 PM, "Gopakumar Pandarikkal" <pandarik...@gmail.com> wrote:
> select a.student_id, a.course_id, b.course_name, a.course_date, > b.course_fee from student a inner join on Course b where a.course_id = > b.course_id where a.course_Date = (select max(effective_Date) from course > where effective_date <= a.course_date) > > Try this tell me if its ok > On Nov 16, 2012 6:22 AM, "SQL-Help" <sketin...@gmail.com> 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 >> > -- 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
