Query/Subquery combo returning incorrect results

[April15Hater]

Hello everyone,

What I'm trying to do is get my query to return the ProductionID for matching ContractorFunctionID and ProductionUnit amounts. The ProductionUnits are assigned a ContractorFunctionID which describes what the units are for. There is a M2M relationship between tblContractorFunction and tblProductionInput, therefore there can be more than one ContractorFunctionID assigned to a particular Production ID.

I can get it to show the correct ProductionID when each of the subqueries are in solitude or there is an "or" between them rather than an "and". That would solve the problem except that if I substitute a ProductionUnit amount assigned to a different ProductionID yet the same ContractorFunctionID, it will return both ProductionID numbers when it should return nothing. Any ideas?

Here is my table structure:

tblProductionInputDetail
-ProductionInputDetailID
-ContractorFunctionID
-ProductionID
-ProductionUnits

tblProductionInput
-ProductionID
-CompleteDate
-ContractorID
-FunctionID

tblContractorFunction
-ContractorFunctionID
-FunctionID
-FunctionType
-FunctionCost

and my code...

SELECT  tblProductionInputDetail.ProductionID
FROM tblProductionInput INNER JOIN tblProductionInputDetail ON tblProductionInput.ProductionID = tblProductionInputDetail.ProductionID
WHERE FunctionID = 58 AND 

(SELECT ProductionID 
FROM tblProductionInputDetail as tblProductionInputDetail1 
WHERE tblProductionInputDetail.ProductionID = tblProductionInputDetail1.ProductionID AND 
tblProductionInputDetail.ProductionUnits = 830 AND 
ContractorFunctionID = 157) AND

(SELECT ProductionID 
FROM tblProductionInputDetail as tblProductionInputDetail1 
WHERE tblProductionInputDetail.ProductionID = tblProductionInputDetail1.ProductionID AND 
tblProductionInputDetail.ProductionUnits = 512 AND 
ContractorFunctionID = 158)

Thanks!

Joe

 

[Banana]

The SQL doesn't seem to be correct.

You can't do a AND between a query and subquery.

Are you trying to do a UNION query so you can display three queries combined? If so, replace the AND between the subquery with UNION to do that.

HTH.

 

[April15Hater]

Hi Banana,

Aren't you proud of me? I took your advice and rolled up my sleeves and jumped right into subqueries!

The UNION doesn't do it though. It prompts me to enter a value for ProductionID.

I want it to return just one ProductionID when the subqueries return the same ProductionID. So in the example, it is going to search tblProductionInputDetail for a ContractorFunctionID of 157 that has ProductionUnits of 830, and return a Production ID (in this case it is 468). Similarly, it is going to search the same table for a ContractorFunctionID of 158 that has ProductionUnits of 512 (again 468). I only want it to return a ProductionID if the ProductionID in both of the subs are identical.

Thanks!

Joe

 

[Banana]

Gotcha.

What you need to do is to make sure your WHERE criteria does something like this:

WHERE foo IN (SELECT foo IN bar WHERE iter = 1) AND foo IN (SELECT foo in baz WHERE iter = 1)

This should accomplish what you're trying to do here, (if I understood you rightly!).

And btw, looks like you're getting hang of SQL. Keep it up!

 

[April15Hater]

Whoa, I blew a mind fuse just by reading that, lol! What is foo, bar, iter and baz, and what is that doing? Also does it need to be in the SubQuery or the Query?

Thanks!

Joe

 

[Banana]

foo, bar, iter, and baz are just placeholder for example's sake.

This would go in the main query's WHERE clause. Note that IN()s reference a subquery, which is where you would put your subquery- inside the IN().

Therefore, foo would = ProductionID for your case, while bar is the table where you need to look (ProductionDetail?) and iter is the critera you want to check (ProductionUnit?).

Did that make sense now?

 

[April15Hater]

I gotcha. You were right, hangin out around here you pick up SQL pretty fast. Fortunately, I haven't stumped myself too bad lately, but this one certainly got me.

As for my prob, I had to change IN to FROM in the subquery and it's working beautifully. I like that IN clause, it's pretty convenient. Thanks again!

Joe

 

[Banana]

Gee whiz, what was I thinking? I must have been on a IN binge when I wrote that SQL!

Glad you figured it on your own; you're doing great!!!