Join tables A and B - Get sum(X) from table B 1

[southbeach]

I have two tables and the following query command:

SELECT a.*, b.*, SUM(pdQtyOnh) as qty FROM quickwr a, productsd b WHERE a.wrSysID = b.pdSysID AND a.wrAccountOfID = 3 AND a.wrDate >= "01/01/2000" AND a.wrDate <= "05/20/2008" AND a.wrStatus != "Q" AND EXISTS (SELECT * FROM productsd WHERE pdQtyOnh > 0 AND pdSysID = quickwr.wrSysID) AND a.wrBranch = "MIA"

I am getting the error

Unknown column 'quickwr.wrSysID' in 'where clause'

I can assure you that the columns exist. What am I doing wrong?

Thank you all in advance for your assistance!

 

[ingresman]

I think you might need to name quickwr in the from in the subselect i.e.

(SELECT * FROM productsd, quickwr WHERE pdQtyOnh > 0 AND pdSysID = quickwr.wrSysID)

 

[southbeach]

You mean to use quickwr.wrSysID in lieu of a.wrSysID?

 

[DonQuichote]

From your query, I think you mean a.wrSysID. That is one of the fields you use in the outer query, because you have used aliases for the tables. So, seen from the inner query, there is no field quickwr.wrSysID, as this field is not producsd and also not in the field list "imported" from the outer query.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[r937]

i have a real problem understanding your query

first of all, you have a SUM() in the outer query SELECT, but you're missing the GROUP BY clause

you want to produce totals for what level of aggregation?

from the title of the thread, i assume quickwr is A and productsd is B, and you want to sum something out of productsd...

so b.* doesn't belong in the SELECT

and why the EXISTS subquery? what it actually says is that you want to produce the sum only if there's at least one positive pdQtyOnh -- so, basically, any number of negative or zero pdQtyOnh values is fine, as long as there's at least one positive one, then the sum is acceptable?

weird

r937.com | rudy.ca

 

[southbeach]

DonQuichote, thank you for pointing that out ... can't believe I missed it ... I spent a long while looking at the query command and did not see it.

r937, this is for an inventory summary on-hand report. I am using header/detail format and the EXISTS is simply my way to ensure that ONLY items reflecting at least one child with 1 or more pieces on hand.

No negative allowed! My application does not allow negative balances as on-hand.

Thank you both for your comments and assistance!