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 Go to next message
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
Re: LEFT Outer Join problem (merged 4) [message #385105 is a reply to message #385097] Fri, 06 February 2009 07:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Don't multipost your question.
If you have any problem during creation, FIRST go back to forum topic list to check if yours is there and if not THEN repost.

Regards
Michel
Re: LEFT Outer Join problem (merged 4) [message #385106 is a reply to message #385097] Fri, 06 February 2009 07:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What version of Oracle are you using? There were quite a few bugs in the ANSI outer join syntax when it first came out.
Re: LEFT Outer Join problem (merged 4) [message #385113 is a reply to message #385106] Fri, 06 February 2009 08:01 Go to previous messageGo to next message
akhgoyal
Messages: 2
Registered: February 2009
Junior Member
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
Re: LEFT Outer Join problem (merged 4) [message #385116 is a reply to message #385113] Fri, 06 February 2009 08:08 Go to previous message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
10.2.0.3 introduced many bugs in the optimizer, upgrade to 10.2.0.4.

Regards
Michel
Previous Topic: length of long data type
Next Topic: deleting with a join
Goto Forum:
  


Current Time: Sat Nov 09 13:15:58 CST 2024