Mysql compare different rows-CodePudding

i have the following table id | user | date | value. I need to find users that have values on eg October month but they dont have on November month. I ve tried the following code around.

select *
from users B 
LEFT OUTER JOIN data A 
on A.user = B.user
Where B.permissions=1 AND A.date BETWEEN '2021-11-01' AND '2021-11-30' group by A.user

CodePudding user response：

I would use exists logic here:

SELECT d1.user  -- add more columns here if desired
FROM data d1
WHERE d1.date BETWEEN '2021-11-01' AND '2021-11-30' AND
      NOT EXISTS (SELECT 1 FROM data d2
                  WHERE d2.user = d1.user AND
                        d2.date BETWEEN '2021-10-01' AND '2021-10-31');

CodePudding user response：

select *
from users as usr
where permissions = 1
  and exists (     -- have data for october 2021
    select 1
    from data AS d
    where d.user = usr.user
      and d.date BETWEEN '2021-10-01' AND '2021-10-31' 
  )
  and not exists ( -- no data for november 2021
    select 1
    from data AS d
    where d.user = usr.user
      and d.date BETWEEN '2021-11-01' AND '2021-11-30' 
  )