_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?
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?
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.
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 http://stackoverflow.com/a/8428729/650863 for an example on this
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
gplana: When I follow your advise the color column returns NULL, but with the above SQL I receive the correct resultset
ASKER
Thanks :-)
HTH,
Kent