Fetch users who read the bible yesterday but not today in mysql-CodePudding

Hello I want to fetch users who read the chapter yesterday but not today. i have one table called user_bible_trackings that is below.

 user_id     date_read
   2          2021-11-25
   2          2021-11-26
   1          2021-11-22
   1          2021-11-23
   3          2021-11-25

My expected output is like below:-

user_id
  3

I have tried below query

SELECT DISTINCT(user_id) FROM `user_bible_trackings` 
WHERE  date_read = '2021-11-25' and date_read != '2021-11-26'

But its coming wrong output. Can anyone tell me what i am doing wrong

CodePudding user response：

You could use:

SELECT user_id
FROM user_bible_trackings ubt1
WHERE date_read = CURDATE() - INTERVAL 1 DAY AND
      NOT EXISTS (SELECT 1 FROM user_bible_trackings ubt2
                  WHERE ubt2.user_id = ubt1.user_id AND
                        ubt2.date_read = CURDATE());

Note that I chose to use CURDATE() to represent today. You could continue to hard code dates in the above query and it would work, but CURDATE() is more flexible.

CodePudding user response：

This could be an option:

SELECT user_id
FROM user_bible_trackings
WHERE date_read IN ( CURDATE(), CURDATE() - INTERVAL 1 DAY )
GROUP BY user_id
HAVING MAX(date_read) = CURDATE() - INTERVAL 1 DAY