how to use union all - 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

SQL Server 2005 Forums

Transact-SQL (2005)

how to use union all

Author

Topic

R.Prabu
Starting Member

33 Posts

Posted - 2008-10-18 : 11:31:00

Hai This my query it display thes same records two times

Select PM.PropertyId,PM.OwnerId,
CC.MRNumber As 'RequestCode',

ISNULL(MR.SafetyAnnualReview,0) As 'SafetyAnnualReview',
ISNULL(MR.Insurance,0) As 'Insurance',
ISNULL(MR.CPAFee,0) As 'CPAFee',
ISNULL(MR.AgentFee,0) As 'AgentFee',
0 As 'Labour',0 As 'Parts', CC.JobStatus AS 'Status',
'Jobstatus' = CASE WHEN CC.JobStatus = 0 THEN 'New jobs' WHEN CC.JobStatus = 1 THEN 'Actioned'
WHEN CC.JobStatus = 2 THEN 'Awaiting for the cost' WHEN CC.JobStatus = 3 THEN 'Completed and costed'
End,
MR.RequestType

from HSSPMS_Tbl_PropertyMaster PM
Inner join HSSPMS_Tbl_Callcentre_Compliants CC
On PM.PropertyId=CC.PropertyId
INNER Join HSSPMS_Tbl_MaintenanceRequest MR On MR.Property_Id=PM.PropertyId And Year(MR.CreatedDate)=2008
Where PM.PropertyId= 'PMS0810113' And PM.OwnerId='LA1'

Union All

SELECT PM.PropertyId,PM.OwnerId,
CC.MRNumber As 'RequestCode',
0 As 'SafetyAnnualReview',
0 As 'Insurance',
0 As 'CPAFee',
0 As 'AgentFee',
SUM(ISNULL(MC.Labour,0)) As 'Labour', SUM(ISNULL(MC.Parts,0)) As 'Parts', CC.JobStatus AS 'Status',
'Jobstatus' = CASE WHEN CC.JobStatus = 0 THEN 'New jobs' WHEN CC.JobStatus = 1 THEN 'Actioned'
WHEN CC.JobStatus = 2 THEN 'Awaiting for the cost' WHEN CC.JobStatus = 3 THEN 'Completed and costed'
End,
CC.TypeOfRequest

FROM HSSPMS_Tbl_PropertyMaster PM
Inner join HSSPMS_Tbl_Callcentre_Compliants CC
On PM.PropertyId=CC.PropertyId
Inner Join HSSPMS_TblMaintenanceCostDetails MC On MC.Property_Id=PM.PropertyId And MC.ForYear=2008

Where CC.PropertyId='PMS0810113' And PM.OwnerId='LA1'

GROUP BY CC.MRNumber,CC.JobStatus,PM.PropertyId,PM.OwnerId,
CC.TypeOfRequest, MC.Labour

any one can help me

Regards,
Prabu R

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-18 : 11:48:09

You haven't provided enough information for us to help you. First of all, if you run the queries without the union, do they produce the same data? The answer is yes, so you need to figure out why. We can't answer that part for you without more extensive information.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ursangel
Starting Member

17 Posts

Posted - 2008-10-29 : 11:39:56

use union instead of union all cos , Union will filter duplicate values where as union all will not filter duplicate values

Regards
Angel

ursangel
Starting Member

17 Posts

Posted - 2008-10-29 : 11:40:15

use union instead of union all cos , Union will filter duplicate values where as union all will not filter duplicate values

Regards
Angel

ursangel
Starting Member

17 Posts

Posted - 2008-10-29 : 11:40:56

use Union instead of Unionall cos, Union will filter duplicate values where as union all will not filter duplicate values

Regards
Angel

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-10-29 : 11:46:25

moved to appropriate forum.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources