0
Good afternoon, you guys I use two tables provides and occurrence - and I have 1 occurrence for N provids. I need to perform a record query that brings up how many provisions has the occurrence.
Follow the image of my base, it’s easy to understand 
I need to perform a query that brings my provisions that are linked to the occurrence that I will enter in the survey.
1
There are several ways to perform 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
Probably the Inner Join would be more right.
I am using the database in access for testing, when running the sql module informs that this failed in the expression Inner Join ....
was experiencing a syntax error in the code, on occorencia.cod_ocorrencia == providencia.cod_ocorrencia but I’ve fixed it, the right thing is on occorencia.cod_ocorrencia = providencia.cod_ocorrencia, checks the names of the tbm fields.
Brumazzi I thank you, but this command only brings the last record of the table and not all the provisions of the occurrence table. Does anyone have any idea ?
The code should actually return a value, just like the one you asked for, "I need to perform a record query that brings up how many provisions have occurred." the code counts how many provisions exist and returns, if you want the return of the provisions replace count(providencia.cod_provid) for providencia.cod_provid, this will return the codes of the provisions.
Let me give you an example of the image - My Cod_occurrence 0000002 generated two cod_provid 00002 and 00003. I need to generate a query that on the search screen when typing the cod_occurrence informs that you have 2 records for this provision.
0
I would use:
If you want to search the data of the provisions
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 seek 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 event you are searching for.
Your code occurs syntax error
Could you be more specific? Maybe it’s the name of a column? Which one? Both?
I found where I was, the column that was in the Where clause was ambiguous. So the bank did not know which table to look for, the occurrence or provide. I modified the query and tested it here, test it again ;)
0
You can use COUNT() together with GROUP BY to return. In your case, it would be 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
Thus, the query will return for each occurrence the total of provisions (Count) when there is a relation between them, grouping by COD_OCORRENCIA.
Browser other questions tagged sql
You are not signed in. Login or sign up in order to post.
Put the structure and population simply: http://lorefnon.me/plain-text-table/
– rubStackOverflow