MySql View 'SELECT contains a subquery'

[mikazza]

Hi,

I've been trying to get a view written for the past few days, I run a website which runs off MySql and also a local version of the same site which runs off MSAccess i've just about managed to get both versions running off the same code, all except the one view or query, this works in access but not mysql:

SELECT t.fld_prodcode,
t.display,
t.fld_shopcode,
t.fld_itemcode,
t.fld_price,
t.fld_postage,
t.fld_stock,
t.fld_condition,
t.fld_link,
Total
FROM (SELECT fld_prodcode,
fld_shopcode,
fld_stock,
fld_condition,
Min(fld_itemcode) as min_code,
MIN(total) AS min_total
FROM qry_shopprices
GROUP BY fld_prodcode, fld_shopcode, fld_stock, fld_condition) AS m
INNER JOIN qry_shopprices AS t
ON t.fld_prodcode = m.fld_prodcode
AND t.fld_shopcode = m.fld_shopcode
AND t.fld_stock = m.fld_stock
AND t.fld_condition = m.fld_condition
AND t.total= m.min_total
AND t.fld_itemcode = m.min_code;

I have another view setup called qry_shopprices which works ok and this view is based on that, on access this is a query based on another query but it's the same type of thing. When I try to add this view to the MySQL database I get this:

MySQL said:

#1349 - View's SELECT contains a subquery in the FROM clause

Does anybody have any idea how to get this working in a MySql view?

[BubikolRamios]

mybe you should do 2 views from this. The second beeing view of first wiew.

[Old Pedant]

What version of MySQL are you running??? I have no problem with things like that in version 5.1, I'm pretty sure.

But worst come to worst, do this is *TWO* views.

Thus:

CREATE VIEW view1 AS
SELECT fld_prodcode,
fld_shopcode,
fld_stock,
fld_condition,
Min(fld_itemcode) as min_code,
MIN(total) AS min_total
FROM qry_shopprices
GROUP BY fld_prodcode, fld_shopcode, fld_stock, fld_condition;

CREATE VIEW view2 AS
SELECT t.fld_prodcode,
t.display,
t.fld_shopcode,
t.fld_itemcode,
t.fld_price,
t.fld_postage,
t.fld_stock,
t.fld_condition,
t.fld_link,
Total
FROM view1 AS m
INNER JOIN qry_shopprices AS t
ON t.fld_prodcode = m.fld_prodcode
AND t.fld_shopcode = m.fld_shopcode
AND t.fld_stock = m.fld_stock
AND t.fld_condition = m.fld_condition
AND t.total= m.min_total
AND t.fld_itemcode = m.min_code;

...

[Old Pedant]

LOL! Beaten by *seconds* just because I'm so darned verbose!