Show only those that are not common between two querys in mysql

Navigation
0

I have this first query that only shows names with the registration date of the current day: 

SELECT nome,
       centrodb.registoMiDe.Quarto,
       DataRegisto,
       Miccao,
       Tipo1,
       Dejeccao,
       Tipo

FROM centrodb.utentes LEFT OUTER JOIN centrodb.registoMiDe

ON centrodb.registoMiDe.NomeUtente = centrodb.utentes.nome

WHERE descricaovalencia = 'LAR' AND nome <> 'CLASSE' AND DAY(DataRegisto) = DAY(CURDATE())

Now in the next query I show all the names with the registration date null and days behind the current date: 

SELECT nome,
       centrodb.registoMiDe.Quarto,
       DataRegisto,
       Miccao,
       Tipo1,
       Dejeccao,
       Tipo

FROM centrodb.utentes LEFT OUTER JOIN centrodb.registoMiDe

ON centrodb.registoMiDe.NomeUtente = centrodb.utentes.nome

WHERE descricaovalencia = 'LAR' AND nome <> 'CLASSE' AND DataRegisto IS NULL OR DAY(DataRegisto) < DAY(CURDATE())

Now the goal is just to show names that have not yet been registered with the current day's record date.

    
asked by anonymous 12.04.2018 / 15:56

1 answer

1

If I understand correctly, just make a NOT EXISTS of the query that you do not want to appear in the result: 

    SELECT nome,
           centrodb.registoMiDe.Quarto,
           DataRegisto,
           Miccao,
           Tipo1,
           Dejeccao,
           Tipo

    FROM centrodb.utentes LEFT OUTER JOIN centrodb.registoMiDe

    ON centrodb.registoMiDe.NomeUtente = centrodb.utentes.nome

    WHERE descricaovalencia = 'LAR' AND nome <> 'CLASSE' AND DataRegisto IS NULL OR DAY(DataRegisto) < DAY(CURDATE()) 
    AND NOT EXISTS (SELECT 1

                    FROM centrodb.utentes  
                    LEFT OUTER JOIN centrodb.registoMiDe    
                    ON centrodb.registoMiDe.NomeUtente = centrodb.utentes.nome    
                    WHERE descricaovalencia = 'LAR' 
                    AND nome <> 'CLASSE' 
                    AND DAY(DataRegisto) = DAY(CURDATE()))
    
12.04.2018 / 16:56
How to get value from an Html.EditorFor Command does not work when executed via cronjob