Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2009
    Posts
    2

    Unanswered: Left Outer join issue

    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

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Iadmireyouareacleverpersonasyoucanmanagethistotall yunformattedmess.

    Maybe you do not understand the sentence above, but be quite sure that the other readers similarly understand that code you posted, as it is totally unreadable because it lacks any formatting.

    It reminded me one proverb: smart people do not need to tidy up because they are good at mess; only fools have to maintain order to master anything.

    But, maybe you are not so smart, as you overlooked the "ROWNUM <= 50" condition in the LEFT JOIN query, which is missing in the "INNER JOIN" query. I put it into double quotes, as I cannot find any join there.
    Maybe there are other differences between those queries, which may affect the resultset row count. But I will let their finding on you...

  3. #3
    Join Date
    Feb 2009
    Posts
    2
    Flyboy

    I dont know what kind of formatting u require . And better not to answer a question if you dont understand it properly . Incomplete knowledge is always dangerous .


    As far as query mentioned in my post is a single query with a LEFT OUTER JOIN (bold for people to notice it ) . The Left and Right tables for that outer join are complex subqueries.

    I think i have made myself clear now

    Thanks and Regards
    Akhil Goyal

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    I dont know what kind of formatting u require .
    Look at the code you posted. It looks at the first sight as two SELECT statements divided by that bold LEFT OUTER JOIN.
    SQL is not text. In this complex SQL, each clause (FROM, WHERE,...) should begin in the new line and the code should be indented so you could easily see in what is the depth of the subquery (that the posted code uses too much).
    Incomplete knowledge is always dangerous
    I cannot agree more. So, you broke the query into pieces, checked that subquery results and examined whether everything is correct. But, I wonder why you did not post it here.

    By the way, that query can be (with the help of WITH clause and after putting the WHERE condition into the main query) transformed to something like:
    Code:
    WITH r AS (SELECT DISTINCT user_id, active_yn
           FROM roster
           WHERE section_id = 353790
             AND active_yn = 'Y'
             AND is_deleted <> '1'),
         rep AS (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'))
    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,
      FROM (
        SELECT a.*, ROWNUM rnum
        FROM (
          SELECT r.user_id, r.active_yn, eusers.first_name, eusers.last_name
          FROM (
            r LEFT JOIN 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 rep_atmt ON all_users.user_id = rep_atmt.user_id
    WHERE (rep_atmt.user_id, rep_atmt.section_id, rep_atmt.assignment_id, rep_atmt.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)
    ORDER BY Upper(all_users.last_name), Upper(all_users.first_name), 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
    It is still complex (because of ALL_USERS TOP N subquery - but it can be put into the WITH section too), but: do you not see the code flow (=what it queries and what should be done and returned) more clear than from the one you posted?
    But, if you are able to easily make changes (=so you clearly find the changed part and know the impact of that change) to that originally formatted query, good luck.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •