Just add a.course_id=b.course_id to the subquery where clause. On Nov 17, 2012 2:53 PM, "Gopakumar Pandarikkal" <pandarik...@gmail.com> wrote:
> Another approach > > Sorry I am not able to test these > > Select * from ( > select a.student_id, a.course_id, b.course_name, a.course_date, > b.course_fee,effective_date,max(effective_date) over (partition by > b.course_id) mx > from student a inner join couse b on a.course_id=b.course_id where > effective_date<=(select max(effective_date) from course where > effective_date <=a.course_date) > where effective_date =mx > On Nov 17, 2012 1:48 PM, "Gopakumar Pandarikkal" <pandarik...@gmail.com> > wrote: > >> Select * from >> (select a.student_id, a.course_id, b.course_name, a.course_date, >> b.course_fee,effective_date,max(effective_date) over (partition by >> b.course_id) mx >> from student a inner join couse b on a.course_id=b.course_id and >> effective_date<=a.course_date) >> >> where effective_date =mx >> On Nov 17, 2012 1:18 PM, "Gopakumar Pandarikkal" <pandarik...@gmail.com> >> wrote: >> >>> 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
