Good afternoon, folks. I use two providence and occurrence tables - being that I have 1 occurrence for N providences. I need to perform a registry query that shows how many actions has occurred.
Follow the image from my base, it's easier to understand 
I need to make a query that brings my My providences that are linked to the occurrence that I will type in the search.
There are several ways to do this search, "key equality" and "inner join".
//inner join
select count(providencia.cod_provid) from providencia inner join ocorrencia on occorencia.cod_ocorrencia = providencia.cod_ocorrencia;
// igualdade de chaves
select count(cod_provid) from providencia, ocorrencia where providencia.cod_ocorrencia = ocorrencia.cod_ocorrencia
The inner join would probably work better.
I would use:
If you want to find the data of the measures
SELECT p.*
FROM ocorrencia o
INNER JOIN providencia p ON (o.cod_ocorrencia = p.cod_ocorrencia)
WHERE o.cod_ocorrencia = 1;
If you want to find the amount of provisions for that occurrence
SELECT count(p.cod_providencia)
FROM ocorrencia o
INNER JOIN providencia p ON (o.cod_ocorrencia = p.cod_ocorrencia)
WHERE o.cod_ocorrencia = 1;
Replace 1 with the id of the instance you are looking for.
You can use COUNT () together with GROUP BY to return. In your case, it would look something like:
SELECT
COD_OCORRENCIA,
COUNT(COD_PROVID) AS TOTAL_PROVID
FROM
ocorrencia,
providencia
WHERE
ocorrencia.COD_OCORRENCIA = providencia.COD_OCORRENCIA
GROUP BY COD_OCORRENCIA
In this way, the query returns for each occurrence the total count (count) when there is a relationship between them, grouping by COD_CORRENCE.