I have 2 tables,
Relationship is 1 to many, where
tblBasicInfo is on the 1 side, and
tblPayment is on the
Relationship is optional and that is the problem.
I need to subtract value of certain field from parent table with sum of certain fields from child table that match certain criteria.
If there are no records in child table that fulfill the criteria then this should be represented with zero (
data from parent table - 0 ).
I apologize if this is not crystal clear, English is not my native and I am not experienced enough to know how to properly describe the problem.
It would be best to demonstrate what I mean with a small example:
We shall start from table schema:
tblBasicInfo: #ID, TotalPrice (double) tblPayment: #P_ID, $ID, Amount (double), IsPaid (bool)
Here is the content for parent table
ID | TotalPrice 1 | 100 2 | 150 3 | 200 4 | 250
Here is the content for child table
P_ID | ID | IsPaid | Amount 1 | 1 | true | 50 2 | 1 | false | 25 3 | 2 | false | 100 4 | 2 | false | 25 5 | 3 | true | 200
This is what I have accomplished on my own:
SELECT tblBasicInfo.ID, ( tblBasicInfo.TotalPrice - sum(tblPayment.Amount) ) AS [Difference] FROM tblBasicInfo, tblPayment WHERE ( tblBasicInfo.ID = tblPayment.ID ) GROUP BY tblBasicInfo.TotalPrice, tblPayment.IsPaid HAVING ( tblPayment.IsPaid = TRUE ) --this is the criteria I talked above ORDER BY tblBasicInfo.ID;
This is what I get from the above query:
ID | Difference 1 | 50 3 | 0 . . .
I need to get the following result:
ID | Difference 1 | 50 2 | 150 -- does not meet the criteria ( IsPayed = false ) 3 | 0 4 | 250 -- no records in child table . . .
I apologize for imperfect title of the question, but I really did not know how to describe this problem.