Subtracting value from parent table with SUM(value from child table)

Question

I have 2 tables, tblBasicInfo and tblPayment.

Relationship is 1 to many, where tblBasicInfo is on the 1 side, and tblPayment is on the many side.

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 tblBasicInfo:

ID | TotalPrice

 1 | 100

 2 | 150

 3 | 200

 4 | 250

Here is the content for child table tblPayment:

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.

Answer 1

I tried this on SQL Server, but you can achieve same in other RDMS you can achieve this in probably more than one way here I presented two solutions I found that first solution performs better than second

SELECT ti.id,MAX(totalprice) - ISNULL(SUM(CASE WHEN is_payed = ((0)) THEN 0 ELSE amount END),0) amount
FROM tblbasicinfo ti LEFT OUTER JOIN  tblpayment tp ON ti.id = tp.p_id
GROUP BY ti.id 

--OR 

SELECT id,totalprice-ISNULL((SELECT SUM(amount) 
FROM tblpayment tp 
WHERE  ti.id = tp.p_id AND is_payed = ((1))
GROUP BY id),0) AS reconsile
 FROM tblbasicinfo ti 

CREATE TABLE tblBasicInfo (id INT IDENTITY(1,1),totalprice MONEY)

CREATE TABLE tblPayment (id INT IDENTITY(1,1), P_ID INT ,is_payed BIT,amount MONEY)

INSERT INTO tblbasicinfo

VALUES(100),(150),(200),(250)

INSERT INTO tblpayment(p_id,is_payed,amount)
VALUES(1,((1)),50),(1,((0)),25),(2,((0)),100),(2,((0)),25),(3,((1)),200)