如何使用mysql查询查找用户信息

Question

I want to find user id and Email from mysql query

I have three table in mysql database

1. users 2. photographer_timetables 3.jobs

1.users

2.photographer_timetables

3 jobs

here is my query

   SELECT Users.id AS `Users__id`
      , Users.email AS `Users__email` 
   FROM users          Users 
   LEFT 
   JOIN photographer_timetables pt    
     ON (pt.user_id = users.id AND role =3) 
  RIGHT JOIN jobs jb 
     ON (jb.photographer_id = pt.user_id 
     AND HOUR(jb.job_time) !='12:00:00'  
     AND jb.job_date !='2016-03-21')    
 WHERE (HOUR(start_time) <= '08:00:00'  
    AND weekday = 1)

I want to find those users information who is not available in job_date and job_time

my sql query return blank but o/p should be at least on row

Thanks in advance

Answer 1

You can use NOT EXISTS.

Query

select * from users
where not exists (
  select 1 from jobs
  where jobs.user_id = users.id
);

Hope that once the job over, you are deleting the row from jobs table.

And if you want to check who all are available for a particular date. Then

Query

select * from users
where not exists (
  select 1 from jobs
  where jobs.user_id = users.id
  and jobs.job_date = '2016-03-23' -- change the date accordingly
);