Min value for a field

[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.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 and just cannot see the problem.

[mikr0s]

SELECT X.*, PP.product
FROM prefs PR
JOIN products PP ON PP.prodid = PR.prodid --get product names
JOIN sup_prices X ON X.prodid = PR.prodid --get all suppliers, all prices of each product
JOIN ( SELECT prodid, MIN(price_each) AS price_each FROM sup_prices GROUP BY prodid) A ON A.prodid = X.prodid AND A.price_each = X.price_each --keep only the
--minimum prices per product
WHERE PR.userid = 1

--if there are two suppliers having the minimum price of a product, then both suppliers will be displayed

--HTH--