Min value for a category

[heals1ic]

I will start off by posting my SQL code first-

SELECT X.prodid, Y.product, X.supplierid, MIN( X.price_each ) AS minprice
FROM sup_prices X
INNER JOIN (
  SELECT PP.prodid, PP.product
  FROM products PP
  INNER JOIN prefs PR ON PP.catid = PR.catid
  AND PP.prodid = PR.pref
  WHERE userid =1
)Y ON X.prodid = Y.prodid
GROUP BY X.prodid

table prefs -> userid catid pref_num pref

(users preferences, pref_num has a maximum of 3 therefore each user has the option of 3 preferences)

table sup_prices -> supplierid prodid price_each

(prices for each supplier)

table products -> prodid product catid

(table of products)

My aim is to select the minimum priced supplier for the products selected in the users preferences.

It seems to pull out the minimum prices but not the associated supplier?

Any ideas on where my query is incorrect? It has me stumpted, I have been looking at it for hours just cannot see the problem.

 

[r937]

this must be MySQL, as that's the only database that will run this query with the incorrect GROUP BY

you are getting unpredictable results with the incorrect GROUP BY, and to be fair, the mysql site does warn you that this will happen (do a search on the mysql site for "group by with hidden fields")

using a correlated subquery should fix things, as it has the same effect as grouping

select X.prodid
     , X.supplierid
     , X.price_each  as minprice
  from sup_prices X
inner 
  join (
       select PP.prodid
            , PP.product
         from products PP
       inner 
         join prefs PR 
           on PP.catid = PR.catid
          and PP.prodid = PR.pref
        where userid =1
       ) Y 
    on X.prodid = Y.prodid
 [COLOR=red]where X.price_each
     = ( select min(price_each)
           from sup_prices
          where prodid = X.prodid )[/color]

r937.com | rudy.ca