Re: problem with SELECT

[Felix Geerinckx]

[Your question has nothing to do with MySQL transactions. Please pick a more appropriate forum next time]

Steven Baker wrote:

> i am looking for a SELECT statement that produces:
> Product1 10 Acme1
> Product2 120 Acme2
> These are the productname, the lowest price for the product and the supplier that offers it for
> the lowest price.

SELECT
p.NAME,
c.Price,
s.NAME
FROM tableproduct p
JOIN connectiontable c ON p.ID1 = c.Product
JOIN tablesupplier s ON c.Supplier = s.ID2
WHERE
c.Price = (SELECT MIN(Price) FROM connectiontable WHERE Product = p.ID1);

--
felix
Please use BBCode to format your messages in this forum.