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

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.

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

For joining two tables

SELECT * FROM User INNER JOIN Busy_Days ON User.user_id=Busy_Days.user_id;

Then to achieve the result

SELECT * FROM User INNER JOIN Busy_Days ON User.user_id=Busy_Days.user_id WHERE NOT Busy_date=2022-05-27;

SELECT * FROM User INNER JOIN Busy_Days ON User.user_id=Busy_Days.user_id WHERE NOT Busy_date=2022-05-26;

Hope this helps you if you have any query regarding this please feel free comment