I'm trying to do this research to know how many children and pregnant women each visitor attends but is only pulling the families that have pregnant even putting the blessed LEFT OUTER JOIN
, is because of the ON
clause?
I can not think of another way because only the children and pregnant tables are linked by the family, do you have an alternative to pull all the families having or not pregnant?
SELECT X_Crs.crs
, M.nome
, X_Visitador.visitador
, X_Familia.familia
, X_Gestante.nome
FROM X_Familia
, X_Visitador
LEFT JOIN X_Municipio M ON M.municipio = X_Visitador.municipio
INNER JOIN X_Crs ON X_Crs.crs = M.crs
LEFT OUTER JOIN X_Gestante ON X_Gestante.familia = X_Familia.familia
WHERE X_Visitador.situacao = '1'
AND X_Familia.situacao = '1'
AND X_Gestante.situacao = '1'
AND X_Gestante.statusErro IS NULL
AND X_Familia.visitador = X_Visitador.visitador
ORDER BY crs
, M.nome
, visitador
, familia;