Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Angry Unanswered: Query help (Oracle to SQL) with Joins

    Can someone help convert this query to SQL Server using the ANSI compliant LEFT OUTER and RIGHT OUTER join syntax?

    Thanks!

    SELECT

    Stuff

    FROM
    TD,
    ALMC,
    ADO,
    AD,
    ADDE

    WHERE

    TD.ACCOUNTNUMBER = ALMC.ACCOUNTNUMBER(+) AND
    TD.XDATE = ALMC.XDATE(+) AND
    TD.SNUMBER = ALMC.SNUMBER(+) AND
    ALMC.AIRPORTCODE = ADO.AIRPORTCODE(+) AND
    ALMC.CARRIERCODE = AD.CARRIERCODE(+) AND
    ALMC.DESTAIRPORTCODE = ADDE.AIRPORTCODE(+)

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A straight join returning only complete records would look like this:

    SELECT *
    FROM TD
    inner join ALMC on TD.ACCOUNTNUMBER = ALMC.ACCOUNTNUMBER
    AND TD.XDATE = ALMC.XDATE
    AND TD.SNUMBER = ALMC.SNUMBER
    inner join ADO on ALMC.AIRPORTCODE = ADO.AIRPORTCODE
    inner join AD on ALMC.CARRIERCODE = AD.CARRIERCODE
    inner join ADDE on ALMC.DESTAIRPORTCODE = ADDE.AIRPORTCODE

    If you want to return all higher-level records regardless of whether subtable have matching records, use LEFT OUTER JOIN:

    SELECT *
    FROM TD
    left outer join ALMC on TD.ACCOUNTNUMBER = ALMC.ACCOUNTNUMBER
    AND TD.XDATE = ALMC.XDATE
    AND TD.SNUMBER = ALMC.SNUMBER
    left outer join ADO on ALMC.AIRPORTCODE = ADO.AIRPORTCODE
    left outer join AD on ALMC.CARRIERCODE = AD.CARRIERCODE
    left outer join ADDE on ALMC.DESTAIRPORTCODE = ADDE.AIRPORTCODE

    blindman

  3. #3
    Join Date
    Sep 2003
    Posts
    3

    Thumbs up Thanks and more help needed!

    Thanks for the accurate response... I need some help with one more.

    Here is the Query in Oracle:

    create view V_ST_AIRLINE_LEG as
    select
    *
    from
    st_airline_leg sta,
    airport_description_org ao,
    airport_description_dest ad,
    airline_description
    where
    (sta.orgairportcode = ao.airportcode(+)) and
    (sta.destairportcode = ad.airportcode(+)) and
    (sta.carriercode = airline_description.carriercode(+))

    I created this in SQL Server, but the resultset doesn't contain as many items as the Oracle View. Any ideas?

    CREATE VIEW V_ST_AIRLINE_LEG AS
    SELECT
    *
    FROM
    ST_AIRLINE_LEG STA
    LEFT OUTER JOIN AIRPORT_DESCRIPTION_ORG AO ON
    STA.ORGAIRPORTCODE = AO.AIRPORTCODE
    LEFT OUTER JOIN AIRPORT_DESCRIPTION_DEST AD ON
    STA.DESTAIRPORTCODE = AD.AIRPORTCODE
    LEFT OUTER JOIN AIRLINE_DESCRIPTION ON
    STA.CARRIERCODE = AIRLINE_DESCRIPTION.CARRIERCODE

    Thanks again!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You got me. Your query should return all the records from ST_AIRLINE_LEG, plus duplicates if there is more than one mathing record in any of the subtables.

    Perhaps somebody with more familiarity with Oracal syntax can diagnose this.

    Are you sure your recordsets are identical?

    blindman

  5. #5
    Join Date
    Sep 2003
    Location
    London
    Posts
    56
    This tutorial may be of help to you.

    http://www.databasejournal.com/featu...le.php/2209301

    Regards,
    Matt.

  6. #6
    Join Date
    Sep 2003
    Posts
    3

    Angry Oops!

    The recordsets were different. After I dropped the 4 tables, and copied them over from Oracle the resultsets were identical. Thanks!

Posting Permissions

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