How use correctly a condition with dates in sql?

Question

I have this query:

SELECT
    t1.usu_id,
    t2.usu_nombre,
    t2.usu_apellido
FROM
    act_regisdiario t1
INNER JOIN
    act_usuario t2
ON
    (t2.usu_id = t1.usu_id)
WHERE
     t1.rdi_fechacti != '2016-06-02 00:00:00' 
 AND t2.usu_admin = 0
GROUP BY 
    t2.usu_id

and the query work with 2 tables:

act_regisdiario:

and act_usuarios:

So i want yo select the users that have a different rdi_fechacti. And in this case is : 2016-06-02 00:00:00

But this show me all usu_nombre , but only Jean have 2016-06-02 00:00:00

I need only show Jean.

Sorry my english.

Answer 1

All the users have at least one row that meets your condition.

Instead use not exists:

SELECT u.*
FROM act_usuario u
WHERE NOT EXISTS (SELECT 1
                  FROM act_regisdiario r
                  WHERE u.usu_id = r.usu_id AND
                        r.rdi_fechacti = '2016-06-02 00:00:00'
                 )
      u.usu_admin = 0;

Notice that you don't need aggregation for this either.

Answer 2

You query was asking for any that had any without that datetime, this attempts to JOIN with records with that datetime and only accepts ones that "failed":

SELECT DISTINCT
t2.usu_id,
t2.usu_nombre,
t2.usu_apellido
FROM act_usuario t2
LEFT JOIN act_regisdiario t1
ON (t2.usu_id = t1.usu_id AND t1.rdi_fechacti = '2016-06-02 00:00:00')
WHERE t1.rdi_fechacti IS NULL and t2.usu_admin = 0
ORDER BY t2.usu_id
;

Answer 3

Change your WHERE clause to this:

WHERE t1.rdi_fechacti < '2016-06-02 00:00:00' OR t1.rdi_fechacti > '2016-06-02 23:59:59'
    and t2.usu_admin = 0

This says to exclude anything with a t1.rdi_fechacti == '2016-06-02 00:00:00' and it doesn't require a huge change to your starting code. You should be able to just update the date portion of the DATETIME to search for any others that may fit in your window of exclusion.

I hope this helps.

-C§