CODEWITHSUNDEEP
mysqlsqljoin
Mahmudul Hasan Shafin
Mahmudul Hasan Shafin

Reputation: 159

join to fetch users who do not have a busy date--the available users

I am trying to join two tables User and Busy Days. I want to fetch all the users who do not have a busy date--the available users.

User

user_id username
1 John
2 Doe

Busy Days

id busy_date user_id
1 2022-05-26 1
2 2022-05-26 2
3 2022-05-29 1
4 2022-06-01 2

I want to search by date. If 2022-05-26 the result should be zero because both users have a busy day on that date, but if 2022-05-27 both users should appear.

Upvotes: 1

Views: 82

Answers (2)

Abra
Abra

Reputation: 20914

Works for me with not exists.

select U.USER_ID
  from USER U
 where not exists (select 1
                     from BUSY_DAYS B
                    where B.USER_ID = U.USER_ID
                      and B.BUSY_DATE = 'some date')

Note: Replace some date with your actual date, e.g. 2022-05-26.
Refer to this db<>fiddle

Upvotes: 2

amirhosein hadi
amirhosein hadi

Reputation: 436

 select username from user
 where id not in (select user_id from busy_days where busy_date = "2022-05-26")

Upvotes: 2

Related Questions