|
Subject:
|
Query does not produce desired result
|
|
Posted By:
|
snufse
|
Post Date:
|
7/2/2008 12:14:39 PM
|
I have a query that selects job production data (see code below). The query works except for that I am not able to extract existing "notes" for a specific job.
SELECT dbo.event.eventdate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId,
dbo.JobNoteEvent.Note, dbo.productionevent.quantity
FROM dbo.Batch
inner join dbo.event on dbo.event.batchguid = dbo.batch.batchguid
inner join dbo.job on dbo.job.jobguid = dbo.event.jobguid
inner join dbo.item on dbo.item.itemguid = dbo.event.itemguid
inner join dbo.productionevent on dbo.productionevent.eventguid = dbo.event.eventguid
left join dbo.JobNoteEvent on dbo.JobNoteEvent.EventGuid = dbo.event.eventguid
WHERE dbo.Job.CompanyJobId = 3505048 and dbo.Item.CompanyItemId = '05001'
ORDER BY dbo.event.eventdat
Result:
EventDate = 2008-04-07
CompanyJobId = 3505048
CompanyItemId = 05001
Notes = Null
Quantity = 276.78
Now, I if comment out lines that extarcts the quantity like this:
SELECT dbo.event.eventdate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId,
dbo.JobNoteEvent.Note --, dbo.productionevent.quantity
FROM dbo.Batch
inner join dbo.event on dbo.event.batchguid = dbo.batch.batchguid
inner join dbo.job on dbo.job.jobguid = dbo.event.jobguid
inner join dbo.item on dbo.item.itemguid = dbo.event.itemguid
--inner join dbo.productionevent on dbo.productionevent.eventguid = dbo.event.eventguid
left join dbo.JobNoteEvent on dbo.JobNoteEvent.EventGuid = dbo.event.eventguid
WHERE dbo.Job.CompanyJobId = 3505048 and dbo.Item.CompanyItemId = '05001'
ORDER BY dbo.event.eventdate
Then I get the notes like below
EventDate = 2008-04-07
CompanyJobId = 3505048
CompanyItemId = 05001
Notes = LTL 1/1 7484-7915
Now, If I do this:
SELECT i.eventdate, i.companyjobid, i.companyitemid, n.note, i.quantity
FROM
(SELECT dbo.event.eventdate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId,
dbo.productionevent.quantity
FROM dbo.Batch
inner join dbo.event on dbo.event.batchguid = dbo.batch.batchguid
inner join dbo.job on dbo.job.jobguid = dbo.event.jobguid
inner join dbo.item on dbo.item.itemguid = dbo.event.itemguid
inner join dbo.productionevent on dbo.productionevent.eventguid = dbo.event.eventguid
WHERE dbo.Job.CompanyJobId = 3505048 and dbo.Item.CompanyItemId = '05001' and dbo.productionevent.quantity > 0
GROUP BY dbo.event.eventdate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId, dbo.productionevent.quantity)i
Inner JOIN
(SELECT dbo.Job.CompanyJobId, dbo.Item.CompanyItemId, dbo.JobNoteEvent.Note
FROM dbo.Batch
inner join dbo.event on dbo.event.batchguid = dbo.batch.batchguid
inner join dbo.job on dbo.job.jobguid = dbo.event.jobguid
inner join dbo.item on dbo.item.itemguid = dbo.event.itemguid
left join dbo.JobNoteEvent on dbo.JobNoteEvent.EventGuid = dbo.event.eventguid
WHERE dbo.Job.CompanyJobId = 3505048 and dbo.Item.CompanyItemId = '05001'
GROUP BY dbo.event.eventdate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId, dbo.JobNoteEvent.Note)n
ON n.CompanyJobId = i.CompanyJobId
Result: I get 2 records (I just want the record that have both qty and notes)
EventDate = 2008-04-07
CompanyJobId = 3505048
CompanyItemId = 05001
Notes = Null
Quantity = 276.78
EventDate = 2008-04-07
CompanyJobId = 3505048
CompanyItemId = 05001
Notes = LTL 1/1 7484-7915
Quantity = 276.78
|
|
