asked on

UNION two selects

I have two selects (There will be more, but I need this to start and ill try to do the others).

SELECT 'Current Status' AS 'Type',
a.UserId AS userid,
a.LastNm AS LastName,
a.FirstNm AS FirstName,
a.ExpiresOnD AS Expdate,
a.NiStatus AS Currentstatus,
b.Docstatdesc AS action ,
d.MaidenNm AS Employer
FROM Users a
LEFT JOIN Documentstatus AS b ON a.Docstatusstat = b.DocumentStatus
LEFT JOIN Users AS d ON a.EmployerId = d.UserId
WHERE a.FirmId = 2
AND a.Archivedcont = 0
AND a.ExpiresOnD BETWEEN ( GETDATE() + 0 ) AND ( GETDATE() + 30 )
AND ( a.Docstatusstat <> 0
OR a.Docstatusstat IS NULL
)
AND a.UserType = 'contact'
ORDER BY a.ExpiresOnD;

UNION

SELECT 'I94' AS 'Type',
a.UserId AS userid,
a.LastNm AS LastName,
a.FirstNm AS FirstName,
a.I94DateD AS Expdate ,
a.NiStatus AS Currentstatus,
b.Docstatdesc AS action ,
d.MaidenNm AS Employer
FROM Users a
LEFT JOIN Documentstatus AS b ON a.DocI94stat = b.DocumentStatus
LEFT JOIN Users AS d ON a.EmployerId = d.UserId
WHERE a.FirmId = 2
AND a.Archivedcont = 0
AND a.I94DateD BETWEEN ( GETDATE() + 0 ) AND ( GETDATE() + 3000 )
AND a.UserType = 'contact'
AND ( a.DocI94stat <> 0
OR a.DocI94stat IS NULL
)
ORDER BY a.I94DateD;

The selects have different joins and conditions, but the resulting columns are the same and have the same headings. Is there a way to create a UNION of the two ? so it shows ALL in one table instead of needing to have one for each query ?

SOLUTION

Leo Torres

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Steve Wales

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ste5an

Or more readable:

SELECT	Q.A1, Q.A2, Q.A3 
FROM (
	SELECT column1 , column2, column3 FROM table1 WHERE <conditions>
	UNION
	SELECT column4, column5, column6 FROM table2 WHERE <conditions>
	) Q ( A1, A2, A3 ) 
ORDER BY Q.A1, Q.A2, Q.A3;

Open in new window