Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Dec 2000
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Nested query with UNION and JOIN

    I'm having some difficulty structuring a SQL statement that involves nesting an UNION within a JOIN.

    Let's say I have the following statement:

    SELECT Table1.Name, Table3.NumID
    FROM Table1 INNER JOIN Table3 ON Table1.ID = Table3.NumID
    UNION
    SELECT Table2.Name2, Table3.NumID
    FROM Table2 INNER JOIN Table3 ON Table2.ID = Table3.NumID;

    In this case, Table1 is JOINED w/Table3 and Table2 is JOINED w/Table3 and then these 2 results are being UNIONED together.

    But let's say I *first* want to UNION Table1 and Table2 and then JOIN this virtual result with Table3, how do I write that? I know this involves some sort of nested query. I tried:

    SELECT * FROM
    (SELECT Table1.Name FROM Table1
    UNION
    SELECT Table2.Name2 FROM Table2)
    INNER JOIN Table3 ON Table1.ID = Table3.NumID;

    This totally did not work and I'm a little lost. Any help would be greatly appreciated.

    Stephan Ip

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Nested query with UNION and JOIN

    Well, you've left out a few things. The only field you're pulling in your union query is Name, so there is nothing there to connect to the NumID field in Table3. Here's the same kind of SQL construction usings some tables in Northwind, but this works:

    SELECT * FROM
    (SELECT Customers.CustomerID, 0 As EmployeeID
    FROM Customers
    UNION
    SELECT Orders.CustomerID, Orders.EmployeeID
    FROM Orders) As E
    INNER JOIN Employees ON E.EmployeeID = Employees.EmployeeID

    The union subquery has to contain the field you're going to use to join the third table and you have to alias the subquery (the "...As E" bit)in order to make the join work without throwing an error.
    Charlotte

Posting Permissions

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