I am trying to write a query that will pull records from a database and cannot
get it to work.
Here’s what I need:
All members that have paid their dues for this year and it is the first year
they have paid dues.
In this system, the year is an entry in the database so I don’t have to deal
with dates in my query, just a primary key. Also, it’s important to note that
users are not required to pay amounts due in their entirely in a single
payment, so I have to sum payments and verify that they are at least equal to
or greater than the amount due to establish if the dues for the year have been
paid.
There are several tables involved. I have:
members
(each member)
fees
(there is an entry in this table for each year’s annual dues)
members_fees
(there is an entry in this table for each member that owes annuals dues)
members_charges
(there is an entry in this table for each amount a member owes—it exists
because there are other types of things to pay for besides fees)
members_payments
(there is an entry in this table for each payment that a member makes—again,
there can be multiple payments for a single charge)
Retrieving a list of all members that have paid this year’s annual dues
completely is easy:
SELECT memb.memb_id, memb.memb_lname, memb.memb_fname, mech.mech_amount,
(SELECT SUM(mepa.mepa_amount)
FROM members_payments mepa
WHERE mepa.mepa_mech_id=mech.mech_id) as paid
FROM members memb
INNER JOIN members_charges mech
ON memb.memb_id=mech.mech_memb_id
INNER JOIN members_fees mefe
ON mech.mech_fk=mefe.mefe_id AND mech.mech_fk_type=1
INNER JOIN fees fees
ON mefe.mefe_fees_id=fees.fees_id
WHERE memb.memb_status=1 AND fees.fees_id=20
HAVING paid >= mech.mech_amount
ORDER BY memb.memb_id
I thought that I should be able to then exclude members that have paid previous
years dues in full like this:
SELECT memb.memb_id, memb.memb_lname, memb.memb_fname, mech.mech_amount,
(SELECT SUM(mepa.mepa_amount)
FROM members_payments mepa
WHERE mepa.mepa_mech_id=mech.mech_id) as amount_paid_this,
(SELECT SUM(mepa.mepa_amount)
FROM members_payments mepa
WHERE mepa.mepa_mech_id<>mech.mech_id) as amount_paid_earlier
FROM members memb
INNER JOIN members_charges mech
ON memb.memb_id=mech.mech_memb_id
INNER JOIN members_fees mefe
ON mech.mech_fk=mefe.mefe_id AND mech.mech_fk_type=1
INNER JOIN fees fees
ON mefe.mefe_fees_id=fees.fees_id
WHERE memb.memb_status=1 AND fees.fees_id=20
HAVING amount_paid_this >= mech.mech_amount AND amount_paid_earlier = 0
ORDER BY memb.memb_id
…but that query runs and runs and runs and eventually returns zero results.
Any ideas for me?
_______________________________________________
UPHPU mailing list
[email protected]
http://uphpu.org/mailman/listinfo/uphpu
IRC: #uphpu on irc.freenode.net
