Hi, You need to self-join payhistory along the lines of
select p1.* from payhistory p1, payhistory.p2 where p1.payid = p2.payid and p1.paydate = max(p2.paydate) Something like that, anyway - I know I had to fiddle around a bit to get a similar think going. Hope this help Quentin -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, 1 November 2001 4:22 p.m. To: [EMAIL PROTECTED] Subject: Need query help Hello all, I have 3 tables I need to join to extract certain data base on the acct_days in the active table '6' days. QUERY: SELECT CONCAT(m.fname,' ',m.lname) AS name, m.email,m.zip,p.paytype,p.event,p.paydate FROM members m LEFT JOIN payhistory p ON p.memid = m.memid LEFT JOIN active a ON a.memid = m.memid WHERE a.acct_days = '6' GROUP BY p.memid ORDER BY p.paydate DESC QUERY RESULTS: +-----------------+-----------------------+-------+---------+--------------- +------------+ | name | email | zip | paytype | event | paydate | +-----------------+-----------------------+-------+---------+--------------- +------------+ | Charge Schwartz | [EMAIL PROTECTED] | 33308 | Charge | Quarterly New | 2001-10-30 | | Check Schwartz | [EMAIL PROTECTED] | 33308 | Check | Quarterly New | 2001-10-30 | +-----------------+-----------------------+-------+---------+--------------- +------------+ But what I need it to do is pull the most recent date and related payhistory data in those columns that match the memid from the members table that only has the 6 days left on their account. RESULTS DESIRED: +-----------------+-----------------------+-------+---------+--------------- +------------+ | name | email | zip | paytype | event | paydate | +-----------------+-----------------------+-------+---------+--------------- +------------+ | Charge Schwartz | [EMAIL PROTECTED] | 33308 | Check | Quarterly New | 2001-10-31 | | Check Schwartz | [EMAIL PROTECTED] | 33308 | Charge | Quarterly New | 2001-10-31 | +-----------------+-----------------------+-------+---------+--------------- +------------+ Below are snips for the tables being joined. Any help would be much appreciated. Been at this for almost good part of the day! :) TIA ----------------------------------------------------------------- active table +-------+-----------+ | memid | acct_days | +-------+-----------+ | 21 | 6 | | 22 | 6 | +-------+-----------+ payhistory +-------+-------+------------------------+------------+---------+ | payid | memid | event | paydate | paytype | +-------+-------+------------------------+------------+---------+ | 83 | 21 | Quarterly New | 2001-10-30 | Charge | | 84 | 22 | Quarterly New | 2001-10-30 | Check | | 85 | 21 | Quarterly Renew | 2001-10-31 | Check | | 86 | 22 | Quarterly Renew | 2001-10-31 | Charge | +-------+-------+------------------------+------------+---------+ members +-------+-----------------+-----------------------+-------+ | memid | name | email | zip | +-------+-----------------+-----------------------+-------+ | 21 | Charge Schwartz | [EMAIL PROTECTED] | 33308 | | 22 | Check Schwartz | [EMAIL PROTECTED] | 33308 | +-------+-----------------+-----------------------+-------+ mysql database Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
