stoocoo

thanks so much for the replies and yes I get confused as heck too! firstly, for reasons I won't go into, I am stuck with the product_name being used in the offers table instead of product_id which it should be, thanks for pointing that out. I have tried the query you suggested and while it does seem to pull out the correct offer_id for the lowest price offers, it shows ALL offers at that price rather than just one offer per product. It seems that I would still need a ' GROUP BY product_id' at the end of the main SELECT to accomplish this, or am I missing something? I think that using an INNER JOIN to a temporary table created by a SELECT statement is the big missing piece, but I'm still left with a list a lot longer than I want! Can you see a way to get only one offer per product and still be able to add on the other tables i need to get information from (manufacturers, products, suppliers) ? thanks for your help so far, hope you'll have another go! bfn

Hi all, I have four tables: product_offers offerid product_name supplier_id price product_info id name description manufacturer_id manufacturers id name suppliers id name What I want is to get a list of the lowest offer price for each product along with the relevant offer information for ANY offer of that product at that price, plus product manufacturer and offer supplier information To put it another way: I want a list showing offers, one for each product, at the lowest price available, from any ONE of the suppliers offering it at that price. This is the latest incarnation of the query I am trying to wrestle into shape: select o.product_name , o.price , o.offerid from offers as o, products as p, manufacturers as m, suppliers as s where o.product_name = p.name AND p.manufacturerid = m.id AND o.supplier_id = s.id o.price = ( select min(o.price) from offers as o2 where o2.product_name = o.product_name ) group by o.product_name order by o.product_name ...but as you'll see, this gives me the correct info for product name and price, but the value for offerid is junk, as is any information I try to pull from other tables. I would appreciate any neat solutions to this using only mysql. I am thinking now that I'll just do it in two stages and use PHP to loop through results from an initial query to get the detail but I know that will be clumsy... any ideas anyone? Many thanks in advance, stoocoo