How to select objects if not exist between a period in Sqlite-CodePudding

I like to select those users who haven't filled out a form in the last 7 days but I'm stuck. The background: I am working on an app that lists the users who have filled out the form but I wrote it in another query that works fine. Now I need to select just those users who haven't filled the form out in the last 7 days. The query I wrote selects all the users because everyone has objects that outside the period. How can I select just those users who haven't filled out the form in the given period but not to include all users. As you can see on the picture the user with id 1 appears two times with Yes and No.

Tha query I wrote:

SELECT DISTINCT  auth_user.id,
   CASE WHEN felmeres.date BETWEEN date("now", "-7 day") AND date('now') 
   THEN 'Yes'
   ELSE 'No'
   END AS period
FROM felmeres
LEFT JOIN profile ON profile.user_id = felmeres.user_name_id
ORDER BY felmeres.date DESC

CodePudding user response：

You could use a join aggregation approach:

SELECT p.user_id
FROM profile p
INNER JOIN felmeres f
    ON f.user_name_id = p.user_id
GROUP BY p.user_id
HAVING SUM(f.date BETWEEN date('now', '-7 day') AND date('now')) = 0;

CodePudding user response：

If profile contains the users' data then it should be the left table in the LEFT join and the condition for the dates should be placed in the ON clause, so that you filter out the matching users:

SELECT p.*
FROM profile p LEFT JOIN felmeres f
ON f.user_name_id = p.user_id AND f.date BETWEEN date(CURRENT_DATE, '-7 day') AND CURRENT_DATE 
WHERE f.user_name_id IS NULL;

Or, with NOT EXISTS:

SELECT p.*
FROM profile p 
WHERE NOT EXISTS (
  SELECT 1
  FROM felmeres f
  WHERE f.user_name_id = p.user_id AND f.date BETWEEN date(CURRENT_DATE, '-7 day') AND CURRENT_DATE
);