Select data from three SQL tables

Question

Select data from three SQL tables

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I'm trying to merge data from three tables using this sql:

SELECT cod_nota, cupom, valor_contrato, valor_cadastro, data_emissao
FROM cupons_nota, vendas, notas_fiscais
WHERE cupons_nota.cupom = vendas.numero_nf AND notas_fiscais.cod_cliente='00000212' AND data_emissao BETWEEN '01.03.2018' AND '30.04.2018'
ORDER BY cod_nota ASC;

The problem is that it is returning the triplicate data, and I only wanted the data once.

Result example:

mysql sql join query firebird

asked by anonymous 24.05.2018 / 15:20

2 answers

0

You can do the query as well.

SELECT cod_nota, cupom, valor_contrato, valor_cadastro, data_emissao
FROM cupons_nota join vendas on id_tabela1=id_tabela2 join notas_fiscais on id_tabela2 = id id_tabela3 WHERE cupons_nota.cupom = vendas.numero_nf AND notas_fiscais.cod_cliente='00000212' AND data_emissao BETWEEN '01.03.2018' AND '30.04.2018'
ORDER BY cod_nota ASC;

24.05.2018 / 15:43

Display a record by quantity and the most repeated value of a column How do I add values from different tables?

score 0 · Accepted Answer

To group the results we use the GROUP BY with the field (s) that will be grouped, in its case, would be the field cod_nota :

SELECT cod_nota, cupom, valor_contrato, valor_cadastro, data_emissao
FROM cupons_nota, vendas, notas_fiscais
WHERE cupons_nota.cupom = vendas.numero_nf AND notas_fiscais.cod_cliente='00000212' AND data_emissao BETWEEN '01.03.2018' AND '30.04.2018'
GROUP BY cod_nota
ORDER BY cod_nota ASC;

If you need to add the results of a column use the SUM function:

SELECT cod_nota, cupom, SUM(valor_contrato) valor_contrato, SUM(valor_cadastro) valor_cadastro, data_emissao
FROM cupons_nota, vendas, notas_fiscais
WHERE cupons_nota.cupom = vendas.numero_nf AND notas_fiscais.cod_cliente='00000212' AND data_emissao BETWEEN '01.03.2018' AND '30.04.2018'
GROUP BY cod_nota
ORDER BY cod_nota ASC;