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
