Users No records in BD

Navigation
0

I have 2 tables, one of users with id autoincrement 

ID_usuario|nombre

and another where their accesses are registered. 

ID_acesso|ID_usuario|fecha_hora_registro

How can I get the users who do not have any registration during the 3 days after the date of consultation in the database?

that is, today is April 18, then show the users that do not show records as of April 15

Hello, in advance, thank you very much for your help I have this query 

SELECT
    (
    CASE
        WHEN (SELECT date_format(checkin.fecha_check_in,"%Y-%m-%d") FROM checkin WHERE checkin.usuario_id = 1080  ORDER BY checkin.fecha_check_in DESC LIMIT 1) <= DATE(timestamp(DATE_SUB( NOW() , INTERVAL 96 HOUR ) )) THEN (SELECT date_format(checkin.fecha_check_in,"%Y-%m-%d") FROM checkin WHERE checkin.usuario_id = 1080  ORDER BY checkin.fecha_check_in DESC LIMIT 1)
        WHEN (SELECT date_format(checkin.fecha_check_in,"%Y-%m-%d") FROM checkin WHERE checkin.usuario_id = 1080  ORDER BY checkin.fecha_check_in DESC LIMIT 1) >= DATE(timestamp(DATE_SUB( NOW() , INTERVAL 96 HOUR ) )) THEN 1
        WHEN (SELECT date_format(checkin.fecha_check_in,"%Y-%m-%d") FROM checkin WHERE checkin.usuario_id = 1080  ORDER BY checkin.fecha_check_in DESC LIMIT 1) IS NULL THEN 0
    END) AS last_check

the problem is that I execute it once for every id I have

    
asked by EDER AVENDAÑO 18.04.2018 в 21:54
source

1 answer

0

You can get the result in the following way: 

SELECT * 
FROM usuario
WHERE id NOT IN(
  SELECT usuario_id
  FROM checkin
  WHERE fecha_check_in >= ( CURDATE() - INTERVAL 3 DAY )
);
    
answered by 19.04.2018 в 17:17
Php odbc postresql Match content type with blocks of views for drupal 8