Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005
    Posts
    6

    Unanswered: ANSI Outer Join Syntax - Need Help

    Hi,

    I'am currently investigating the task of changing a number of queries using the *= and/or =* syntax for outer joins to the ANSI standard LEFT JOIN and/or RIGHT JOIN syntax. I have managed to reproduce many results with simple queries (2 tables with a single key), however I'am having a rather difficult time over queries where particular tables are used more than once, and with tables where the join involves more than 1 column in the key. I have been getting a variety of differenct messages with the most recent being the following:

    "Tables 'OPERATOR_SCALE_PART' and 'OPERATOR_SCALE_PART' have same exposed names. Use correlation names to distinguish them."

    I have tried to find examples (googled) that are similiar to what I have been trying, but have not found any. I have looked up what the message means (I think it is telling me to use table alias's, which I have no problem doing in the old syntax, but they do not seem to work for me using the ANSI syntax). Any help would be appreciated.

    Thanks,
    Bill40

    Original Query:

    SELECT OPERATOR_SCALE_PART.operator_id,
    OPERATOR_SCALE_PART.machine_type_id,
    OPERATOR_SCALE_PART.percent_of_scale,
    OPERATOR.employee_no,
    MACHINE_TYPE.machine_type_name,
    OPERATOR_SCALE_PART.scale_unit_id,
    OPERATOR.operator_name,
    OPERATOR_SCALE_PART.payrate,
    OPERATOR_PART_BONUSES.bonus_id,
    OPERATOR_PART_BONUSES.bonus_percent,
    OPERATOR_SCALE_PART.operator_name
    FROM OPERATOR_SCALE_PART,
    OPERATOR,
    MACHINE_TYPE,
    OPERATOR_PART_BONUSES
    WHERE ( OPERATOR.operator_id =* OPERATOR_SCALE_PART.operator_id) and ( MACHINE_TYPE.machine_type_id =* OPERATOR_SCALE_PART.machine_type_id)
    and (OPERATOR_SCALE_PART.scale_id *= OPERATOR_PART_BONUSES.scale_id)
    and ( OPERATOR_SCALE_PART.scale_part_id *= OPERATOR_PART_BONUSES.scale_part_id)
    and ( OPERATOR_SCALE_PART.operator_id *= OPERATOR_PART_BONUSES.operator_id)
    and ( OPERATOR_SCALE_PART.machine_type_id *= OPERATOR_PART_BONUSES.machine_type_id)
    and (OPERATOR_SCALE_PART.scale_id = 2109364
    AND OPERATOR_SCALE_PART.scale_part_id = 1)
    ORDER BY OPERATOR_SCALE_PART.operator_id ASC,
    OPERATOR_SCALE_PART.machine_type_id ASC,
    OPERATOR_PART_BONUSES.bonus_id ASC


    ANSI Version:

    SELECT OPERATOR_SCALE_PART.operator_id,
    OPERATOR_SCALE_PART.machine_type_id,
    OPERATOR_SCALE_PART.percent_of_scale,
    OPERATOR.employee_no,
    MACHINE_TYPE.machine_type_name,
    OPERATOR_SCALE_PART.scale_unit_id,
    OPERATOR.operator_name,
    OPERATOR_SCALE_PART.payrate,
    OPERATOR_PART_BONUSES.bonus_id,
    OPERATOR_PART_BONUSES.bonus_percent,
    OPERATOR_SCALE_PART.operator_name
    FROM OPERATOR LEFT JOIN OPERATOR_SCALE_PART ON (OPERATOR.operator_id = OPERATOR_SCALE_PART.operator_id),
    MACHINE_TYPE RIGHT JOIN OPERATOR_SCALE_PART ON (MACHINE_TYPE.machine_type_id = OPERATOR_SCALE_PART.machine_type_id),
    OPERATOR_SCALE_PART LEFT JOIN OPERATOR_PART_BONUSES ON (OPERATOR_SCALE_PART.scale_id = OPERATOR_PART_BONUSES.scale_id
    AND OPERATOR_SCALE_PART.scale_part_id = OPERATOR_PART_BONUSES.scale_part_id
    AND OPERATOR_SCALE_PART.operator_id = OPERATOR_PART_BONUSES.operator_id
    AND OPERATOR_SCALE_PART.machine_type_id = OPERATOR_PART_BONUSES.machine_type_id)
    WHERE OPERATOR_SCALE_PART.scale_id = 2109364
    AND OPERATOR_SCALE_PART.scale_part_id = 1
    ORDER BY OPERATOR_SCALE_PART.operator_id ASC,
    OPERATOR_SCALE_PART.machine_type_id ASC,
    OPERATOR_PART_BONUSES.bonus_id ASC

  2. #2
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    Here is an example that I use to teach my students at Uni.

    CREATE TABLE JMH_Grade
    (
    refID NUMERIC(5, 0) NOT NULL,
    StudentID NUMERIC(5, 0) NOT NULL,
    SubjectID NUMERIC(5, 0) NOT NULL,
    ExamDate DATETIME,
    ExamResult VARCHAR(1)
    )

    CREATE TABLE JMH_Student
    (
    refID NUMERIC(5, 0) NOT NULL,
    Name VARCHAR(50),
    Phone VARCHAR(20)
    )

    CREATE TABLE JMH_Subject
    (
    refID NUMERIC(5, 0) NOT NULL,
    Name VARCHAR(50),
    Code VARCHAR(10)
    )


    CREATE TABLE JMH_StudentSubject
    (
    StudentID NUMERIC NOT NULL,
    SubjectID NUMERIC NOT NULL
    )


    insert into JMH_Student (refID, Name, Phone) values (1, 'John', '38221234')
    insert into JMH_Student (refID, Name, Phone) values (2, 'Garry', '07 12345')
    insert into JMH_Student (refID, Name, Phone) values (3, 'Yuki', '07 12345')
    insert into JMH_Student (refID, Name, Phone) values (4, 'Leigh', '02 1234 5678')
    insert into JMH_Student (refID, Name, Phone) values (5, 'Keith', '07 3304 1111')


    insert into JMH_Subject (refID, Name, Code) values (100, 'Accounting', 'ACCT')
    insert into JMH_Subject (refID, Name, Code) values (200, 'English', 'ENGL')
    insert into JMH_Subject (refID, Name, Code) values (300, 'Japanese', 'JAPN')
    insert into JMH_Subject (refID, Name, Code) values (400, 'Keyboards101', 'KBD1')

    insert into JMH_StudentSubject (StudentID, SubjectID) values (1, 100)
    insert into JMH_StudentSubject (StudentID, SubjectID) values (1, 200)
    insert into JMH_StudentSubject (StudentID, SubjectID) values (1, 300)
    insert into JMH_StudentSubject (StudentID, SubjectID) values (2, 200)
    insert into JMH_StudentSubject (StudentID, SubjectID) values (2, 300)
    insert into JMH_StudentSubject (StudentID, SubjectID) values (3, 300)
    insert into JMH_StudentSubject (StudentID, SubjectID) values (3, 400)
    insert into JMH_StudentSubject (StudentID, SubjectID) values (4, 100)

    insert into JMH_Grade (refID, StudentID, ExamDate, ExamResult, SubjectID)
    values (1, 1, GetDate(), 'A', 100)
    insert into JMH_Grade (refID, StudentID, ExamDate, ExamResult, SubjectID)
    values (2, 1, GetDate(), 'B', 200)
    insert into JMH_Grade (refID, StudentID, ExamDate, ExamResult, SubjectID)
    values (3, 3, GetDate(), 'E', 300)
    insert into JMH_Grade (refID, StudentID, ExamDate, ExamResult, SubjectID)
    values (4, 3, GetDate(), 'A', 200)
    insert into JMH_Grade (refID, StudentID, ExamDate, ExamResult, SubjectID)
    values (5, 2, GetDate(), 'C', 400)


    select * from JMH_Student
    select * from JMH_Subject
    select * from JMH_StudentSubject
    select * from JMH_Grade

    SELECT DISTINCT STU.Name
    FROM JMH_Student STU
    ,JMH_StudentSubject SS
    WHERE SS.StudentID = STU.RefId

    -- Get all the students that may or may not have subjects
    SELECT DISTINCT STU.Name
    FROM JMH_Student STU
    ,JMH_StudentSubject SS
    WHERE SS.StudentID =* STU.RefId


    -- Get all the students (and their stubject id)
    -- Nb. Students may or may not have subjects
    SELECT STU.Name, SS.SubjectID, SUB.Name
    FROM JMH_Student STU
    ,JMH_StudentSubject SS
    ,JMH_Subject SUB
    WHERE SS.StudentID =* STU.RefId
    AND SUB.RefId =* SS.SubjectID


    SELECT STU.Name, SS.SubjectID, SUB.Name
    FROM JMH_Student STU
    LEFT OUTER JOIN JMH_StudentSubject SS ON SS.StudentID = STU.RefId
    LEFT OUTER JOIN JMH_Subject SUB ON SUB.RefId = SS.SubjectID



    -- Get all the students (and their stubject id, subject and exam results) -- Nb. Students may or may not have subjects

    -- T-SQL style
    SELECT STU.Name, SS.SubjectID, SUB.Name, G.ExamResult
    FROM JMH_Student STU
    ,JMH_StudentSubject SS
    ,JMH_Subject SUB
    ,JMH_Grade G
    WHERE SS.StudentID =* STU.RefId
    AND SUB.RefId =* SS.SubjectID
    AND G.StudentID =* SS.StudentID
    AND G.SubjectID =* SUB.RefID



    SELECT DISTINCT STU.Name
    FROM JMH_Student STU
    ,JMH_StudentSubject SS
    WHERE SS.StudentID = STU.RefId

    --ANSI SQL
    SELECT DISTINCT STU.Name
    FROM JMH_Student AS STU JOIN JMH_StudentSubject AS SS ON ( SS.StudentID = STU.RefId )


    -- Get all the students that may or may not have subjects
    SELECT DISTINCT STU.Name
    FROM JMH_Student STU
    ,JMH_StudentSubject SS
    WHERE SS.StudentID =* STU.RefId


    --ANSI SQL
    SELECT DISTINCT STU.Name
    FROM JMH_Student AS STU RIGHT OUTER JOIN JMH_StudentSubject AS SS ON ( SS.StudentID = STU.RefId )

    -- Get all the students (and their stubject id)
    -- Nb. Students may or may not have subjects
    SELECT STU.Name, SS.SubjectID, SUB.Name
    FROM JMH_Student STU
    ,JMH_StudentSubject SS
    ,JMH_Subject SUB
    WHERE SS.StudentID =* STU.RefId
    AND SUB.RefId =* SS.SubjectID

    --ANSI SQL
    SELECT DISTINCT STU.Name, SS.SubjectID, SUB.Name
    FROM JMH_StudentSubject AS SS RIGHT OUTER JOIN JMH_Student AS STU ON ( SS.StudentID = STU.RefId )
    LEFT OUTER JOIN JMH_Subject AS SUB ON ( SUB.RefId = SS.SubjectID )



    -- Get all the students (and their stubject id, subject and exam results) -- Nb. Students may or may not have subjects

    -- T-SQL style
    SELECT STU.Name, SS.SubjectID, SUB.Name, G.ExamResult
    FROM JMH_Student STU
    ,JMH_StudentSubject SS
    ,JMH_Subject SUB
    ,JMH_Grade G
    WHERE SS.StudentID =* STU.RefId
    AND SUB.RefId =* SS.SubjectID
    AND G.StudentID =* SS.StudentID
    AND G.SubjectID =* SUB.RefID

    --ANSI SQL
    SELECT distinct STU.Name, SS.SubjectID, SUB.Name, G.ExamResult
    FROM JMH_StudentSubject AS SS RIGHT OUTER JOIN JMH_Student AS STU ON ( SS.StudentID = STU.RefId )
    LEFT OUTER JOIN JMH_Subject AS SUB ON ( SUB.RefId = SS.SubjectID )
    LEFT OUTER JOIN JMH_Grade AS G ON ( G.StudentID = SS.StudentID ) AND ( G.SubjectID = SUB.RefID )

  3. #3
    Join Date
    Oct 2005
    Posts
    6
    Thank you for the help

Posting Permissions

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