Avatar of _callisen
_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?
MySQL Server

Avatar of undefined
Last Comment
_callisen

8/22/2022 - Mon
Kent Dyer

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
gplana

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.
johanntagle

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
_callisen

ASKER
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
ASKER CERTIFIED SOLUTION
johanntagle

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
_callisen

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

ASKER
Thanks :-)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.