I have a table called exam and another call questions.
These tables are related to the idexamen.
My query is to show the name exam and the questions it contains:
SELECT examen.idexamen,examen.detalles,count(*)
from examen,modulo
where examen.modulo = modulo.idmodulo
and modulo.profesor ="00000100p"
group by examen.idexamen
HAVING count(*) IN
(SELECT count(*) from preguntas
group by preguntas.examen)
When doing this query, it does not show me anything, I do not see the error. Any solution?
You can do the job using INNER JOIN and changing the grouping of the field preguntas.examen to examen.idexamen :
SELECT
examen.idexamen,
examen.detalles,
count(*) total
FROM modulo
INNER JOIN examen
ON examen.modulo = modulo.idmodulo
INNER JOIN preguntas
ON preguntas.examen = examen.idexamen
WHERE
modulo.profesor = "00000100p"
GROUP BY
examen.idexamen
I recommend that for performance issues you use as external query the one you use as a restriction in WHERE to avoid looking for unnecessary relationships that will be filtered later.
Also, to detect the case in which there is no record to show, you can add the typical loop while (.. = ..->fetch()) a fetchAll with a if to detect the absence of data and a foreach to replace to while :
$f = $consulta->fetchAll();
if (count($f) == 0) {
$cadena .= "<tr><td colspan='4'>No hay registros</td></tr>\n";
} else {
foreach ($f as $f1) {
$cadena .= "<tr><td>" . htmlspecialchars($f1['idexamen']) . "</td><td>" . htmlspecialchars($f1['detalles']) .
"</td><td>" . htmlspecialchars($f1["total"]) . "</td><td><a href='mostrarexamen.php?id=" .
urlencode($f1["idexamen"]) . "'><span class='glyphicon glyphicon-eye-open icon1'></span></a> <a href='editarpreguntas.php?id=" .
urlencode($f1["idexamen"]) . "'><span class='glyphicon glyphicon-pencil icon1'></span></a></td></tr>";
}
}
Try this SELECT using LEFT JOIN.
SELECT e.idexamen, e.detalles, count(*) 'Total preguntas'
FROM examen e LEFT JOIN modulo m ON e.modulo = m.idmodulo
LEFT JOIN preguntas p ON e.idexamen = p.idexamen
WHERE m.profesor ="00000100p" GROUP BY e.idexamen