I concur with the previous two queries and recommend you read up on aggregate functions and group by (as I saw no group by in your query). Looks like you are doing OK with joins but I recommend making sure you put the smallest table up to the largest last for performance. :) On Nov 23, 2013 11:49 AM, "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 _______________________________________________ UPHPU mailing list [email protected] http://uphpu.org/mailman/listinfo/uphpu IRC: #uphpu on irc.freenode.net
