CREATE TABLE coursex AS
(SELECT 'A103' course_id
, 'Accounting1' course_name
, 200 course_fee
, TO_DATE ('04/01/2011', 'mm/dd/yyyy') effective_date
FROM DUAL
UNION ALL
SELECT 'A103' course_id
, 'Accounting1' course_name
, 210 course_fee
, TO_DATE ('06/01/2011', 'mm/dd/yyyy') effective_date
FROM DUAL
UNION ALL
SELECT 'A103' course_id
, 'Accounting1' course_name
, 220 course_fee
, TO_DATE ('10/01/2011', 'mm/dd/yyyy') effective_date
FROM DUAL
UNION ALL
SELECT 'A102' course_id
, 'Writint101' course_name
, 150 course_fee
, TO_DATE ('04/01/2011', 'mm/dd/yyyy') effective_date
FROM DUAL);
CREATE TABLE studentx AS
(SELECT 123 student_id
, 'Kathy Smith' student_name
, 'A103' course_id
, TO_DATE ('12/01/2011', 'mm/dd/yyyy') course_date
FROM DUAL
UNION ALL
SELECT 123 student_id
, 'Kathy Smith' student_name
, 'A102' course_id
, TO_DATE ('12/01/2011', 'mm/dd/yyyy') course_date
FROM DUAL
UNION ALL
SELECT 124 student_id
, 'Chris Jones' student_name
, 'A103' course_id
, TO_DATE ('06/01/2011', 'mm/dd/yyyy') course_date
FROM DUAL
UNION ALL
SELECT 124 student_id
, 'Chris Jones' student_name
, 'A102' course_id
, TO_DATE ('06/01/2011', 'mm/dd/yyyy') course_date
FROM DUAL);
SELECT a.student_id
, a.student_name
, a.course_id
, b.course_name
, a.course_date
, b.course_fee
FROM studentx a LEFT OUTER JOIN Coursex b ON a.course_id = b.course_id
WHERE b.effective_Date <= a.course_date
AND NOT EXISTS
(SELECT 1
FROM studentx q LEFT OUTER JOIN Coursex r ON
q.course_id = r.course_id
WHERE r.effective_Date <= q.course_date
AND q.student_id = a.student_id
AND q.course_id = a.course_id
AND r.effective_date > b.effective_date)
ORDER BY 1, 4, 5, 6;
STUDENT_ID STUDENT_NAME COURSE_ID COURSE_NAME COURSE_DATE COURSE_FEE
---------- ------------ --------- ----------- ----------- ----------
123 Kathy Smith A103 Accounting1 01-DEC-11 220
123 Kathy Smith A102 Writint101 01-DEC-11 150
124 Chris Jones A103 Accounting1 01-JUN-11 210
124 Chris Jones A102 Writint101 01-JUN-11 150
4 rows selected.
Sanhtosh,
you will ge:t ORA-01799: a column may not be outer-joined to a subquery
Mike
On Thu, Nov 15, 2012 at 10:46 PM, Santhosh <[email protected]>wrote:
> SELECT a.student_id,
> a.course_id,
> b.course_name,
> a.course_date,
> b.course_fee
> FROM student a
> LEFT OUTER JOIN
> Course b
> ON a.course_id = b.course_id
> AND b.effective_Date = (SELECT effective_Date
> FROM course
> WHERE effective_date <= a.course_date);
>
> Try something this.If not getting let me know.
>
> On Thursday, 15 November 2012 00:20:47 UTC+5:30, 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 [email protected]
> To unsubscribe from this group, send email to
> [email protected]
> 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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
