Petre Agenbag <[EMAIL PROTECTED]> wrote: > > I think I'm having a very off day and need some confirmations on how > MySQL works with it's result sets. > > I have a couple of relational tables , the first holding the person's > name and address for instance, and the other tables holds comments and > complaints respectively. Each table has it's own id field, as well as a > "master_id" that equals the id of the "main" table. > > The app that I'm writing (in PHP), lists the users with a simple "select > * from main" , and this returns all the users currently on the system ( > I have names as varchar and unique). > > My problem is now with the following: > > When the user clicks on one of the names, I want to do a couple of > things: > > a) The user details be displayed along with all the comments and > complaints that correspond to that users id located in the other tables. > pseudo SQL -> "select * from comments where (place_holder id in > comments) = (id in main table)"; > b) Being able to list the comments and complaints in reverse order ie, > older ones first: > pseudo SQL -> "select * from comments where (place_holder id) = (id in > main table) order by id desc"; > c) List ONLY the last (newest) comments/complaints > THIS IS WHERE I have problems: > > If I do a "select MAX(id), comment from comments where (place_holder id) > = (id in main table)" will MySQL automagically grab the comment from the > row that has the maximum ID? If so, is there a shorter way of doing this > query? For my example here, it's not a big deal, but with larger tables > with more collumns, having to specify the collumns in the query ( when I > want ALL to be returned) becomes a bit of a hassle.. >
Nope. MySQL will return fist found comment. > I basically want to say: > > return ONLY the last comment added where the id matches the supplied id > from main_table. > > So, I need to Translate this to SQL... > > Can I do this with SQL, or must I first establish the id with the > "select MAX(id) from comments where id = provided_id", and then do a new > query " select * from comments where id = MAX(id) " Without subselects you need 2 queries. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
