Using UNION with three queries

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

I have three queries that produce the same three fields:

SELECT A.ProductID, A.Description, A.Quantity FROM Query1 AS A
SELECT B.ProductID, B.Description, B.Quantity FROM Query2 AS B
SELECT C.ProductID, C.Description, C.Quantity FROM Query3 AS C

Obviously, none of these is a complete query statement, but I'm just giving
the idea. I want to produce one table with five fields: ProductID,
Description, A.Quantity, B.Quantity and C.Quantity, where all the quantity
fields for a given ProductID/Description are pulled together on one line (one
record) and where one or more of the quantity fields (but not all three) can
be null.

I have a query that I'm using presently when there are only two original
queries:

SELECT A.ProductID, A.Description, A.Quantity, B.Quantity
FROM Query1 AS A LEFT JOIN Query2 AS B
ON A.ProductID=B.ProductID
UNION SELECT B.ProductID, B.Description, Null, B.Quantity
FROM Query1 AS A RIGHT JOIN Query2 AS B
ON A.ProductID = B.ProductID
WHERE A.ProductID IS Null;

I just don't know how to make it work with a third query. Can someone help
me with an idea of how to do this? Thanks!
 
I have three queries that produce the same three fields:

SELECT A.ProductID, A.Description, A.Quantity FROM Query1 AS A
SELECT B.ProductID, B.Description, B.Quantity FROM Query2 AS B
SELECT C.ProductID, C.Description, C.Quantity FROM Query3 AS C

Obviously, none of these is a complete query statement, but I'm just giving
the idea. I want to produce one table with five fields: ProductID,
Description, A.Quantity, B.Quantity and C.Quantity, where all the quantity
fields for a given ProductID/Description are pulled together on one line (one
record) and where one or more of the quantity fields (but not all three) can
be null.

I have a query that I'm using presently when there are only two original
queries:

SELECT A.ProductID, A.Description, A.Quantity, B.Quantity
FROM Query1 AS A LEFT JOIN Query2 AS B
ON A.ProductID=B.ProductID
UNION SELECT B.ProductID, B.Description, Null, B.Quantity
FROM Query1 AS A RIGHT JOIN Query2 AS B
ON A.ProductID = B.ProductID
WHERE A.ProductID IS Null;

I just don't know how to make it work with a third query. Can someone help
me with an idea of how to do this? Thanks!
Click to expand...

I think you'll have to do it in stages. First create a UNION query selecting
only the ProductID and Description fields.

Then join THIS query with a Left Outer Join to each of Query1, Query2 and
Query3 to pick up the A, B and C quantities.
 
Back
Top