Mysql error on view but works in normal query

Question

I am getting an error: "View's SELECT contains a subquery in the FROM clause" when I try to create a view for it but it works fine in a normal query. I assume it has something to do with my WHERE clause but I don't understand why or how to fix it. This is my desired query:

select concat(`company`, ' | ', `material`, ' | ', `newcost`) as datarow from( Select `b`.`company` AS `company`, `bp`.`material` AS `material`, if(((`bp`.`cost` * 1.2) < `ls`.`maximumbid`), (`bp`.`cost` * 1.2),`bp`.`cost`) AS `newcost` from (((`windows_brands_products` `bp` left join `windows_brands` `b` on((`bp`.`brand_id` = `b`.`id`))) join `Windows_last_submissions` `ls`) join `windows_materials` `wm`) where ((`bp`.`width` = round(`ls`.`width`,0)) and (`bp`.`height` = round(`ls`.`height`,0)) and (`bp`.`material` = `wm`.`name`) and (`bp`.`type` = `ls`.`type`) and if((`ls`.`minimumbid` <> '0.00'),(`bp`.`cost` between `ls`.`minimumbid` and `ls`.`maximumbid`),(`bp`.`cost` <= `ls`.`maximumbid`))) ) x

Why is it showing this error and how to I change it so I can use this as a view (ultimately I am using each generated row as dynamic data for a dropdown menu).

Please use the preview when asking a question to ensure your query is formatted properly, this is very hard to read. — AdamMc331, Jun 15 '15 at 17:28
No worries. I would try to edit it, but you know better than I do where it starts and ends and I don't want to risk changing the intent. — AdamMc331, Jun 15 '15 at 17:31
[This answer](http://stackoverflow.com/a/3520552/3131147) may help you, I'm not sure. — AdamMc331, Jun 15 '15 at 17:37

score 0 · Answer 1 · answered Jun 15 '15 at 17:49

Ya in a way McAdam331 that would have most likely helped, but a friend helped me figure it out.. I just kept my previous view of the above query without the concat stuff (thus getting rid of the aliases) and made another view and queried:

select concat(`company`, ' | ', `material`, ' | ', `newcost`) as options
from Windows_last_options

so that referenced the "non concatenated" view and worked!

Mysql error on view but works in normal query

1 Answers1