Results 1 to 6 of 6
  1. #1
    Join Date
    May 2012
    Posts
    3

    Unanswered: Outer Join with conditions

    Good Morning,

    I am having problems getting an left outer join to work if I am running a condition on the second table. Here is an example of the SQL code I am running:

    select DRIVER.driver_id, LEGSUM.LS_TRIP_NUMBER, LEGSUM.LS_LEG_ID, LEGSUM.LS_LEG_STAT, LEGSUM.LS_POWER_UNIT, PUNIT.FLEET_MANAGER

    FROM (DRIVER DRIVER left outer JOIN LEGSUM ON DRIVER.DRIVER_ID = LEGSUM.LS_DRIVER) left outer JOIN PUNIT PUNIT ON LEGSUM.LS_POWER_UNIT = PUNIT.UNIT_ID

    WHERE DRIVER.ACTIVE_IN_DISP = 'True' AND LEGSUM.LS_LEG_STAT = 'ACTIVE' and (legsum.ls_expected_date >= current timestamp - 30 days or leg sum.ls_leg_stat is null)

    order by DRIVER.deliver_by, DRIVER.current_zone

    It will only pull in the records where the driver is linked to the legsum table. I thought putting the is null condition in would fix this but apparently I am wrong.

    I would really appreciate any help you all might be able to offer.

    Thanks and have a great day!

    Jay

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT driver.driver_id
         , legsum.ls_trip_number
         , legsum.ls_leg_id
         , legsum.ls_leg_stat
         , legsum.ls_power_unit
         , punit.fleet_manager
      FROM driver 
    LEFT OUTER 
      JOIN legsum 
        ON legsum.ls_driver = driver.driver_id 
       AND legsum.ls_leg_stat = 'active' 
       AND legsum.ls_expected_date >= CURRENT_TIMESTAMP - INTERVAL 30 DAY
    LEFT OUTER 
      JOIN punit 
        ON punit.unit_id = legsum.ls_power_unit
     WHERE driver.active_in_disp = 'true' 
    ORDER 
        BY driver.deliver_by
         , driver.current_zone
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2012
    Posts
    3
    Thank you very much for your reply. Can I ask what the 'ON' part does? I plugged in your code but I didn't get any results back.

    Thanks again for all your help, I am pretty new at this so I really appreciate you lending you expertise.

  4. #4
    Join Date
    May 2012
    Posts
    3

    On Second thought

    I needed to capitalize the ACTIVE. It works great. Thanks for your help. If you would still let me know how the ON works I would really appreciate it so I can tell moving forward.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ON is part of explicit join syntax

    every decent sql tutorial will cover it...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Or if you prefer:
    Code:
    SELECT driver_id,
         , ls_trip_number
         , ls_leg_id
         , ls_leg_stat
         , ls_power_unit
         , punit.fleet_manager
      FROM (SELECT drive_id, deliver_by, current_zone
              FROM driver
             WHERE driver.active_in_disp = 'True'
           ) d
           LEFT OUTER JOIN
           (SELECT ls_trip_number, ls_driver, ls_leg_id, ls_leg_stat
                 , ls_power_unit
              FROM legsum 
             WHERE ls_leg_stat = 'ACTIVE' 
               AND ls_expected_date >= CURRENT_TIMESTAMP - 30 DAYS
           ) l
           ON ls_driver = driver_id 
           LEFT OUTER JOIN
           punit 
           ON punit.unit_id = ls_power_unit
    ORDER BY d.deliver_by, d.current_zone
    The "ON" conditions specify the "links" between the tables. The "WHERE" conditions filter within one table (be it a base table, or an intermediate result table).
    That's the tricky part when filtering *after* a left outer join: a "WHERE" condition on a NULL field is never true (except for "IS NULL, of course), while an "ON" condition will always keep all rows from the left table.
    That explains why, in your first implementation, the rows that were still present after the outer join, were filtered out by your WHERE LS_LEG_STAT = 'ACTIVE', while they would be kept by moving the condition into the ON.
    Last edited by Peter.Vanroose; 05-16-12 at 03:28.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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