SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Union All?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WJHamel
Aged Yak Warrior

USA
614 Posts
Posted - 03/26/2012 :  12:39:24  Show Profile  Reply with Quote
I want the following query (which i'm going to add many more extensions to) to output one result set. I assume i need to use a UNION ALL clause, but not sure where to do so:



--Complaint query
SELECT cfs.CFSID,
       cfs.ReceivedTime,
       ISNULL(cfs.OtherAgency, 0) AS OtherAgency,
       cfs.OccuredStart,
       cfs.OccuredEnd,
       cd.ReportCode,
       CONVERT(CHAR(15), cfs.ReceivedTime, 101) AS CallDate,
       NAME = ISNULL(pd.LastName, '') + ', ' + ISNULL(pd.FirstName, '')
FROM   CombinedDesc cd,
       CFSData cfs,
       PersonData pd
WHERE  cfs.CallTypeID = cd.ID
       AND cfs.CFSID = pd.FormID
       AND pd.FormTypeID = 10000
       --AND pd.LastName LIKE '%Br%'
       --AND pd.FirstName LIKE '%h%'
       --AND cfs.OccuredStart < '03/27/2010'
       --AND cfs.OccuredStart >= '02/25/2012 0:00:00'
ORDER BY
       cfs.CFSID DESC

--Aggravated assault query:
SELECT cfs.CFSID,
       cfs.ReceivedTime,
       ISNULL(cfs.OtherAgency, 0) AS OtherAgency,
       cfs.OccuredStart,
       cfs.OccuredEnd,
       cd.ReportCode,
       CONVERT(CHAR(15), cfs.ReceivedTime, 101) AS CallDate,
       cn.Description AS CallNature,
       NAME = ISNULL(pd.LastName, '') + ', ' + ISNULL(pd.FirstName, '')
FROM   CombinedDesc cd,
       CFSData cfs,
       CombinedDesc cn,
       PersonData pd
WHERE  cfs.CallTypeID = cd.ID
       AND cfs.NatureOfCallID = cn.ID
       AND cfs.NatureOfCallID = 4367
       AND cfs.CFSID = pd.FormID
       AND pd.FormTypeID = 10000
       
ORDER BY
       cfs.CFSID DESC
	
--911 Hangup query
SELECT cfs.CFSID,
       cfs.ReceivedTime,
       ISNULL(cfs.OtherAgency, 0) AS OtherAgency,
       cfs.OccuredStart,
       cfs.OccuredEnd,
       cd.ReportCode,
       CONVERT(CHAR(15), cfs.ReceivedTime, 101) AS CallDate,
       cn.Description AS CallNature,
       NAME = ISNULL(pd.LastName, '') + ', ' + ISNULL(pd.FirstName, '')
FROM   CombinedDesc cd,
       CFSData cfs,
       CombinedDesc cn,
       PersonData pd
WHERE  cfs.CallTypeID = cd.ID
       AND cfs.NatureOfCallID = cn.ID
       AND cfs.NatureOfCallID = 1944
       AND cfs.CFSID = pd.FormID
       AND pd.FormTypeID = 10000
 
ORDER BY
       cfs.CFSID DESC
	
--911 Misdial:

SELECT cfs.CFSID,
       cfs.ReceivedTime,
       ISNULL(cfs.OtherAgency, 0) AS OtherAgency,
       cfs.OccuredStart,
       cfs.OccuredEnd,
       cd.ReportCode,
       CONVERT(CHAR(15), cfs.ReceivedTime, 101) AS CallDate,
       cn.Description AS CallNature,
       NAME = ISNULL(pd.LastName, '') + ', ' + ISNULL(pd.FirstName, '')
FROM   CombinedDesc cd,
       CFSData cfs,
       CombinedDesc cn,
       PersonData pd
WHERE  cfs.CallTypeID = cd.ID
       AND cfs.NatureOfCallID = cn.ID
       AND cfs.NatureOfCallID = 1944
       AND cfs.CFSID = pd.FormID
       AND pd.FormTypeID = 10000
 
ORDER BY
       cfs.CFSID DESC

--Abandoned Vehicle:


SELECT cfs.CFSID,
       cfs.ReceivedTime,
       ISNULL(cfs.OtherAgency, 0) AS OtherAgency,
       cfs.OccuredStart,
       cfs.OccuredEnd,
       cd.ReportCode,
       CONVERT(CHAR(15), cfs.ReceivedTime, 101) AS CallDate,
       cn.Description AS CallNature,
       NAME = ISNULL(pd.LastName, '') + ', ' + ISNULL(pd.FirstName, '')
FROM   CombinedDesc cd,
       CFSData cfs,
       CombinedDesc cn,
       PersonData pd
WHERE  cfs.CallTypeID = cd.ID
       AND cfs.NatureOfCallID = cn.ID
       AND cfs.NatureOfCallID = 1361
       AND cfs.CFSID = pd.FormID
       AND pd.FormTypeID = 10000

ORDER BY
       cfs.CFSID DESC
	
	
--Advise calls:

SELECT cfs.CFSID,
       cfs.ReceivedTime,
       ISNULL(cfs.OtherAgency, 0) AS OtherAgency,
       cfs.OccuredStart,
       cfs.OccuredEnd,
       cd.ReportCode,
       CONVERT(CHAR(15), cfs.ReceivedTime, 101) AS CallDate,
       cn.Description AS CallNature,
       NAME = ISNULL(pd.LastName, '') + ', ' + ISNULL(pd.FirstName, '')
FROM   CombinedDesc cd,
       CFSData cfs,
       CombinedDesc cn,
       PersonData pd
WHERE  cfs.CallTypeID = cd.ID
       AND cfs.NatureOfCallID = cn.ID
       AND cfs.NatureOfCallID = 1369
       AND cfs.CFSID = pd.FormID
       AND pd.FormTypeID = 10000

ORDER BY
       cfs.CFSID DESC


thanks!

james

X002548
Not Just a Number

15586 Posts
Posted - 03/26/2012 :  12:44:25  Show Profile  Reply with Quote
Do you want to know which query produced the output as well?

In any case, if all the columns are the same and they all have the same data type, just add UNION ALL Before your comments

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/
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
4979 Posts
Posted - 03/26/2012 :  12:49:01  Show Profile  Visit russell's Homepage  Reply with Quote
And remove all the ORDER BYs except for the last one.
Go to Top of Page

WJHamel
Aged Yak Warrior

USA
614 Posts
Posted - 03/26/2012 :  13:00:40  Show Profile  Reply with Quote
Brett: no, i don't 'really' need to know which query produced which, but lets say I did. What would i mod to make that happen? I'm assuming an alias for each resultset (AS)?
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 03/26/2012 :  13:03:41  Show Profile  Reply with Quote 


--Complaint query

SELECT 'Complaint query' AS qry_SOURCE, 
	   cfs.CFSID,
       cfs.ReceivedTime,
       ISNULL(cfs.OtherAgency, 0) AS OtherAgency,
       cfs.OccuredStart,
       cfs.OccuredEnd,
       cd.ReportCode,
       CONVERT(CHAR(15), cfs.ReceivedTime, 101) AS CallDate,
       NAME = ISNULL(pd.LastName, '') + ', ' + ISNULL(pd.FirstName, '')
FROM   CombinedDesc cd,
       CFSData cfs,
       PersonData pd
WHERE  cfs.CallTypeID = cd.ID
       AND cfs.CFSID = pd.FormID
       AND pd.FormTypeID = 10000
 
UNION ALL

--Aggravated assault query:

SELECT 'ggravated assault' AS qry_SOURCE,
	   cfs.CFSID,
       cfs.ReceivedTime,
       ISNULL(cfs.OtherAgency, 0) AS OtherAgency,
       cfs.OccuredStart,
       cfs.OccuredEnd,
       cd.ReportCode,
       CONVERT(CHAR(15), cfs.ReceivedTime, 101) AS CallDate,
       cn.Description AS CallNature,
       NAME = ISNULL(pd.LastName, '') + ', ' + ISNULL(pd.FirstName, '')
FROM   CombinedDesc cd,
       CFSData cfs,
       CombinedDesc cn,
       PersonData pd
WHERE  cfs.CallTypeID = cd.ID
       AND cfs.NatureOfCallID = cn.ID
       AND cfs.NatureOfCallID = 4367
       AND cfs.CFSID = pd.FormID
       AND pd.FormTypeID = 10000
       
UNION ALL

ect



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/
Go to Top of Page

WJHamel
Aged Yak Warrior

USA
614 Posts
Posted - 03/26/2012 :  13:14:39  Show Profile  Reply with Quote
Yep. kinda what i figured. Thanks. Also noticed that i have to make sure the number of arguments is equal in each part of the query for the unions to work. Working out nicely. Thanks again.
Go to Top of Page

WJHamel
Aged Yak Warrior

USA
614 Posts
Posted - 03/26/2012 :  13:15:47  Show Profile  Reply with Quote
Noticing also that it takes exponentially longer for the query to execute with the unions. Would indexes help that at all?
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 03/26/2012 :  13:18:37  Show Profile  Reply with Quote
It probably will be less time...if you added all the time up for each query as compared to running the all at once



In this case, you might want to use a temp table and get all the like info in 1 shot, then use then temp table in your unions



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/
Go to Top of Page

WJHamel
Aged Yak Warrior

USA
614 Posts
Posted - 03/26/2012 :  13:30:17  Show Profile  Reply with Quote
yeah, this is going to be problematic because each part of the query returns almost 60,000,000 records and in the end, i will have approximately 30 sections to this query. I wonder if it won't be more efficient to simply incorporate all of the WHERE clauses into one query.
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 03/26/2012 :  13:34:22  Show Profile  Reply with Quote
One, problem I see...


1st your JOINS are Not ANSI
2nd I don't see where you are joing all of your tables

Please change the to the format

FROM Table1 t1
INNER JOIN Table2 t2
ON t1.key = t2.key

ect

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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000