Left Outer Join Pulling Only Contained Data

2

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;
    
asked by anonymous 04.10.2018 / 16:54

1 answer

1

Following the user's bfavaretto , along with some refactoring and optimization:

SELECT      XC.crs
        ,   XM.nome
        ,   XV.visitador
        ,   XF.familia
        ,   IFNULL(XG.nome, '') AS NomeGestante
FROM        X_Familia   XF
INNER JOIN  X_Visitador XV  ON  XV.visitador    = XF.visitador
LEFT JOIN   X_Municipio XM  ON  XM.municipio    = XV.municipio
INNER JOIN  X_Crs       XC  ON  XC.crs          = XM.crs
LEFT JOIN   X_Gestante  XG  ON  XG.familia      = XF.familia
                            AND XG.situacao     = '1'
                            AND XG.statusErro   IS NULL 
WHERE       XV.situacao     = '1' 
        AND XF.situacao     = '1' 
ORDER BY    XC.crs
        ,   XM.nome
        ,   XV.visitador
        ,   XF.familia
    
04.10.2018 / 17:11