Okay, first of all, you don't need the fees table to be joined, as the
only information you use from it is also contained in the member_fees
table. I'm assuming that the fees_id is the per year identifier, since
that seems to be what it must be. I've rewritten your original query as
follows:
SELECT
memb.memb_id,
memb.memb_lname,
memb.memb_fname,
mech.mech_amount,
SUM(mepa.mepa_amount) 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
LEFT JOIN members_payments mepa ON mepa.mepa_mech_id=mech.mech_id
WHERE
memb.memb_status=1
AND mefe.mefe_fees_id=20
AND mech.mech_fk_type=1
GROUP BY
memb.memb_id
HAVING
paid >= mech.mech_amount
Now to make it find only people for whom this is the first year. What
I'll do is modify the query to select and group by all fees_id values,
and then do an outer query group by on that result to find ones for whom
20 is the lowest fees_id.
SELECT
memb_id,
memb_lname,
memb_fname,
MIN(mefe_fees_id) AS first_fees_id
FROM
(
SELECT
memb.memb_id,
mefe.mefe_fees_id,
memb.memb_lname,
memb.memb_fname,
mech.mech_amount,
SUM(mepa.mepa_amount) 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
LEFT JOIN members_payments mepa ON
mepa.mepa_mech_id=mech.mech_id
WHERE
memb.memb_status=1
AND mech.mech_fk_type=1
GROUP BY
memb.memb_id,
mefe.mefe_fees_id
HAVING
paid >= mech.mech_amount
) res
GROUP BY
memb_id
HAVING
first_fees_id=20
Without the database, I can't verify that it works, but it works in my
mind. :)
On 11/23/13 10:41 AM, Wade Shearer wrote:
> 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
>
_______________________________________________
UPHPU mailing list
[email protected]
http://uphpu.org/mailman/listinfo/uphpu
IRC: #uphpu on irc.freenode.net
