SQL help - sink plugs!
#2
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.
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.
#3
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...
...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...
#5
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)))
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)))
#6
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]
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]
#7
Scooby Regular
Join Date: Jul 2000
Location: Islington
Posts: 2,145
Likes: 0
Received 0 Likes
on
0 Posts
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]
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]
Thread
Thread Starter
Forum
Replies
Last Post