I have this error in my view: "View's SELECT contains a subquery in the FROM clause" What do I do?

Question

I have this error in my view: "View's SELECT contains a subquery in the FROM clause" What do I do?

Navigation

#1 by (1 votes)

2

I want to convert the following query into a view, but I still get the error mentioned above. This is my query:

SELECT C.id_pdmatriz, C.id_proveedor, C.Precio , x.Nombre , p.Nombre
FROM (
SELECT MIN(A.Precio) AS Precio , A.id_pdmatriz
FROM producto A where A.id_proveedor > 0 and A.Estado_id !=5
GROUP BY A.id_pdmatriz) B
JOIN producto C
ON B.Precio = C.Precio
and B.id_pdmatriz = C.id_pdmatriz 
left Join producto_matriz x
ON C.id_pdmatriz = x.id
left Join proveedor p
ON C.id_proveedor = p.id;

Can you help me create a view with this query? Why do I get the error?

mysql

asked by Fmcv Mcv 18.08.2017 в 17:37

source

1 answer

Regular expression for django url with slug containing "/" How to create a list with indeterminate levels?

score 1 · Accepted Answer

In MySql, it is not possible to include a sub query in the views. You can see more information of the views for the version 5.7 of MySql in this link.

A possible solution is having an independent view for your sub query. The first view could look like this:

CREATE VIEW Vista1 AS SELECT C.id_pdmatriz, C.id_proveedor, C.Precio , x.Nombre , p.Nombre
FROM Vista2 B
JOIN producto C
ON B.Precio = C.Precio
and B.id_pdmatriz = C.id_pdmatriz 
left Join producto_matriz x
ON C.id_pdmatriz = x.id
left Join proveedor p
ON C.id_proveedor = p.id;

The second view would look like this:

CREATE VIEW Vista2 AS 
SELECT MIN(A.Precio) AS Precio , A.id_pdmatriz
FROM producto A where A.id_proveedor > 0 and A.Estado_id !=5
GROUP BY A.id_pdmatriz