Link to home
Start Free TrialLog in
Avatar of _callisen

asked on

SQL Error: View's SELECT contains a subquery in the FROM clause

Hi MySQL experts

I'm new to MySQL (usually I work with T-SQL), and need your help!

I need to make a self join to solve the issue at hand, which works fine. However I would like to make query into a view, because I want to hide the complexity from the external developers.

The problem is that I get the following error: View's SELECT contains a subquery in the FROM clause

Are there any samt way to handle this i MySQL?
Avatar of Kent Dyer
Kent Dyer
Flag of United States of America image

Without seeing your query, kind of hard to postulate what you are needing..  However, it sounds like you are asking to make a view do what you need.


Why don't you put your query ?
Problem seems to be that your query has a SELECT inside your from clause. This isn't necessari to make a self-join. You have just to put the same table twice on the from and use an alias for every instance of the table, and of course put the join condition on WHERE clause.

If you put your query I can solve it.
Looks like you are trying to create a view that has a select statement in the FROM clause.  This is not allowed in mysql.  If for some reason what gplana is suggesting doesn't work (I've seen it happen, it really depends on your query), you can create another view for the subquery then just call that view in the join (basically a view that uses another view).  See for an example on this
Avatar of _callisen


This is my query - a readable sample of it :-)

Is there a way to get the same output into a MySQL view?

--TABLE test
ID  , name, size, type, type_name
1   , H2O t-shirt, small, brand, H2O
1   , H2O t-shirt, small,color, red
2   , H2O t-shirt, medium, brand, H2O
2   , H2O t-shirt, medium, color, red

        WHEN test_a.type = 'Brand'
          THEN test_a.type_name
       END AS Brand
FROM test AS test_a
            WHEN test_b.type = 'Color'
              THEN test_b.type_name
           END AS Color
    FROM test AS test_b
    GROUP By name, size
  ) AS test_c ON test_a.ID = test_c.ID
GROUP By name, size

ID  , name, size, Brand, Color
1   , H2O t-shirt, small, H2O, red
2   , H2O t-shirt, medium, H2O, red
Avatar of johanntagle
Flag of Philippines image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
gplana: When I follow your advise the color column returns NULL, but with the above SQL I receive the correct resultset
Thanks :-)