Limit Results with a sub query.

Question

User1215529056 posted

I have production hours in a list of tables to show how much time is required to do a job on the cutter.

See query below:

SELECT
OrderValue.ProductionMinutes / 60 as hours
FROM OrderValue
INNER JOIN OrderProcess ON OrderValue.JobNumber = OrderProcess.JobNumber
AND OrderValue.ComponentNumber = OrderProcess.ComponentNumber
AND OrderValue.GroupNo = OrderProcess.GroupNo
AND OrderValue.ItemNumber = OrderProcess.ItemNumber
AND OrderValue.PartNumber = OrderProcess.PartNumber
INNER JOIN OrderQtyTable ON OrderValue.JobNumber = OrderQtyTable.JobNumber
AND OrderValue.ComponentNumber = OrderQtyTable.ComponentNumber
AND OrderValue.QuantityLineNo = OrderQtyTable.QuantityLineNo
LEFT JOIN Process ON OrderProcess.ProcessCode = Process.ProcessCode
INNER JOIN OrderComponent ON OrderValue.JobNumber = OrderComponent.JobNumber
AND OrderValue.ComponentNumber = OrderComponent.ComponentNumber
AND OrderValue.QuantityLineNo = OrderComponent.QtyOrdIndex
WHERE OrderProcess.JobNumber ='23385'
AND OrderProcess.PartNumber = 0
AND OrderProcess.DontPrintOnTicket = 0
AND Process.CostCenterCode = 5000

What I want to do is to limit the results based on what was done in production.

If the cutter has completed component 1 and still has to complete component 2 I want component 2 hours to be returned

I added this in the where clause but it does not like it and I can't seem to find out why and fix it.

It underlines in red Select

AND (Select

Production.CompletionCode
from Production
join ProductionJobNumber on Production.code=ProductionJobNumber.ProductionCode
join Process pr on Production.ProcessCode=Pr.ProcessCode
where OrderProcess.JobNumber =ProductionJobNumber.jobnumber
And Production.CompletionCode<>'30'
AND Pr.CostCenterCode=5000)

Answer 1

User753101303 posted

Hi,

The subquery returns data not a boolean. I assume you want AND NOT EXISTS maybe ? See  https://www.w3schools.com/sql/sql_exists.asp