Combine two queries into one - 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)

Combine two queries into one

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-06-16 : 14:33:25

I need to combine to sql queries. Separately they work fine, but I need the "total qty" from second query put into the first query

Query 1

SELECT         dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Region.CompanyRegionID, dbo.Job.Active, 
                      dbo.Job.ChangeDate
FROM            dbo.Job 
                      LEFT OUTER JOIN dbo.Division ON dbo.Job.DivisionGuid = dbo.Division.DivisionGuid 
                      LEFT OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid 
                      LEFT OUTER JOIN dbo.JobType ON dbo.Job.JobTypeGuid = dbo.JobType.JobTypeGuid
WHERE        dbo.job.CompanyJobId = 3505048
ORDER BY   dbo.Job.CompanyJobId

Query 2

SELECT         case dbo.SourceType.CompanySourceTypeId
                    when 'PR' then SUM(dbo.ProductionEvent.Quantity)
               end
               AS Ttl_Qty
FROM           dbo.Batch INNER JOIN
               dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
               dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
               dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
               dbo.Item ON dbo.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.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer join
      	       dbo.JobNoteEvent on Event.EventGuid = dbo.JobNoteEvent.EventGuid 
WHERE          dbo.Job.CompanyJobId = 3505048 and dbo.SourceType.CompanySourceTypeId = 'PR'
GROUP BY       dbo.SourceType.CompanySourceTypeId

I have tried this but it doe not work:

SELECT         dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Region.CompanyRegionID, dbo.Job.Active, 
               dbo.Job.ChangeDate, Ttl_Qty
FROM           dbo.Job 
                      LEFT OUTER JOIN dbo.Division ON dbo.Job.DivisionGuid = dbo.Division.DivisionGuid 
                      LEFT OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid 
                      LEFT OUTER JOIN dbo.JobType ON dbo.Job.JobTypeGuid = dbo.JobType.JobTypeGuid
WHERE         dbo.job.CompanyJobId = 3505048 and where Ttl_Qty = 

(SELECT         case dbo.SourceType.CompanySourceTypeId
                    when 'PR' then SUM(dbo.ProductionEvent.Quantity)
               end
               AS Ttl_Qty
FROM           dbo.Batch INNER JOIN
               dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
               dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
               dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
               dbo.Item ON dbo.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.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer join
      	       dbo.JobNoteEvent on Event.EventGuid = dbo.JobNoteEvent.EventGuid 
WHERE          dbo.Job.CompanyJobId = 3505048 and dbo.SourceType.CompanySourceTypeId = 'PR'
GROUP BY       dbo.SourceType.CompanySourceTypeId)

ORDER BY   dbo.Job.CompanyJobId

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 14:35:29

Whats the connecting field in between both the queries?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 14:40:24

[code]SELECT j.CompanyJobId, j.Name, r.CompanyRegionID, j.Active,
j.ChangeDate,COALESCE(b.Ttl_Qty,0)
FROM dbo.Job j
LEFT OUTER JOIN dbo.Division d ON j.DivisionGuid = d.DivisionGuid
LEFT OUTER JOIN dbo.Region r ON j.RegionGuid = r.RegionGuid
LEFT OUTER JOIN dbo.JobType jt ON j.JobTypeGuid = jt.JobTypeGuid
OUTER APPLY (
SELECT case dbo.SourceType.CompanySourceTypeId
when 'PR' then SUM(dbo.ProductionEvent.Quantity)
end
AS Ttl_Qty
FROM dbo.Batch INNER JOIN
dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
dbo.Item ON dbo.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.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer join
dbo.JobNoteEvent on Event.EventGuid = dbo.JobNoteEvent.EventGuid
WHERE dbo.Job.CompanyJobId = j.CompanyJobId
and dbo.SourceType.CompanySourceTypeId = 'PR'
GROUP BY dbo.SourceType.CompanySourceTypeId
)b
WHERE j.CompanyJobId = 3505048
ORDER BY j.CompanyJobId[/code]

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-06-16 : 14:59:14

Getting syntax eror:

Server: Msg 170, Level 15, State 1, Line 24
Line 24: Incorrect syntax near 'b'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-17 : 00:56:25

quote:
Originally posted by snufse

Getting syntax eror:

Server: Msg 170, Level 15, State 1, Line 24
Line 24: Incorrect syntax near 'b'.

I'm not getting any syntax error. Are you sure you ran the whole query?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources