CODEWITHSUNDEEP
sql-server
Glen Pearce
Glen Pearce

Reputation: 21

Using 2 UNION commands in a JOIN Query

I am trying to retrieve data from 2 identical Current and Archive tables using the JOIN Query below. However, this returns 2 result sets and also does not return the data in the Archive tables (I'm sure this is because I reference the 'nTrans' CURRENT table in the JOIN command, but I don't know how to reference both tables).

I've tried every combination of query, without success. What am I doing wrong?

SELECT N.TranType,N.UserNo,N.TranCode
FROM (SELECT TranType,UserNo,TranCode FROM nTrans 
UNION ALL SELECT TranType,UserNo,TranCode FROM nTransArc) 
AS N
SELECT H.HeaderKey 
FROM (SELECT HeaderKey FROM MoveHdr
UNION ALL SELECT HeaderKey FROM MoveHdrArc) 
AS H
JOIN nTrans N ON H.HeaderKey = N.TranType + N.UserNo + N.TranCode

Upvotes: 0

Views: 52

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

I think you want this:

SELECT N.TranType, N.UserNo, N.TranCode
FROM (SELECT TranType, UserNo, TranCode FROM nTrans 
      UNION ALL 
      SELECT TranType, UserNo, TranCode FROM nTransArc) AS N
JOIN (SELECT HeaderKey FROM MoveHdr
      UNION ALL
      SELECT HeaderKey FROM MoveHdrArc) AS H
ON H.HeaderKey = N.TranType + N.UserNo + N.TranCode

Upvotes: 2

Related Questions