Re: need help to build a query

afan@afan.net wrote:

hi to all, I have table orders with primary key order_id. I have table uploaded_files with primary key ufid and uploaded files are linked to orders by order_id column. I have to list all orders and uploaded files. this works fine: $query = mysql_query(" select order_id, order_date, order_status from orders order by order_id desc limit 100"); while($result=mysql_fetch_array($query)) { echo "ID: ". $result['order_date']."|"; echo "DATE: ". $result['order_date'] ."|"; echo "STATUS: ". $result['order_status'] ."|"; echo "UPLOADED FILES: "; $query2 = mysql_query("

    select uf.file_name
    from uploaded_files as uf
    where uf.order_id = $result['order_id']

"); while($result2=mysql_fetch_array($query2)) {

    echo $result2['file_name']."|";

} echo "<hr>"; } but I know there must be much better solution then this one. thanks for any help. -afan

Perhaps something like this: (not sure how this would play with the limit key word, but you could play around with it...) If you can guarantee that a record (order_id) will appear in both tables, a simple join will work... but if a record in table A exists but not in table B, a join will not return that record, which is why there is a left outer join. select o.order_id, o.order_date, o.order_status, uf.file_name from orders o left outer join uploaded_files uf on uf.order_id = o.order_id order by o.order_id desc Not sure if mysql supports this..?? select * from (select o.order_id, o.order_date, o.order_status, uf.file_name from orders o left outer join uploaded_files uf on uf.oerder_id = o.order_id order by o.order_id desc ) LIMIT 100