List users which have not connected by date-CodePudding

I have a table login

Idlogin  userid. Date
1.        12A.   22/03/2021
2.        12B.   22/04/2021
3.        12A.   29/03/2021

I have a table users that contains a list of all the users

Iduser
12A
12B
23k
Jk8
Kl9

And a table dimdates that contains all dates from 2018

Date
01/01/2018
02/01/2018 till now

I need to list all users that have not connected in all the dates available in the table date

CodePudding user response：

Cartesian Join to list all users with all dates and use NOT EXIST to exclude the rows using login table:

select u.Iduser, d.date from 
dimdates d,
users u
where not exists 
(
select 1 from login l
where l.date = d.date
and l.Idlogin=u.Iduser
)

CodePudding user response：

Anti-semi join:

select * 
from [users] U
where not exists
(
    select 1
    from [login] L
    inner join dimdates D on L.[Date]=D.[Date]
    where L.Idlogin=U.Iduser
)