Yes, you were write that joining against fees was unnecessary—good catch. Your first query did produce the same results.
Brilliant. That did it. Thanks, Steve. I always forget the trick of selecting from a selection. Ed, thanks for the recommendations on aggregate functions and group by as well. I’ve used group by but aggregate functions are new to me. I’ll look into it. On 23 Nov 2013, at 11:48, Steve Meyers <[email protected]> wrote: > 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
