Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Location
    Wellington, NZ
    Posts
    5

    Unanswered: Problems with right outer join

    Hiya,
    I'm a little stuck with this one but it is probably my understanding off how outer joins are affected with more conditions that is the problem.

    Basically this first query works and delivers all the records in office even if they have a null for sales area which is as I would have expected.

    SELECT
    offout.name as office_name,
    grpout.name as sales_area
    FROM
    edw.t_office_all offout,
    edw.t_group_all grpout
    WHERE
    offout.sales_area = grpout.id (+)

    However when I try adding some correlation columns for extracting data from a warehouse I only get the office records that have a sales area specified. Office records that have null for sales area are not returned.

    SELECT
    offout.name as office_name,
    grpout.name as sales_area
    FROM
    edw.t_office_all offout,
    edw.t_group_all grpout
    WHERE
    offout.SNAPSHOT_TIME =
    (
    SELECT
    max(offin.SNAPSHOT_TIME)
    FROM
    edw.t_office_all offin
    WHERE
    offin.id = offout.id
    ) AND
    grpout.SNAPSHOT_TIME =
    (
    SELECT
    max(grpin.SNAPSHOT_TIME)
    FROM
    edw.t_group_all grpin
    WHERE
    grpin.id = grpout.id

    )
    AND offout.sales_area = grpout.id (+)

    Thanks for any help.

    Cheers

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select offout.name as office_name
         , grpout.name as sales_area
      from edw.t_office_all offout 
    left outer
      join edw.t_group_all grpout 
        on offout.sales_area = grpout.id 
       and grpout.SNAPSHOT_TIME =
           ( select max(grpin.SNAPSHOT_TIME) 
               from edw.t_group_all grpin
              where grpin.id = grpout.id )
     where offout.SNAPSHOT_TIME 
         = ( select max(offin.SNAPSHOT_TIME) 
               from edw.t_office_all offin
              where offin.id = offout.id )
    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
  •