Re: need help to build a query

On Tue, 10 Oct 2006 14:22:54 -0500, Richard Lynch wrote: > On Mon, October 9, 2006 2:58 pm, John Wells wrote: >> On 10/9/06, afan@afan.net <afan@afan.net> wrote: >>> but I know there must be much better solution then this one. >> >> You're right: Simply JOIN your queries... >> >> SELECT order_id, order_date, order_status, file_name >> FROM orders >> JOIN uploaded_files AS uf ON orders.order_id = uf.order_id >> ORDER BY orders.order_id DESC >> LIMIT 100 > > This will "miss" all the orders with no upload at all, unlike the > original. As specified elsewhere in this thread, use outer join to prevent losing orders with no uploads. > AND it limits you to the 100 uploaded files, not 100 orders. > > This is a lot more subtle problem than it seems at first glance, eh? To fetch all uploaded filenames in one result row, and solving the limit problem all at once, see group_concat(). Mind you, works only with MySQL 4.1... Ivo