CODEWITHSUNDEEP
mysqlsqljoin
user42479
user42479

Reputation: 17

SQL query to compare one column with another

This is for a small project I'm doing. I am trying to write a query which can do this on the following table

ID, Name, Pledge_Amount , Pledge_payment_amt , Gift_No, Pledge_Gift_No, Pledge_Open/Closed

ID isn't unique. So, one person would have pledged some money (Pledge_Amount), would have paid the full or part of it (Pledge_payment_amt) with multiple records for the same. The Gift_no will be be the same for the same pledge/gift and will match with the Pledge_Gift_No whenever the person decides to donate. Only when the gift has been completely paid off, the status will change.

1, Alex, 20, 10, G1, NULL, Open

1, Alex, NULL,10, NULL, G1, Closed

And the query is:

Find the persons with outstanding gift amounts and change their status to open/closed

So far, I have been able to do this:

select *
from Test_Table2 S inner join Test_Table2 F
on S.Gift_No = F.Pledge_gift_No
WHERE S.ID = F.ID;

Any pointers on how to go about with the rest of the query will be greatly appreciated.

Upvotes: 0

Views: 2423

Answers (1)

Marc B
Marc B

Reputation: 360842

Something like this?

SELECT S.*, SUM(Pledge_payment_amt) AS pledged
FROM Test_table2 AS recipients
LEFT JOIN Test_table2 AS pledgers
    ON recipients.Gift_no = pledgers.Pledge_gift_no
GROUP BY S.Gift_No
HAVING S.Pledge_Amount > pledged

This would return all the "recpients" whose required pledge_amount is greater than the amount already pledged - e.g. they haven't met their pledge goal yet.

Upvotes: 2

Related Questions