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 ?
LVL 1
AleksAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leo TorresSQL DeveloperCommented:
Not sure what your asking a Union does exactly what you stated above two different queries with the same column info. Are you referring to the missing distinct's. If this is so use a "Union ALL" this will bring back even dups. Not sure if this is what your asking. What is your end game here? What are you trying to accomplish?
ste5anSenior DeveloperCommented:
Remove both ORDER BYs and the first semi-colon..
Steve WalesSenior Database AdministratorCommented:
If what you're asking is "How do I name my columns"

Then you do this:

select column1 as A1, column2 as A2, column3 as A3 from table1 where <conditions>
UNION
select column4, column5, column6 from table2 where <conditions>

What this gets you at the end is a list of columns called A1, A2, A3.

Alias the columns for your first query only, UNION (or UNION ALL depending on if you want any duplicates to show) and then your result set has columns named as the aliases of the first query in the list.

Then at the end if you want to sort the lot as a total:

select * from
(
select column1 as A1, column2 as A2, column3 as A3 from table1 where <conditions>
UNION
select column4, column5, column6 from table2 where <conditions>
)
order by A1, A2, A3

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.