'GROUP BY' problem...

[stoocoo]

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

[bubblegum.anarchy]

product_offers

offerid      product_name      supplier_id      price

 

should probably be:

 

product_offers

offerid      product_id      supplier_id      price

[bubblegum.anarchy]

Personally, I find these types of queries confusing as all heck.

 

The following should list the supplier_id, product_id and price of the lowest offer, and a left join to the resulting set should provide the appropriate information, including off coarse the products that have had no offers:

 

SELECT derived.product_id, product_offers.supplier_id, derived.lowest_price
FROM product_offers
INNER JOIN ( 
	SELECT product_id, min(price) AS lowest_price FROM product_offers GROUP BY product_id ) AS derived 
	ON product_offers.product_id = derived.product_id AND product_offers.price = derived.lowest_price

 

The above query assumes that product_id is used instead of product_name in the product_offers table.

[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

[bubblegum.anarchy]

The INNER JOIN is intentional so as not to return superfluous records... like I said, a LEFT JOIN to the query block I provided, from the products_info table will result in all products being listed.

 

So all that needs to be done is SELECT FROM product_info, INNER JOIN to manufacturers and finally LEFT JOIN to the query block I provided.