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
