troubleshooting Question

How to join union subqueries?

Avatar of AshuTN
AshuTN asked on
Microsoft AccessSQL
7 Comments1 Solution1332 ViewsLast Modified:
I have 3 tables - TABLE A  and TABLES X and Y
Each table contains related records identifed by some number SN
I have created a query that selects SN, x, y
there is a union subquery that combines the values from TABLES X and Y
so the record for each SN may be as follows
1 x1
1       y1
1 x2
1      y2
The main query should join the subquery on SN and select all records related to SN in sub queries
My join is not working ...here is what I have so far...

SELECT SN, X.x, Y.y
FROM
A
INNER JOIN
(SELECT  SN, x, "" AS y
FROM X
UNION
SELECT SN, "", y
FROM Y)
AS B
ON A.SN = B.SN
WHERE
IN (SELECT B.SN from B);

The final query will need to GROUP results and select the MAX values for x and y

Thanks in advance
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros