Why not order in descending values and return the first row?

 

That would only return one result from the search.

The problem is that not only do I need to retrive the lowest price Acme plug, I need plugs from all the other manufacturers too.

 

Just what I was after! Thanks!

...Except mySQL doesn't like it. MS SQL accepts the query fine, but MySQL errors:

"You have an error in your SQL syntax near 'SELECT MIN(s.stock_price) FROM tbl_stock AS s" etc...

I'm guessing there is something about the structure of the query that MySQL doesn't like but I can't figure what...

 

the tbl_stock s predicate shouldn't be the same as in the main sql function (if MySQL follows ANSI).

 

I've changed the predicate but it still won't run it..

This is what I have, works fine in MS SQL:

SELECT p.prod_id, c.cat_name, m.mfr_name, p.prod_name, s.stock_price, p.prod_imageurl, p.prod_desc
FROM tbl_prod p INNER JOIN
tbl_mfr m ON p.mfr_id = m.mfr_id INNER JOIN
tbl_cat c ON p.cat_id = c.cat_id INNER JOIN
tbl_stock s ON p.prod_id = s.prod_id
WHERE (p.prod_id IN (12, 6, 7)) AND (s.stock_price =
(SELECT MIN(ss.stock_price)
FROM tbl_stock AS ss
WHERE ss.prod_id IN (12, 6, 7)))

 

I'm writing a stock database for kitchen fittings and accessories. For example there is a product "sink plug" manufactured by "Acme kitchens".

There is one product_id for this as the mfr info and colour is the same for all applications, however there are multiple entries in the stock table because the Acme sink plug is produced for different size Acme sinks and the price is different for each application.

Searching for "sink plug" returns all the individual applications for sink plugs, what I need it to do is just return the lowest price application for each type of sink plug. I think I need a DISTINCT somewhere but I can't figure it out.

My tables are as follows:

tbl_prod
----------
prod_id
prod_name

tbl_mfr
---------
mfr_id
mfr_name

tbl_application
--------------
application_id
application_name

tbl_stock
----------
stock_id
prod_id
application_id
stock_price

My sql is:

SELECT p.prod_id, m.mfr_name, p.prod_name, s.stock_price
FROM tbl_prod p INNER JOIN
tbl_mfr m ON p.mfr_id = m.mfr_id INNER JOIN
tbl_cat c ON p.cat_id = c.cat_id INNER JOIN
tbl_stock s ON p.prod_id = s.prod_id
WHERE (p.prod_name LIKE '%sink plug%')
ORDER BY s.stock_price

Thanks in advance.

Adrian.

[Edited by AdrianFRST - 1/12/2003 12:31:39 PM]

 

you need a
and s.stock_price = (select min(ss.stock_price) from tbl_stock ss where ss.stuff = previous table definitions.stuff)

that way you'll only get the smallest value back for each type.

[Edited by Fosters - 1/12/2003 7:23:25 PM]