Query does not produce desired result

snufse · July 2nd, 2008, 12:14 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.

Code:

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 <s>--, dbo.productionevent.quantity </s>
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
        <s>--inner join dbo.productionevent on dbo.productionevent.eventguid = dbo.event.eventguid </s>
                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:

Code:

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