Combine 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)

Combine two queries

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-06-30 : 13:56:54

Need to combine two queries into one.

Query 1

SELECT           dbo.Item.CompanyItemId, dbo.Item.Name, dbo.ProductionEvent.Quantity 
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
WHERE            dbo.job.CompanyJobId = 3505048  and dbo.ProductionEvent.Quantity > 0 and dbo.SourceType.CompanySourceTypeId = 'PR' 
GROUP BY         dbo.Item.CompanyItemId, dbo.Item.Name, dbo.ProductionEvent.Quantity

Query 2

SELECT                dbo.Item.CompanyItemId, dbo.JobNoteEvent.Note
FROM                  dbo.Batch 
                     	  INNER JOIN dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid 
                    	  inner join dbo.JobNoteEvent ON dbo.Event.EventGuid = dbo.JobNoteEvent.EventGuid 
                    	  INNER JOIN dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid 
                          INNER JOIN dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid
WHERE                dbo.job.CompanyJobId = 3505048  
GROUP BY             dbo.Item.CompanyItemId, dbo.JobNoteEvent.Note

Combined Query

SELECT           i.CompanyItemId, i.Name, i.Quantity, n.Note, 
(SELECT          dbo.Item.CompanyItemId, dbo.Item.ItemName 
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
WHERE                  dbo.job.CompanyJobId = 3505048  and dbo.ProductionEvent.Quantity > 0 and dbo.SourceType.CompanySourceTypeId = 'PR' 
GROUP BY               dbo.Item.CompanyItemId, dbo.Item.Name)i
INNER JOIN
(SELECT          dbo.Item.CompanyItemId,  dbo.JobNoteEvent.Note
FROM             dbo.Batch 
                       INNER JOIN dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid 
                       INNER join dbo.JobNoteEvent ON dbo.Event.EventGuid = dbo.JobNoteEvent.EventGuid 
                       INNER JOIN dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid 
                       INNER JOIN dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid
WHERE                  dbo.job.CompanyJobId = 3505048 
GROUP BY               dbo.Item.CompanyItemId, dbo.JobNoteEvent.Note)n
ON n.CompanyItemId = i.CompanyItemId

Syntax errors

Server: Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'INNER'.
Server: Msg 170, Level 15, State 1, Line 20
Line 20: Incorrect syntax near 'n'.

karuna
Aged Yak Warrior

582 Posts

Posted - 2008-06-30 : 14:42:29

[code]
SELECT i.CompanyItemId, i.Name, i.Quantity, ~~n.Note,~~ n.Note FROM
(
SELECT dbo.Item.CompanyItemId, dbo.Item.ItemName
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
WHERE dbo.job.CompanyJobId = 3505048 and dbo.ProductionEvent.Quantity > 0 and dbo.SourceType.CompanySourceTypeId = 'PR'
GROUP BY dbo.Item.CompanyItemId, dbo.Item.Name
) i
INNER JOIN
(
SELECT dbo.Item.CompanyItemId, dbo.JobNoteEvent.Note
FROM dbo.Batch
INNER JOIN dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid
INNER join dbo.JobNoteEvent ON dbo.Event.EventGuid = dbo.JobNoteEvent.EventGuid
INNER JOIN dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid
INNER JOIN dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid
WHERE dbo.job.CompanyJobId = 3505048
GROUP BY dbo.Item.CompanyItemId, dbo.JobNoteEvent.Note
)n
ON n.CompanyItemId = i.CompanyItemId
[/code]

Thanks
Karunakaran

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-06-30 : 15:06:22

Thank you, getting very close. The only issue I now have is one field: i.Quantity -> dbo.ProductionEvent.Quantity. This field comes from a table different from 'i'. How can I solve this?

SELECT           i.CompanyItemId, i.Name, i.Quantity, n.Note FROM
(
	SELECT          dbo.Item.CompanyItemId, dbo.Item.Name 
	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
	WHERE                  dbo.job.CompanyJobId = 3505048  and dbo.ProductionEvent.Quantity > 0 and dbo.SourceType.CompanySourceTypeId = 'PR' 
	GROUP BY               dbo.Item.CompanyItemId, dbo.Item.Name
) i
INNER JOIN
(
	SELECT          dbo.Item.CompanyItemId,  dbo.JobNoteEvent.Note
	FROM             dbo.Batch 
						   INNER JOIN dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid 
						   INNER join dbo.JobNoteEvent ON dbo.Event.EventGuid = dbo.JobNoteEvent.EventGuid 
						   INNER JOIN dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid 
						   INNER JOIN dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid
	WHERE                  dbo.job.CompanyJobId = 3505048 
	GROUP BY               dbo.Item.CompanyItemId, dbo.JobNoteEvent.Note
)n
ON n.CompanyItemId = i.CompanyItemId

Error:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Quantity'.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-30 : 15:27:38

Use like this:

SELECT i.CompanyItemId, i.Name, i.Quantity, n.Note FROM
(
SELECT dbo.Item.CompanyItemId,
dbo.Item.Name , dbo.ProductionEvent.Quantity
FROM dbo.Batch Inner join .........

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-06-30 : 15:34:23

Great, perfect, works fantastic ... thank you all ... saved my day ....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources