Complex MySQL queries on data spread over multiple tables (PHP) -

i trying solve complex problem mysql , php.

here example of tables have:

list of clients:

table_clients client_id | client_name | address         | zip code | ----------|-------------|-----------------|----------| 1         | mark        | 127 park ave    | 12235    | 2         | john        | 6 freeman drive | 12899    | 3         | allan       | 450 clever rd   | 12235    | 

list of services:

table_services service_id | service_name | service_price | -----------|--------------|---------------| 1          | fertilizer   | 100.00        | 2          | bug spray    | 50.00         | 3          | seeds        | 20.00         | 

next table stores client has services (one or more), status of service , date done, if applicable:

table_jobs job_id | client_id | service_id | status | date_done  | -------|-----------|------------|--------|------------| 1      | 1         | 1          | done   | 2013-05-01 | 2      | 1         | 3          | active | null       | 3      | 2         | 1          | active | null       | 4      | 2         | 2          | active | null       | 5      | 3         | 1          | active | null       | 6      | 3         | 3          | active | null       | 

now comes tricky part. services need have time difference others. example, 1 client can't receive seeds if received fertilizer in last 30 days. keep track of this, have third table information:

table_time_difference service_id_1 | service_id_2 | time_diff | -------------|--------------|-----------| 1            | 3            | 30d       | 1            | 4            | 7d        | 2            | 4            | 14d       | 4            | 5            | 14d       | 

now stored in database (keep in mind there can dozens of services , thousands of clients), trying rows of clients have services or not, while respecting time difference.

for example:

i want client due receive fertilizer, should return:

client_id | client_name | zip code | job_id | service_id | service_name | ----------|-------------|----------|--------|------------|--------------| 2         | john        | 12235    | 3      | 1          | fertilizer   | 3         | allan       | 12145    | 5      | 1          | fertilizer   | 

now if want clients due receive fertilizer , bug spray:

client_id | client_name | zip code | job_id | service_id | service_name | ----------|-------------|----------|--------|------------|--------------| 2         | john        | 12235    | 3      | 1          | fertilizer   | 2         | john        | 12235    | 4      | 2          | bug spray    | 

and if want clients due receive seeds in zip code 12235:

client_id | client_name | zip code | job_id | service_id | service_name | ----------|-------------|----------|--------|------------|--------------| 3         | allan       | 12235    | 6      | 3          | fertilizer   | 

notice how mark isn't included doesn't meet 30 days requirements since last fertilizer service.

i have tried many different options sorts of joins, never found solution work described. closest have gotten generating sub-queries php , them joining them in big query.

for example, 1 of attempts looked (for last expected result above):

select c.client_id,        c.client_name,        c.zip_code,        j.job_id,        s.service_id,        s.service_name clients c left join jobs j     on j.client_id = c.client_id left join services s     on s.service_id = j.service_id s.service_id = "1"    && c.zip_code = "12235"    && c.client_id not in (                           select client_id                           jobs                           status = "done"                              && date_done < (unix_timestamp() - 2592000)                          ) 

• note subquery has been generated php script lookup restrictions corresponding service requested , minimum time difference service since there can multiple restriction same service , don't know if can in pure sql.

now, query shown above work exact scenario (although slow), breaks , haven't been able adapt fit other needs (multiple services included or excluded).

tell me if need other information or if open discussing further.

thank has read through whole question (very long) , hope of understand needs , can me!

the following may of help:

this pull outstanding orders (without restrictions)

select * table_jobs t_job, table_services t_ser, table_clients t_cli t_job.client_id=t_cli.client_id , t_job.service_id=t_ser.service_id , t_job.status='active' 

this should pull order have done orders restrictions time_diff should in days (ie remove d)

select * (table_jobs t_job, table_services t_ser, table_clients t_cli) left join (table_time_difference t_dif, table_jobs t_ojobs) on (     , t_job.service_id=t_dif.service_id_1     , t_dif.service_id_2=t_ojob.service_id     , t_ojobs.date_done > date_sub(curdate(), interval t_dif.time_diff day)     , t_ojobs.status='done' ) t_job.client_id=t_cli.client_id , t_job.service_id=t_ser.service_id , t_job.status='active' , t_ojobs.job_id null 

you can add additional parameters bug spray or zip code @ end, using t_job, t_ser or t_cli table names. (ie not t_ojobs or t_dif)