0

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.

Community
  • 1
  • 1
Michal
  • 1,955
  • 5
  • 33
  • 56
  • 1
    you have a subquery in the from clause.. whether or not its in the JOIN.. that is still a subquery. make the subquery a view and join that view instead of the subquery. like that other post has an answer to. – John Ruddell Aug 26 '14 at 21:49
  • if that still doesn't make sense to you then go to this webpage http://dev.mysql.com/doc/refman/5.0/en/create-view.html --------- search for 'subquery in FROM' on the page. and read that post. it should explain it for you. – John Ruddell Aug 26 '14 at 21:51
  • Aha, I see, I thought that JOIN does not belong to FROM, but it does. My fault, thank you – Michal Aug 26 '14 at 21:51
  • If you want to fix the query, ask another question. – Gordon Linoff Aug 26 '14 at 21:59

0 Answers0