Deejay
Deejay

Reputation: 75

Select users that are not present on a certain date

I have two tables, one for the users and the other for their attendance. I need to show the users that are not on the attendance table on a certain date.

Here are my tables:

USER_TABLE

| ID   | Name     |
-------------------
| 1    | John     |
| 2    | Peter    |
| 3    | Anne     |
| 4    | May      |
ATTENDANCE_TABLE

| ID   | Date       |
--------------------------------
| 2    | 2019-02-16 |
| 2    | 2019-02-17 |
| 2    | 2019-02-18 |
| 3    | 2019-02-17 |
| 4    | 2019-02-18 |

I need to select all the users that are not present on "2019-02-18".

So the result should look like this.

| ID   | Name     |
-------------------
| 1    | John     |
| 3    | Anne     |

Upvotes: 0

Views: 745

Answers (4)

Fahmi
Fahmi

Reputation: 37473

You can try using left join

SELECT a.id,name 
FROM USER_TABLE a
LEFT JOIN ATTENDANCE_TABLE b ON a.id=b.id
  AND b.date='2019-02-18'
AND b.id IS NULL

Upvotes: 0

Ali
Ali

Reputation: 426

Select UserTable.Id, name
From UserTable left join AttendanceTable
On UserTable.Id=AttendanceTable.Id
Where date! =#2019-02-18#

Upvotes: -1

Strawberry
Strawberry

Reputation: 33935

Drop table if exists my_users;

Create table my_users
(User_ID serial primary key
,Name varchar(100) not null unique
);

Insert into my_users values
(1,'John'),
(2,'Peter'),
(3,'Anne'),
(4,'May');

Drop table if exists my_ATTENDANCE;

Create table my_attendance
(user_ID INT NOT NULL
,Date date not null
,primary key(user_id,date)
);

Insert into my_attendance values
( 2    ,'2019-02-16'),
( 2    ,'2019-02-17'),
(2    ,'2019-02-18'),
(3    ,'2019-02-17'),
(4    ,'2019-02-18');

Select u.*
  From my_users u
  Left
  Join my_attendance a
    On a.user_id = u.user_id
   and a.date = '2019-02-18'
 Where a.user_id is null;

User_ID Name
      3 Anne
      1 John

https://rextester.com/YQZTM12580

Upvotes: 2

Santosh
Santosh

Reputation: 886

Try this query,

select ID, Name
from USER_TABLE UT
where ID not in (select ID from ATTENDANCE_TABLE where date = '2019-02-18')

Upvotes: 3

Related Questions