Reputation: 75
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
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
Reputation: 426
Select UserTable.Id, name
From UserTable left join AttendanceTable
On UserTable.Id=AttendanceTable.Id
Where date! =#2019-02-18#
Upvotes: -1
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
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