Select data from three SQL tables

Asked

Viewed 98 times

0

I am trying to join 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’s returning the triplicate data, and I wanted only once the dice.

Example of the result:

inserir a descrição da imagem aqui

mysql sql query firebird join

  • What is the expected result?

  • it returns "triplicate" because you must have 3 notes for this client. I repeat the question of @Robertodecampos, "what is the expected result"?

  • The expected result was a single line, not the three.

  • if you have to add a column (add, average, max, min) use the group by. If the data is correct and are only duplicating because of Join, use the Distinct

  • how would you look wearing the Distinct?

  • Select distinct ......(everything is already the same.).....

Show 1 more comment

2 answers

0


To group the results we use the GROUP BY with the field(s) (s) that will be grouped, in your 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 sum the results of a column use the function SUM:

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;

  • When I tried it now, it returned the following error: Invalid Expression in the select list (not contained in either an Aggregate Function or the GROUP BY clause)

  • Which error was returned?

  • because there is no aggregation... use the Distinct

  • It worked, thanks guys!

0

You can do the consultation like this.

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;

Browser other questions tagged mysql sql query firebird join

You are not signed in. Login or sign up in order to post.