having problem with following MySQL view. Syntax check says "Congrats! SQL looks fine" However, my MySQL 5.6.20 gives:
ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause
Here is the view
CREATE OR REPLACE VIEW view_salon_list AS SELECT
s.id AS salon_id,
v.name AS salon_name,
v.street AS salon_street,
v.photo AS salon_photo,
m.name AS city_name,
k.name AS county_name,
c.avg_rating AS avg_rating,
c.comment_count AS comment_count
FROM
salon_version AS v
LEFT JOIN salon AS s ON v.salonid = s.id
LEFT JOIN city AS m ON v.cityid = m.id
LEFT JOIN county AS k ON m.countyid = k.id
LEFT JOIN ( SELECT
vc.salon_id AS salon_id,
AVG(vc.rating_value) AS avg_rating,
COUNT(vc.salon_id) AS comment_count
FROM
view_comment_stats AS vc
GROUP BY
vc.salon_id
) AS c ON c.salon_id = s.id;
UPDATE:
To misters with "duplicite post" Maybe I am blind, but I haven't subquery in FROM clause, as it is in post you marked as answer to my problem. I have subquery only inside JOIN clause and according to answer of this post Selecting multiple columns/fields in MySQL subquery it should be possible.