Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2009
    Posts
    2

    Unanswered: Need to join 3 tables - OUTER JOIN with INNER JOIN

    Hi,

    I am trying to join 3 tables (Employee, EmployeeOrg, and Org) structured below. I need to return all Org names no matter the membership, so an outer join definitely should apply here, but it's not working.

    The dev environment uses SQL Server Express 2005, but Production will be SQL Server 2005. The query is:

    SELECT E.EmpName, A.OrgName, EA.OrgID
    FROM EmployeeOrg EA
    INNER JOIN Employee AS E
    ON EA.EmpID = E.EmpID
    LEFT OUTER JOIN Org AS A
    ON EA.OrgID = A.OrgID

    Data:

    EmpID EmpName
    --------------
    1001 M. Scott
    1002 P. Beesly
    1003 D. Schrute

    EmpID OrgID
    ------------------
    1001 1
    1001 3
    1002 2
    1003 1

    OrgID OrgName
    ------------------
    1 PTA
    2 IEEE
    3 AAA

    Desired output:

    EmpName OrgName OrgID
    ----------------------------------------
    M. Scott PTA 1
    M. Scott IEEE NULL
    M. Scott AAA 3
    P. Beesly PTA NULL
    P. Beesly IEEE 2
    P. Beesly AAA NULL
    D. Schrute PTA 1
    D. Schrute IEEE NULL
    D. Schrute AAA NULL


    It seems to be treating the left outer join as a regular join (not returning any NULLs). If I just join the OrgName and OrgID tables I do get some nulls, but I really need the additional join to the Emp table. I've read many examples and articles online and this should be working but maybe I'm missing something on the syntax. Any help or pointers would be appreciated!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT E.EmpName
         , A.OrgName
         , EA.OrgID
      FROM Employee AS E 
    CROSS
      JOIN Org AS A 
    LEFT OUTER
      JOIN EmployeeOrg EA
        ON EA.EmpID = E.EmpID
       AND EA.OrgID = A.OrgID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2009
    Posts
    2
    Rudy,

    Thanks for the quick reply and excellent guidance! I had previously been reading another one of your threads (http://www.dbforums.com/microsoft-sq...le-tables.html) which helped me construct my earlier query. Your suggestion worked perfectly. Somehow I've been able to get away without using CROSS JOINs before. I'll have to read up on it to understand how it works.

Posting Permissions

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