Weird Ordering & Queries

Ive come up with a curly problem and im wondering if anyone can shed some lights on it. I looked under the SELECT part of Mysql but it didnt seem to cover this and I didnt know how to phrase it. What I have is a displayed list on the site. This data that makes up the list comes from 2 tables. 'item' and 'location' are the 2 table names. For each item there is 1 entry in the item table. But under location there is several entries depending on where the part is. Its done this way to log a history of whether the item has been sent out, or received in. Each entry in the location table is date stamped and records the items location, and the item_id field. Currently I am doing my list like this. $item_q=mysql_query("select * from item order by item name"); while ($item=mysql_fetch_array($item_q)) {

        $location_q=mysql_query("select * from location where item_id=$item_q[item_id] order by date desc limit=1");
        $location=mysql_fetch_array($location_q);
        echo"$itemlist[item_name] - $location[location] - $location[date]";

} The reason I do it this way is that I firstly need it to list all items, but then next step (and next SELECT) it grabs the most recent entry in location for that item. My problem is, I need to be able to combine the SELECT queries into 1, so that I can order by either Location, Date or Name Im not sure if this sort of thing is possible but it would make life so much easier if I could do it. Thanks Chris --

      Chris Aitken - Webmaster/Database Designer - IDEAL Internet

email: chris@ideal.net.au phone: +61 2 4628 8888 fax: +61 2 4628 8890

            --------------------------------------------

      Unix -- because a computer's a terrible thing to waste!