Nested query with UNION and JOIN

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote