Hi guys,
I have three tables, <teacher>, <school_jobs>, <sent_match>. I'm trying to build a query that will match teachers to school_jobs based on approximate arrival time and on location. But only if I haven't "matched" the teacher to the school beforehand...
Whenever I make a match, I enter the teacher_id and the school_job_id into the sent_match table, which signifies that they've been matched.
So bascially I'm looking for matches between teachers and jobs that exclude already sent_matches...
TABLE SCHEMA - something like this
SENT_MATCH
| 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 | |
TEACHERS
| name | varchar(50) | NO | | NULL | |
| birthyear | smallint(6) | NO | | 0 | |
| nationality | varchar(20) | NO | | NULL | |
| location | smallint(6) | NO | | NULL | |
| status | tinyint(4) | NO | | 0 | |
| teacher_id | varchar(36) | NO | PRI | NULL | |
SCHOOL JOBS
| contact | varchar(19) | NO | | NULL | |
| school | varchar(29) | NO | | NULL | |
| arrival | date | YES | | NULL |
| status | smallint(6) | NO | | 0 | |
| school_jobs_id | varchar(36) | NO | PRI | NULL | |
| city | smallint(6) | NO | | 0 | |
MYSQL QUERY
This has been my extremely poor attempt to get this thing to fit so far ...
"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 ( teachers JOIN school_jobs
ON (
( teachers.location = school_jobs.city OR teachers.location = 1 )
AND
( teachers.arrival
BETWEEN DATE_SUB( school_jobs.arrival, INTERVAL 20 DAY )
AND
DATE_ADD( school_jobs.arrival, INTERVAL 20 DAY )
)
)
LEFT JOIN sent_match ON (
( school_jobs.school_jobs_id = sent_match.job_id )
AND
( teachers.teacher_id = sent_match.teacher_id )
)
)
WHERE teachers.status != -1 AND school_jobs.status != -1 " ;
I have three tables, <teacher>, <school_jobs>, <sent_match>. I'm trying to build a query that will match teachers to school_jobs based on approximate arrival time and on location. But only if I haven't "matched" the teacher to the school beforehand...
Whenever I make a match, I enter the teacher_id and the school_job_id into the sent_match table, which signifies that they've been matched.
So bascially I'm looking for matches between teachers and jobs that exclude already sent_matches...
TABLE SCHEMA - something like this
SENT_MATCH
| 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 | |
TEACHERS
| name | varchar(50) | NO | | NULL | |
| birthyear | smallint(6) | NO | | 0 | |
| nationality | varchar(20) | NO | | NULL | |
| location | smallint(6) | NO | | NULL | |
| status | tinyint(4) | NO | | 0 | |
| teacher_id | varchar(36) | NO | PRI | NULL | |
SCHOOL JOBS
| contact | varchar(19) | NO | | NULL | |
| school | varchar(29) | NO | | NULL | |
| arrival | date | YES | | NULL |
| status | smallint(6) | NO | | 0 | |
| school_jobs_id | varchar(36) | NO | PRI | NULL | |
| city | smallint(6) | NO | | 0 | |
MYSQL QUERY
This has been my extremely poor attempt to get this thing to fit so far ...
"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 ( teachers JOIN school_jobs
ON (
( teachers.location = school_jobs.city OR teachers.location = 1 )
AND
( teachers.arrival
BETWEEN DATE_SUB( school_jobs.arrival, INTERVAL 20 DAY )
AND
DATE_ADD( school_jobs.arrival, INTERVAL 20 DAY )
)
)
LEFT JOIN sent_match ON (
( school_jobs.school_jobs_id = sent_match.job_id )
AND
( teachers.teacher_id = sent_match.teacher_id )
)
)
WHERE teachers.status != -1 AND school_jobs.status != -1 " ;