updating table 1 where table 2 condition? & join

["Richard Creech - DreamRiver.com" <richardc@xxxxxxxxxxxxxx>]

Hello All,
Two items:

1. UPDATING TABLE 1 WHERE TABLE 2 CONDITION
Is it still true I cannot update table 1 on a table 2 field condition?
example: UPDATE member SET member.acluser='6' where payment.memberexpires < '2001-03-09'

... big sigh ...


2. JOIN SYNTAX
I need help with a specific join syntax. I have two tables with a one to many relationship. Of the "many", I only want the most recent record. I need the sql statement which will return only the most recent records in this instance:

SELECT * FROM member, payment where member.memberid=payment.paymentid ORDER BY payment.number DESC

... except this query will select all payment records and I only want to select the records where payment.number (an autoincrement field) is the highest value for the member. Basically I want to find out if the member has an expired membership without selecting all the payment records. Only the most recent payment.number is wanted.


Thank you for any assistance you can render.

BTW, my users very much enjoy the speed and reliability of mysql. Hundreds of developers download phpYellow Pages every week for this reason. There is growing acceptance of mySQL as a viable database alternative and I am happy to see this. Keep up the good work.

Kind Regards,

Richard Creech
richardc@xxxxxxxxxxxxxx
250.744.3350 Pacific Time Canada
Create your own Yellow Pages Directory with phpYellow Pages
Now Shipping a Premium Version with an Advanced Search module!
http://www.dreamriver.com/phpYellow/

phpEasySQL - connect with your mySQL database using a browser and one php file - free
http://www.dreamriver.com/software/








---------------------------------------------------------------------
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 <mysql-thread67741@xxxxxxxxxxxxxxx>
To unsubscribe, e-mail <mysql-unsubscribe-treed=ultraviolet.org@xxxxxxxxxxxxxxx>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php