Query with Internet

Question

I need to check if a data in Table 1 is represented in table 2

In the tabela1

ID | NOME | PARCELAS | DATA | NF

In the tabela2

ID | VALOR | PARCELA | VENCIMENTO | NF

SQL query with INNER JOIN

I may be mistaken in wanting to consult with inner join what I need to know if the amount of launches the tabela2 and actually the amount of plots reported in the column parcelas of tabela1, example if in the tabela1 the column parcelas is reporting 3 need to know if there really are 3 launches on tabela2.

The consultation I performed and' this' but it is not bringing what I need.

SELECT * 
FROM  `tabela2` 
INNER JOIN tabela1 ON ftabela2.NF = 
tabela1.NF
WHERE fin_lancamentos.Qtde_Parcelas > 1

Answer 1

You have to use LEFT JOIN , 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 so:

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;

Answer 2

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

SELECT * FROM tabela1 T1 
WHERE T1.PARCELAS = (SELECT COUNT(T2.PARCELA) FROM tabela2 T2 WHERE T2.NF = T1.NF)