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

user_12388078 0
Viewed 59

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.

join mysql sql
3 Answers
 select username from user
 where id not in (select user_id from busy_days where busy_date = "2022-05-26")
user_168347660

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

user_21643650

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

user_155547500
Related