rendered paste bodyTEACHERS
+----------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+-------+
| name | varchar(50) | NO | | NULL | |
| birthyear | smallint(6) | NO | | 0 | |
| nationality | varchar(20) | NO | | NULL | |
| email | varchar(50) | NO | | NULL | |
| aftercare | tinyint(1) | YES | | NULL | |
| country_code | varchar(8) | NO | | NULL | |
| phone | varchar(22) | NO | | NULL | |
| calltime | varchar(40) | NO | | NULL | |
| from_time | varchar(4) | NO | | NULL | |
| until_time | varchar(4) | NO | | NULL | |
| timezone | char(3) | NO | | NULL | |
| location | smallint(6) | NO | | NULL | |
| status | tinyint(4) | NO | | 0 | |
| arrival | date | YES | | NULL | |
| gender | varchar(8) | NO | | NULL | |
| in_korea | varchar(10) | NO | | NULL | |
| inkorea | mediumtext | NO | | NULL | |
| university_education | mediumtext | NO | | NULL | |
| experience | mediumtext | NO | | NULL | |
| introduction | text | NO | | NULL | |
| advertise | varchar(50) | NO | | NULL | |
| ip | varchar(17) | YES | | NULL | |
| recruiter_notes | text | NO | | NULL | |
| pic_upload | varchar(60) | NO | | NULL | |
| submit_time | timestamp | YES | | NULL | |
| start_date | date | NO | | NULL | |
| teacher_id | varchar(36) | NO | PRI | NULL | |
| recruiter | varchar(20) | YES | | NULL | |
| date_of_birth | date | YES | | NULL | |
+----------------------+-------------+------+-----+---------+-------+
29 rows in set (0.10 sec)
SENT MATCH
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| teacher_id | varchar(36) | NO | | NULL | |
| job_id | varchar(36) | NO | | NULL | |
| teacher_response | tinyint(1) | YES | | NULL | |
| job_response | tinyint(1) | YES | | NULL | |
| teacher_notes | text | NO | | NULL | |
| job_notes | text | YES | | NULL | |
| deleted | tinyint(1) | NO | | 0 | |
| success | tinyint(4) | NO | | 0 | |
+------------------+-------------+------+-----+---------+-------+
8 rows in set (0.02 sec)
SCHOOL JOBS:
+---------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+-------+
| contact | varchar(19) | NO | | NULL | |
| school | varchar(29) | NO | | NULL | |
| arrival | date | YES | | NULL | |
| recruiter_notes | text | NO | | NULL | |
| email | varchar(40) | NO | | NULL | |
| phone | varchar(40) | NO | | NULL | |
| foreign_teacher | varchar(200) | NO | | NULL | |
| school_description | text | NO | | NULL | |
| postal_address | varchar(200) | NO | | NULL | |
| city | smallint(6) | NO | | 0 | |
| website_address | varchar(40) | NO | | NULL | |
| ticket_money | tinyint(1) | NO | | 0 | |
| accomodation | tinyint(4) | NO | | 0 | |
| salary | smallint(6) | NO | | 0 | |
| kindergarten | tinyint(1) | NO | | 0 | |
| elementary | tinyint(1) | NO | | 0 | |
| middle_school | tinyint(1) | NO | | 0 | |
| high_school | tinyint(1) | NO | | 0 | |
| adults | tinyint(1) | NO | | 0 | |
| number_teachers | tinyint(4) | NO | | 0 | |
| teacher_description | text | NO | | NULL | |
| gender | varchar(4) | NO | | NULL | |
| contract | varchar(60) | NO | | NULL | |
| picupload | varchar(60) | NO | | NULL | |
| status | smallint(6) | NO | | 0 | |
| school_jobs_id | varchar(36) | NO | PRI | NULL | |
| calendar_date | varchar(10) | NO | | NULL | |
+---------------------+--------------+------+-----+---------+-------+
27 rows in set (0.00 sec)
QUERY : Match the teachers to the jobs where the cols match, and where the match hasn't already been made and entered into the sent_match table ...
SELECT teachers.name, teachers.arrival AS teachers_arrival,
teachers.location, school_jobs.school_jobs_id,
sent_match.job_id, school_jobs.school,
school_jobs.city, school_jobs.arrival AS school_jobs_arrival,
teachers.teacher_id, teachers.status, school_jobs.status
FROM school_jobs
LEFT JOIN sent_match ON school_jobs.school_jobs_id = sent_match.job_id, teachers
WHERE teachers.arrival
BETWEEN DATE_SUB( school_jobs.arrival, INTERVAL 20 DAY )
AND DATE_ADD( school_jobs.arrival, INTERVAL 20 DAY )
AND ( teachers.location = school_jobs.city OR teachers.location = '1' )
AND teachers.status != -1
AND school_jobs.status != -1
AND ( teachers.teacher_id != sent_match.teacher_id
OR sent_match.teacher_id IS NULL)
AND (school_jobs.school_jobs_id != sent_match.job_id
OR sent_match.job_id IS NULL)