CODEWITHSUNDEEP
sqlsql-server
Conk1
Conk1

Reputation: 105

How to find a list of non-matching records in SQL? (Having some trouble)

I have 2 tables.

Users - has 1 row per user with these columns:

This shows the last time someone used their key somewhere in the building.

Events - has multiple rows per user, logs every event with these columns:

This shows every instance where someone used their key. An EventTypeID of 70 is when someone enters the building or "clocks in".

I'm trying to make a query that shows people who are using their key but did not use their key to register their entrance on a certain day.

The following will be true for users who HAVE done this properly:

But what I need is a list of the people who are not obeying the rule of the building.

Thank you for any help in advance.

Upvotes: 2

Views: 589

Answers (4)

Kostis
Kostis

Reputation: 963

You need to join the tables and get the UserIDs that don't have a record in the Events table meeting your rules 

SELECT Users.userID 
FROM Users
LEFT JOIN Events ON Users.userID = Events.UserID
    AND Events.EventTypeID = 70 
    AND Events.EventTime >= CONVERT(DATE, GETDATE())
WHERE EventID IS NULL 
    AND Users.LastAccessTime >= CONVERT(DATE, GETDATE())

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Your question suggests NOT EXISTS or NOT IN. I think this is the logic that you want:

Users.LastAccessTime > '2017-02-28' (aka today's date) Most recent case of Events.EventTypeID = 70 will also have Events.EventTime > '2017-02-28' on the same row

select u.*
from users u
where u.LastAccessTime > cast(getdate() as date) and  -- in the building today
      not exists (select 1
                  from events e
                  where e.UserId = u.UserId and
                        e.EventTypeID = 70 and
                        e.EventTime > cast(getdate() as date)
                 );

This returns people who entered today but don't have an event 70 for today.

Upvotes: 2

Tom J Muthirenthi
Tom J Muthirenthi

Reputation: 3340

Is this you want: 

SELECT U.USERID FROM USERS U
LEFT JOIN EVENTS E
ON
U.USERID=E.USERID
WHERE (E.USERID IS NULL OR U.LastAccessTime <> '2017-02-28' OR E.EventTypeID <>70 OR E.EventTime <> '2017-02-28') --if any of the above condition is Violated, their ID will be dispalyed in the select clause

Upvotes: 0

Badro Niaimi
Badro Niaimi

Reputation: 957

If I understood you question, I think you are looking for this query:

select * 
from Users as u,Event as e
where u.UserID=e.UserID and 
     (Users.LastAccessTime < CONVERT (date, GETDATE()) Or 
      Events.EventTypeID <> 70 or  
      Events.EventTime < CONVERT (date, GETDATE()));

the first condition in where I used for join the two tables. and the other conditions I used I opposite of what is supposed to happened with OR between them. so the result if there is a user who didn't obey to one or more condition he will be selected by the query.

Upvotes: 0

Related Questions