merge select

Question

merge select

Navigation

#1 by (4 votes)

1

SELECT vendedores.Nombre,(SELECT  SUM(factura.Total) FROM factura WHERE 
vendedores.Codigo=factura.CodigoVendedor AND factura.Tipo='Factura'
AND factura.Fecha BETWEEN '2018-09-13' AND '2018-09-14' GROUP BY 
vendedores.Nombre)+(SELECT  SUM(sales.Total) FROM sales WHERE 
vendedores.Codigo=sales.CodigoVendedor 
AND sales.Fecha BETWEEN '2018-09-13' AND '2018-09-14' GROUP BY 
vendedores.Nombre)+(SELECT SUM(abonos.Abono) FROM abonos,saldos WHERE 
abonos.NumFactura=saldos.NumFactura AND 
saldos.CodigoVendedor=vendedores.Codigo AND abonos.Fecha BETWEEN '2018-09- 
13' AND '2018-09-14' GROUP BY vendedores.Nombre)
FROM vendedores

As I do so that in that query the "between Date '' and ''" is out as globally so to speak, after "from sellers"

mysql

asked by Richard Víquez Pérez 26.09.2018 в 06:23

source

1 answer

How to jump a column in a datagrid to print the next one? Error importing "Injectable" from Angular

score 4 · Answer 1

Welcome to Stackoverflow.

It seems to me that this type of query is better expressed and clearer when you use JOIN to join the tables.

In the JOIN you add each table (if possible with a single letter alias so you do not have to use the full name of the table) and you put out the criteria WHERE .

Now, what you want in the approach, it seems to me that it is not possible. If each table has a field relative to the date, there can be no way to globalize it (at least I do not think so, unless another expert denies me), because each date column belongs to your table .

What would be interesting is to ask:

Why is this column repeating so much Fecha in three tables if I then have to filter three times?
Will not I have to relate the tables in another way, so that the Fecha field does not triplicate unnecessarily?

I would suggest writing the query like this. At least it is a less confusing query, it is seen who is related to whom, what filters are applied and could even give us an idea to re-design the data model:

SELECT 
    v.Nombre,
    SUM(f.Total)+SUM(sales.Total)+SUM(a.Abono) total     
FROM vendedores v 
INNER JOIN
    factura f ON v.Codigo=f.CodigoVendedor
    sales     ON v.Codigo=sales.CodigoVendedor
    saldos    ON v.Codigo=saldos.CodigoVendedor
    abonos a  ON saldos.NumFactura=a.NumFactura
WHERE
    f.Tipo='Factura' 
    AND f.Fecha     BETWEEN '2018-09-13' AND '2018-09-14'   
    AND sales.Fecha BETWEEN '2018-09-13' AND '2018-09-14'
    AND a.Fecha     BETWEEN '2018-09-13' AND '2018-09-14'
GROUP BY v.Nombre