Results 1 to 2 of 2

Thread: Santa

  1. #1
    Join Date
    May 2011
    Posts
    1

    Red face Unanswered: Santa

    Need help in converting the below JOIN operators with LEFT OUTER JOIN.

    SELECT unit_downtime.unit_id, unit_type.unit_type_desc, locations.location_abbr, unit_type.unit_type,
    unit_downtime_detail.dt_dur_start_date as actual_date, NULL as plan_date,
    unit_downtime.downtime_id, unit_downtime_detail.downtime_category, unit.unit_name,
    unit_downtime.downtime_type, unit_downtime_detail.dt_duration,
    --unit_downtime.last_updated_time,
    unit_downtime_detail.last_updated_time,
    unit_downtime_detail.dt_cost,
    unit_downtime.downtime_desc, udtype.DOWNTIME_TYPE_DESC,
    a.analyst_name,
    unit.location_id,
    unit_type.unit_type_id
    FROM unit_downtime_detail, unit_downtime, unit, unit_type, locations, UNIT_DOWNTIME_TYPE udtype, analyst a
    WHERE unit_downtime_detail.downtime_id = unit_downtime.downtime_id
    AND unit_downtime.unit_id = unit.unit_id
    AND TOST_www.udf_GetUnitTypeID(unit.unit_ID, year(isnull(unit_downtime_detail.dt_dur_start_date ,getdate()))) = unit_type.unit_type_id
    AND unit.location_id = locations.location_id
    AND unit_downtime_detail.downtime_category = 'A'
    AND unit_downtime.downtime_type *= udtype.downtime_type
    AND unit_downtime_detail.last_updated_by *= a.user_id

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
      FROM unit_downtime_detail
    INNER
      JOIN unit_downtime
        ON unit_downtime.downtime_id = unit_downtime_detail.downtime_id
    INNER
      JOIN unit
        ON unit.unit_id = unit_downtime.unit_id
    INNER
      JOIN unit_type
        ON unit_type.unit_type_id = TOST_www.udf_GetUnitTypeID(unit.unit_ID, YEAR(ISNULL(unit_downtime_detail.dt_dur_start_date, GETDATE()))) 
    INNER
      JOIN locations
        ON locations.location_id = unit.location_id 
    LEFT OUTER
      JOIN unit_downtime_type AS udtype
        ON udtype.downtime_type = unit_downtime.downtime_type
    LEFT OUTER
      JOIN analyst AS a
        ON a.user_id = unit_downtime_detail.last_updated_by
     WHERE unit_downtime_detail.downtime_category = 'A'
    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
  •