Query with inner join

1

I need to check if a data in table1 is represented in table2

Na tabela1

ID | NOME | PARCELAS | DATA | NF

Na tabela2

ID | VALOR | PARCELA | VENCIMENTO | NF
  

SQL Query with INNER JOIN

I may be mistakenly wanting to query with inner join what I need to know if the number of entries of tabela2 is actually the amount of parcels reported in column parcelas of tabela1 , example if in% with% column tabela1 is reporting 3 I need to know if there are actually 3 releases in parcelas .

The query I performed is 'but' is not bringing what I need.

SELECT * 
FROM  'tabela2' 
INNER JOIN tabela1 ON ftabela2.NF = 
tabela1.NF
WHERE fin_lancamentos.Qtde_Parcelas > 1
    
asked by anonymous 20.02.2018 / 15:59

2 answers

2

I believe the best way to do this would be with a subquery in the where condition of SQL. For example:

SELECT * FROM tabela1 T1 
WHERE T1.PARCELAS = (SELECT COUNT(T2.PARCELA) FROM tabela2 T2 WHERE T2.NF = T1.NF)
    
20.02.2018 / 16:09
2

You have to use LEFT JOIN , it would look like this:

SELECT a.NF, a.parcelas, COUNT(b.id) AS qtdeParcelas FROM tabela1 a
LEFT JOIN tabela2 b ON a.NF = b.NF
GROUP BY a.NF;

If you want to bring only the ones that are correct you can do this:

SELECT a.NF, a.parcelas, COUNT(b.id) AS qtdeParcelas FROM tabela1 a
LEFT JOIN tabela2 b ON a.NF = b.NF
HAVING tabela1.parcelas = qtdeParcelas
GROUP BY a.NF;
    
20.02.2018 / 16:06