I need to select the Plaque, Employee Name, and Dependent Amount for each Employee, but the result of that query is showing the number of dependents for the first Employee only, / p>
Note: If you remove the WHERE clause, the query shows the amount of all dependents in the dependent first-employee only table.
Tables: COLLABORATOR and DEPENDENT
SELECT C.CHAPA, C.NOMECOLABORADOR,
COUNT(D.NOMEDEP) AS QNT_DEP
FROM COLABORADOR C, DEPENDENTE D
WHERE D.CHAPA=C.CHAPA;
The way you're doing the query, the COUNT() function will count all the returned records.
However, you want to count dependents for each employee, so you need to group the results with the GROUP BY clause. This way the COUNT() function will count the dependents within that defined group, instead of all the returned records.
It would look like this:
SELECT C.CHAPA, C.NOMECOLABORADOR, COUNT(D.NOMEDEP) AS QNT_DEP
FROM COLABORADOR C, DEPENDENTE D
WHERE D.CHAPA = C.CHAPA
GROUP BY C.CHAPA, C.NOMECOLABORADOR;
This is the form dictated by the SQL92 (SQL language revision) standard, which says that the fields that appear in the SELECT clause should also appear in the GROUP BY clause, if it exists. But MySQL can follow the SQL99 standard, depending on how it is set up ( documentation / a>), and then you could just use the CHAPA field to do the grouping:
SELECT C.CHAPA, C.NOMECOLABORADOR, COUNT(D.NOMEDEP) AS QNT_DEP
FROM COLABORADOR C, DEPENDENTE D
WHERE D.CHAPA = C.CHAPA
GROUP BY C.CHAPA;