Home » SQL & PL/SQL » SQL & PL/SQL » LEFT Outer Join problem (merged 4)
LEFT Outer Join problem (merged 4) [message #385097] |
Fri, 06 February 2009 07:30 |
akhgoyal
Messages: 2 Registered: February 2009
|
Junior Member |
|
|
According to the definition the left outer join should always return more or equal number of rows than inner join .
But in my query i am getting less rows in left outer join than in inner join.
I am attaching the query .
SELECT DISTINCT ALL_USERS.USER_ID, rep_atmt.Attempt_no ,ALL_USERS.FIRST_NAME, ALL_USERS.LAST_NAME, rep_atmt.TITLE ,rep_atmt.WEIGHT , rep_atmt.SUBMITPASTDUE, rep_atmt.ASSIGNMENT_ID, rep_atmt.CATEGORY_TYPE, rep_atmt.WID,rep_atmt.SUBMISSION_ID, ALL_USERS.active_yn as status , rep_atmt.SECTION_ID, rep_atmt.ACTIVITY_ID, rep_atmt.SCORE,rep_atmt.PERCENT, rep_atmt.COMPLETION_DATE, rep_atmt.STARTINGDATE, rep_atmt.Assignment_due_date, rep_atmt.manual_scoring_date, rep_atmt.sequence_no
FROM(
SELECT user_id, active_yn, first_name, last_name FROM
( SELECT a.*, ROWNUM rnum FROM
( SELECT r.user_id, r.active_yn, eusers.first_name, eusers.last_name FROM ( ( SELECT DISTINCT user_id, active_yn FROM roster WHERE SECTION_ID = 353790 AND ACTIVE_YN ='Y' AND IS_DELETED <> '1' ) r LEFT JOIN( SELECT DISTINCT user_id FROM REPORTS_ALL_ATTEMPTS WHERE SECTION_ID = 353790 AND ASSIGNMENT_ID IN (421961,421966,421975,422152) AND SUBMITPASTDUE IS NULL AND STATUS='Y' AND IS_DELETED <> '1' AND COMPLETION_DATE BETWEEN TO_DATE( '01-Jan-2007 00:00:00', 'DD-MON-YYYY HH24:MIS' ) AND TO_DATE( '05-Feb-2009 23:59:59', 'DD-MON-YYYYHH24:MIS' ) ) rep ON r.user_id=rep.user_id JOIN erightsusers eusers ON r.user_id = eusers.id ) ORDER BY upper(eusers.last_name), upper(eusers.first_name) )a WHERE ROWNUM < =50) where rnum >=1 ) ALL_USERS
LEFT OUTER JOIN
( SELECT * FROM reports_all_attempts WHERE (USER_ID, SECTION_ID, ASSIGNMENT_ID, ATTEMPT_NO) in (
SELECT user_id, section_id, assignment_id ,max(attempt_no) as attempt_no FROM reports_all_attempts WHERE SECTION_ID = 353790 AND ASSIGNMENT_ID IN (421961,421966,421975,422152) AND SUBMITPASTDUE IS NULL AND STATUS='Y' AND IS_DELETED <> '1' AND COMPLETION_DATE BETWEEN TO_DATE( '01-Jan-2007 00:00:00','DD-MON-YYYY HH24:MIS' ) AND TO_DATE( '05-Feb-2009 23:59:59', 'DD-MON-YYYY HH24:MIS' ) GROUP BY user_id, section_id, assignment_id )
) as rep_atmt ON ALL_USERS.USER_ID = rep_atmt.user_id ORDER BY upper(ALL_USERS.LAST_NAME),upper(ALL_USERS.FIRST_N AME), ALL_USERS.USER_ID, rep_atmt.SECTION_ID, rep_atmt.sequence_no, rep_atmt.assignment_due_date, rep_atmt.title, rep_atmt.assignment_id, rep_atmt.ATTEMPT_NO
Here in the bold part (LEFT OUTER JOIN ) , if i put Inner Join it gives 7 rows , but 3 rows but Left Outer join ..
Also if i use (+) syntax it gives the desired output .
Can anyone help me in this.
Thanks and Regards
Akhil Goyal
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Nov 09 13:15:58 CST 2024
|