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

Asked

Viewed 57 times

0

I have this first query that only shows the names with the current day registration 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 DAY(DataRegisto) = DAY(CURDATE())

Now in the next query I show all the names with the null registration date and the 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 only to show the names that have not yet been registered with the date of registration of the current day.

mysql

  • I didn’t quite understand your difficulty... It wouldn’t just be: DAY(DataRegisto) <> DAY(CURDATE()) ???

  • No and I will explain now, imagine if the same name exists with the registration of the current date, it will appear in it and what I want is that if that name exists with registration of the previous day and registration of the current day does not appear...?

  • I’ve solved the situation

1 answer

1


If I understand correctly, just make a NOT EXISTS of the query you don’t 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()))

  • This solution also works, but I used the NOT IN

  • 1

    Cool, really the NOT IN also works, but in any way rebounds that the NOT EXISTS is more performatic than the NOT IN.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.