How to remove the subquery in creating a MySQL view

Question

How to remove the subquery in creating a MySQL view

Navigation

#1 by (2 votes)

3

I have this query in MySQL

Select t.grupo,
   Sum(servicios.cantidad) As total
From (select distinct clv_servicio, grupo from actuacion) as t
Join servicios On t.clv_servicio = servicios.id_servicos
Group By t.grupo

That works correctly but at the time of creating it I get this error:

# HY000View's SELECT contains subquery in the FROM clause

Is there any way to modify it so that the view can be created? Thanks.

mysql sql

asked by Jose 04.05.2016 в 22:44

source

1 answer

PostgreSQL external db in Django Add new row to a Gridview Devexpress with data from a SQL query

score 2 · Accepted Answer

That's part of mysql when creating views You can not have subquerys in your select

The solution is to create two views:

Vista Uno

Create view VistaUno
As
select distinct clv_servicio, grupo 
  from actuacion

Vista Dos

Create View VistaDos
As
Select t.grupo,
   Sum(servicios.cantidad) As total
  From VistaUno as t
  Join servicios On t.clv_servicio = servicios.id_servicos
 Group By t.grupo

Here is an interesting and complete article on creation and manipulation of views