Query Mysql to count contracts with INNER JOIN

Asked

Viewed 246 times

0

Hello,

relating the following tables:

Contract table

tb_contrato
id
credor_id
num_contrato

Invoice Table

tb_fatura
id
contrato_id
num_fatura
dt_vencimento
valor

Parameters

credor  = 1
valor inicial  = 1
valor final = 99999
atraso inicial = 1
atraso final = 99999

Query

SELECT `contrato`.`num_contrato`
FROM `tb_contrato` `contrato`
JOIN `tb_fatura` `fatura` ON `fatura`.`contrato_id` = `contrato`.`id`
WHERE `tb_fatura`.`valor` >= valor_inicial
AND `tb_fatura`.`valor` <= valor_final

using the parameters, I need to count the number of contracts according to the above query, also I need to understand how I can filter the contracts using the days in arrears of the contract, comparing the expiration date of the invoice with today’s date!

mysql sql

  • In your query a contract will be listed more than once if you have more than one invoice that meets the conditions. To count the different contracts you must use the COUNT function with the DISTINCT clause, but I’m not entirely sure that’s what you want.

  • That’s exactly what’s going on, you’re listing one for each invoice and that’s not what I want. what really is I want to list only the amount of contracts that fit into the query parameters.

  • Yes, it is expected from the result of an INNER JOIN.

  • You can help me build the baby ?

2 answers

2


As quoted in the comments, to know the number of contacts, just make a distinct Count based on the contract number. Here is an example

SELECT count(distinct `contrato`.`num_contrato`) as total_contratos
FROM `tb_contrato` `contrato`
JOIN `tb_fatura` `fatura` ON `fatura`.`contrato_id` = `contrato`.`id`
WHERE `bill`.`valor` between valor_inicial AND valor_final

The invoice table doesn’t have a field to say if you paid or not. To view a count of records whose due date is less than the current date, the following can be done:

SELECT count(distinct `contrato`.`num_contrato`) as total_contratos
FROM `tb_contrato` `contrato`
JOIN `tb_fatura` `fatura` ON `fatura`.`contrato_id` = `contrato`.`id`
WHERE `bill`.`valor` between valor_inicial AND valor_final
AND `fatura`.`dt_vencimento` < CURRENT_DATE()

To return records with a certain amount of days in delay, the DATEDIFF function can be used . Here are examples:

http://jquerydicas.blogspot.com/2013/11/mysql-diferenca-entre-datas.html

1

As clarified in the comments:

SELECT DISTINCT `contrato`.`num_contrato`
FROM `tb_contrato` `contrato`
JOIN `tb_fatura` `fatura` ON `fatura`.`contrato_id` = `contrato`.`id`
WHERE `bill`.`valor` >= valor_inicial
AND `bill`.`valor` <= valor_final

to list the different contracts that meet the conditions, and:

SELECT COUNT(DISTINCT `contrato`.`num_contrato`)
FROM `tb_contrato` `contrato`
JOIN `tb_fatura` `fatura` ON `fatura`.`contrato_id` = `contrato`.`id`
WHERE `bill`.`valor` >= valor_inicial
AND `bill`.`valor` <= valor_final

for the quantity of contracts meeting the conditions.

Browser other questions tagged mysql sql

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