Sorry. This is wrong On Nov 17, 2012 1:10 PM, "Gopakumar Pandarikkal" <pandarik...@gmail.com> wrote:
> 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
