Combining two 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

SQL Server 2005 Forums

Transact-SQL (2005)

Combining two Queries

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-06-19 : 19:51:58

I need to combine two queries into one.

Query 1 (main query)

SELECT   	        dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Job.Name, dbo.Job.ChangeDate, 
            		dbo.Job.Active,
           	             sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material,
         	             sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production
FROM                    dbo.job 
             	         left outer join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid 
                         left outer join dbo.ProductionEvent on Event.EventGuid = dbo.ProductionEvent.EventGuid
                         left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
                         left outer JOIN dbo.Item ON Event.ItemGuid = dbo.Item.ItemGuid 
                         inner join dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
                         inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
                         left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid 
WHERE              dbo.Job.CompanyJobId = 3505048 
                        and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')
GROUP BY 	   dbo.Job.CompanyJobId, dbo.job.name, dbo.Job.ChangeDate, dbo.job.Name, dbo.Job.Active

Result

3505048
SR 434 T-5201 SR 434 T-5201
2007-10-11 16:36:45.647
Y
1314.26 (material qty)
1569.26 (production qty)

(where 1314.26 is sum material and 1569.26 is production)

Query 2

select	sum(EmployeeLaborEvent.Hours) as hours
from	dbo.job
		left outer join dbo.Event ON dbo.Job.JobGuid = Event.JobGuid 
		Left outer join dbo.EmployeeLaborEvent ON Event.EventGuid = dbo.Employeelaborevent.EventGuid 
WHERE   dbo.Job.CompanyJobId = 3505048

Result:

1647.50 (which are sum of hours, this figure is correct)

Now I try to merge query 2 into Query 1 like this:

SELECT   	        dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Job.Name, dbo.Job.ChangeDate, 
            		dbo.Job.Active,
           	             sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material,
         	             sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production,
                             sum(EmployeeLaborEvent.Hours) as hours
FROM               dbo.job 
             	         left outer join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid 
                         left outer join dbo.ProductionEvent on Event.EventGuid = dbo.ProductionEvent.EventGuid
                         left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
                         left outer JOIN dbo.Item ON Event.ItemGuid = dbo.Item.ItemGuid 
                         inner join dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
                         inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
                         left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid 
         
			 left outer join dbo.EmployeeLaborEvent ON Event.EventGuid = dbo.Employeelaborevent.EventGuid 
			 
WHERE              dbo.Job.CompanyJobId = 3505048 
                        and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')
GROUP BY 	   dbo.Job.CompanyJobId, dbo.job.name, dbo.Job.ChangeDate, dbo.job.Name, dbo.Job.Active

When I run the query the result is:

3505048
SR 434 T-5201 SR 434 T-5201
2007-10-11 16:36:45.647
Y
1314.26 (material)
1569.26 (production)
NULL (hours)

The material and production stay the same (and is correct). Hours are wrong.

Any clues? Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 23:56:20

[code]SELECT m.CompanyJobId,m.Name,m.ChangeDate,m.Active,m.material,m.production,t.hours
FROM
(SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Job.ChangeDate,
dbo.Job.Active,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production
FROM dbo.job
left outer join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid
left outer join dbo.ProductionEvent on Event.EventGuid = dbo.ProductionEvent.EventGuid
left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
left outer JOIN dbo.Item ON Event.ItemGuid = dbo.Item.ItemGuid
inner join dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid
WHERE dbo.Job.CompanyJobId = 3505048
and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')
GROUP BY dbo.Job.CompanyJobId, dbo.job.name, dbo.Job.ChangeDate, dbo.job.Name, dbo.Job.Active)m
INNER JOIN
(select dbo.Job.CompanyJobId ,sum(EmployeeLaborEvent.Hours) as hours
from dbo.job
left outer join dbo.Event ON dbo.Job.JobGuid = Event.JobGuid
Left outer join dbo.EmployeeLaborEvent ON Event.EventGuid = dbo.Employeelaborevent.EventGuid
WHERE dbo.Job.CompanyJobId = 3505048
GROUP BY dbo.Job.CompanyJobId
)t
ON t.CompanyJobId =m.CompanyJobId [/code]

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-06-20 : 08:11:57

Running above query, I get following erros:

Invalid object name 'dbo.Source'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.SourceType'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.Region'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.job'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.Event'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.EmployeeLaborEvent'.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-06-20 : 08:57:23

Hi Visakh16,

Disregard my previous post. It works and I get the correct result. Thank you very much. I very much appreciate your help......

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 09:41:13

quote:
Originally posted by snufse

Hi Visakh16,

Disregard my previous post. It works and I get the correct result. Thank you very much. I very much appreciate your help......

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources