Query on two tables with count

Question

Query on two tables with count

Navigation

#1 by (1 votes)

0

I have a table in mysql that has a number of contracted items.

Table itens_contratados

contracted id
  Item
  Qtd_contracted

Table servico , in this I inform the service that I executed in the contract

contract_id

I want to make a query that tells me this:

itens_contratados.id_contratado itens_contratados.qtd_contratada servicos.qtd_servico saldo

Where qtd_servico is equal to the number of records found in the servico table for id_contratado and saldo = qtd_contratada - qtd_servico

mysql sql

asked by anonymous 13.07.2017 / 19:06

1 answer

ImageView of another Activity Localization zero in the Android Maps API

score 1 · Accepted Answer

As you asked in the comments of your question, a generic query using INNER JOIN is below:

SELECT a.id_contratado, a.Qtde_contratada, COUNT(b.id_contratado) qtde_servico, (a.Qtde_contratada - COUNT(b.id_contratado)) saldo
FROM items_contratados a
INNER JOIN servico b ON a.id_contratado = b.id_contratado
GROUP BY a.id_contratado