Re: SQL to Join 2 tables and insert results into 3rd table

[Tristram Shorter]

Hi Michele!

I'm a newby myself, but as I see nobody has answered you, here is my answer:
I think your query would go something like this:

For employee 100...

First: Delete from my_calendar_on_call where user_id="100"; --> to wipe out any previous entries for this employee

Second:

Insert into my_calendar_on_call select "100", global_date from global_calendar left join my_calendar_off as off on gobal_date=date_off and off.user_id="100" where date_off IS NULL and month(global_date)=9 and year(global_date)=2015;

You'd have to repeat this for each employee. Ideally you would want to loop through each employee in the my_calender_off table, and this would be quite easy to do if your database was powering a site using php, for instance. I don't know how to do it with pure mysql however.

On the other hand, if you just want to check which days any employee (say "104") should be working, you could skip creating a third table altogether and use:

select global_date as Work_Day from global_calendar left join my_calendar_off on gobal_date=date_off and user_id="104" where date_off IS NULL and month(global_date)=9 and year(global_date)=2015;

Hope this helps.

Regards,

Tristram