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

_callisen
_callisen used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kent DyerIT Security Analyst Senior

Commented:
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.

HTH,

Kent
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.
Top Expert 2012

Commented:
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 http://stackoverflow.com/a/8428729/650863 for an example on this
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
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

SELECT ID
      ,name
      ,size
      ,CASE
        WHEN test_a.type = 'Brand'
          THEN test_a.type_name
       END AS Brand
       ,test_c.color
FROM test AS test_a
LEFT OUTER JOIN
  (
    SELECT ID
          ,CASE
            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

OUTPUT:
ID  , name, size, Brand, Color
1   , H2O t-shirt, small, H2O, red
2   , H2O t-shirt, medium, H2O, red
Top Expert 2012
Commented:
Create a view using the query used for test_c then use that view in your left join

Author

Commented:
gplana: When I follow your advise the color column returns NULL, but with the above SQL I receive the correct resultset

Author

Commented:
Thanks :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial