Link to home
Avatar of Aleks
AleksFlag for United States of America

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
Avatar of Leo Torres
Leo Torres
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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