What I would do: * The COURSES table: - Probably it's good to choose a different name for COURSES. (Course registration details or maybe better name can be invented). - Add a unique/primary key field for each entry in the table. - If possible - would be easier to work if you have EXPIRATION_DATE there.
* In STUDENT table: - Add a column referencing the unique/primary key which I just suggested for COURSES. (Create a foreign key from this column to the table with course details) Having these it would be trivial to join both tables. On Wednesday, November 14, 2012 8:50:47 PM UTC+2, 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
