Mysql Help to Return the Unavailable Days to Schedule Time

Navigation
0

I have 2 mysql tables that represent the unavailable days for scheduling:

holidays table: id, day (date), name

Special_days table : id, day (date), will_work (boolean)

At the end I need to select the unavailable days which are the holidays, the special_days when they will_work. So far so good, however if there is a special_day in which the date is equal to some holiday and will_work is true I need to remove this date from the list.

So far I've only managed to: 

    SELECT day FROM holidays
    UNION SELECT day FROM special_days WHERE will_work = FALSE;

But I could not remove the holidays where there is some special_day with will_work is true and same day;

    
asked by anonymous 15.05.2018 / 14:15

2 answers

2
SELECT day
FROM holidays
where day not in (SELECT day FROM special_days WHERE will_work = TRUE)
UNION
SELECT day
FROM special_days
WHERE will_work = FALSE;
    
15.05.2018 / 14:45
0

And if you restrict the query by doing a subselect to filter only the days: 

 SELECT day 
   FROM holidays h 
  WHERE h,day in (SELECT day FROM special_days WHERE will_work = FALSE);
    
15.05.2018 / 14:43
query using Case when How to do the query select and call the connection on dao