mostly an sql question

rhaggert

i have 5 tables
employees
ej_rels (employee job relationship table)
jobs
cj_rels (client job relationship table)
clients

im quit new to mysql and php

im trying to get an sql statment to select all jobs that the current loged in employee is NOT working on

SELECT jobs., ej_rels., employees.first_name FROM jobs LEFT JOIN ej_rels ON jobs.id=ej_rels.jobs_ref LEFT JOIN employees ON employees.id=ej_rels.employees_ref WHERE employees.id!= $employee_id;

this is what i got so far but it doesnt work because the result returns jobs that other employees are working on wihich is ok BUT what if the employee is working on a job with soem one else. that result is returned. If i do an sql to join all the tables i will get a shared job as to rows. which is correct. im thinking i can do an OR in my WHERE to say "dont select any with the id of this employee OR any one who is working with them on a job.

been stupmed here for a bit. any hints help criticism or suggestions welcome

thanks

barand

As you are using mysql which does not support sub-queries you need to create a temporary table

SELECT ej_rels.Emp_ref,ej_rels.Job_ref INTO temp
FROM ej_rels
WHERE ej_rels.Emp_ref=$empcode

then

SELECT Job.Jobid, Job.Jobcode
FROM ej_rels INNER JOIN Job ON ej_rels.Job_ref = Job.Jobid) LEFT JOIN temp ON ej_rels.Job_ref = temp.Job_ref
WHERE (((ej_rels.Emp_ref)<>$empcode) AND ((temp.Emp_ref) Is Null))
GROUP BY Job.Jobid, Job.Jobcode

I've tried on an access database and results look right. I couldn't remember your exact field names so some changes required but principle is sound, I think.

hth

rhaggert

now if i make a temp table. could this becme a problem. there is a php interface with this db and multiple employees could be doing the same thing, ie picking up jobs that they are not working on. could this be a problem having that temp table if someone puts info into that table and another employee is reading it iat that time.

barand

Perhaps if generate a temp table name with

list($temptablename,$dummy) = explode(" ",microtime());

then after the queries

mysql_query("DROP TABLE $temptablename");