Results 1 to 2 of 2

Thread: outer join

  1. #1
    Join Date
    Oct 2003
    Posts
    51

    Unanswered: outer join

    I have 2 tables like this.
    SQL> select * from gl_dept;

    DEPTNO Dept_status
    ---------- -
    10 Active
    20 Active
    30 Inactive
    40 Inactive
    50 Inactive

    SQL> select * from step4;

    DEPTNO VOLUME_DAte HOURS
    ---------- --------- ----------
    30 21-FEB-05 40
    10 20-FEB-05 30
    20 20-FEB-05 20
    30 20-FEB-05 40
    40 20-FEB-05 10

    when i do the query for inactive records for 21st, my result should be:

    30 21-feb-05 40
    40 0 0
    50 0 0

    when i do the query for inactive records for 20th, my result should be:

    30 20-feb-05 40
    40 20-feb-05 10
    50 0 0.

    How to do this.
    sampath gowri

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select gl_dept.DEPTNO
         , nvl(step4.VOLUME_DAte,0)
         , nvl(step4.HOURS,0)
      from gl_dept
    left outer
      join step4
        on gl_dept.DEPTNO
         = step4.DEPTNO
       and step4.VOLUME_DAte
         = '20-FEB-05'
     where gl_dept.Dept_status
         = 'Inactive'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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