I am getting a MySQL 1349 error, but it appears as though the error is incorrect:
ERROR 1349: View's SELECT contains a subquery in the FROM clause
Can I not have ANY subqueries when creating a view?
Here is my SQL:
CREATE VIEW `wordpress`.`ffi_be_v_book_details` AS (
SELECT ffi_be_courses. * , COALESCE( `Total` , 0 ) AS `Total`
FROM `ffi_be_courses`
LEFT JOIN (
SELECT * , COUNT( `Course` ) AS `Total`
FROM ffi_be_courses
RIGHT JOIN (
SELECT `Course`
FROM `ffi_be_bookcourses`
LEFT JOIN `ffi_be_sale` ON ffi_be_bookcourses.SaleID = ffi_be_sale.SaleID
WHERE DATE_ADD( ffi_be_sale.Upload, INTERVAL(
SELECT `BookExpireMonths`
FROM `ffi_be_settings` ) MONTH ) > CURDATE( ) AND ffi_be_sale.Sold = '0'
GROUP BY ffi_be_bookcourses.SaleID
) AS `q1` ON ffi_be_courses.Code = q1.Course
GROUP BY q1.Course
) AS `q2` ON ffi_be_courses.Code = q2.Code
WHERE ffi_be_courses.Type = 'Arts'
ORDER BY ffi_be_courses.Name ASC
)
Thank you for your time.
解决方案
The above answers are correct. MySQL 5.5 does not allow subqueries in the FROM clause. It is allowed now, however, in MySQL 5.7.7 and later.