combining three queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

combining three queries

Author

Topic

reacha
Starting Member

49 Posts

Posted - 2010-12-21 : 16:29:11

in the below query i have three select statements combined by union

I want to write something like this select * from (select union select union select)

basically want to club these three select statements into one

SELECT AUDIT_MetricsInterp.AuditStampCompleted, AUDIT_MetricsInterp.PKGID, AUDIT_MetricsInterp.AuditStampLocked,
AUDIT_MetricsInterp.USERID, AUDIT_MetricsInterp.QueueEVENT1ID, AUDIT_MetricsInterp.AuditStampEntered,
EVENT1.NAME, USERMASTER.USERNAME,AUDIT_MetricsInterp.ProcessID, cast(WFUSER.FIELDVALUE_3.COL3 as varchar(30)) as AccountNumber
FROM AUDIT_MetricsInterp INNER JOIN
EVENT1 ON AUDIT_MetricsInterp.ProcessID = EVENT1.PROCESSID AND
AUDIT_MetricsInterp.QueueEVENT1ID = EVENT1.EVENT1ID INNER JOIN
USERMASTER ON AUDIT_MetricsInterp.USERID = USERMASTER.USERID INNER JOIN
WFUSER.FIELDVALUE_3 ON AUDIT_MetricsInterp.PKGID = WFUSER.FIELDVALUE_3.PKGID
WHERE (AUDIT_MetricsInterp.ProcessID IN (3)) AND (AUDIT_MetricsInterp.AuditStampCompleted >= CONVERT(VARCHAR(10), GETDATE()
- 1, 110)) AND (AUDIT_MetricsInterp.AuditStampCompleted < CONVERT(VARCHAR(10), GETDATE(), 110))

UNION

SELECT AUDIT_MetricsInterp.AuditStampCompleted, AUDIT_MetricsInterp.PKGID, AUDIT_MetricsInterp.AuditStampLocked,
AUDIT_MetricsInterp.USERID, AUDIT_MetricsInterp.QueueEVENT1ID, AUDIT_MetricsInterp.AuditStampEntered,
EVENT1.NAME, USERMASTER.USERNAME,AUDIT_MetricsInterp.ProcessID, cast(WFUSER.FIELDVALUE_5.COL3 as varchar(30)) as AccountNumber
FROM AUDIT_MetricsInterp INNER JOIN
EVENT1 ON AUDIT_MetricsInterp.ProcessID = EVENT1.PROCESSID AND
AUDIT_MetricsInterp.QueueEVENT1ID = EVENT1.EVENT1ID INNER JOIN
USERMASTER ON AUDIT_MetricsInterp.USERID = USERMASTER.USERID INNER JOIN
WFUSER.FIELDVALUE_5 ON AUDIT_MetricsInterp.PKGID = WFUSER.FIELDVALUE_5.PKGID
WHERE (AUDIT_MetricsInterp.ProcessID IN (5)) AND (AUDIT_MetricsInterp.AuditStampCompleted >= CONVERT(VARCHAR(10), GETDATE()
- 1, 110)) AND (AUDIT_MetricsInterp.AuditStampCompleted < CONVERT(VARCHAR(10), GETDATE(), 110))

UNION

SELECT AUDIT_MetricsInterp.AuditStampCompleted, AUDIT_MetricsInterp.PKGID, AUDIT_MetricsInterp.AuditStampLocked,
AUDIT_MetricsInterp.USERID, AUDIT_MetricsInterp.QueueEVENT1ID, AUDIT_MetricsInterp.AuditStampEntered,
EVENT1.NAME, USERMASTER.USERNAME,AUDIT_MetricsInterp.ProcessID, cast(WFUSER.FIELDVALUE_6.COL4 as varchar(30)) as AccountNumber
FROM AUDIT_MetricsInterp INNER JOIN
EVENT1 ON AUDIT_MetricsInterp.ProcessID = EVENT1.PROCESSID AND
AUDIT_MetricsInterp.QueueEVENT1ID = EVENT1.EVENT1ID INNER JOIN
USERMASTER ON AUDIT_MetricsInterp.USERID = USERMASTER.USERID INNER JOIN
WFUSER.FIELDVALUE_6 ON AUDIT_MetricsInterp.PKGID = WFUSER.FIELDVALUE_6.PKGID
WHERE (AUDIT_MetricsInterp.ProcessID IN (6)) AND (AUDIT_MetricsInterp.AuditStampCompleted >= CONVERT(VARCHAR(10), GETDATE() - 1,
110)) AND (AUDIT_MetricsInterp.AuditStampCompleted < CONVERT(VARCHAR(10), GETDATE(), 110))

Please help me out!!

X002548
Not Just a Number

15586 Posts

Posted - 2010-12-21 : 16:47:54

ahhh...the club method...I prefer slewdgehammers

Format the code and use [ code] [ /code] tags with no space in front

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources