MySQL Complex Queries for Distributing Data across Multiple Tables (PHP)

Question

I am currently trying to solve a complex problem with MySQL and PHP.

Here is an example of the tables that I 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 | 

The following table stores which customer has which services (one or more), the status of the service and the date it was completed, 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 the hard part. Some services should have a certain time difference with others. For example, one client cannot receive seeds if he received fertilizer in the last 30 days. To track this, I have a third table with 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 that everything is stored in the database (keep in mind that there may be dozens of services and thousands of customers), I try to get client lines with certain services or not, always observing the time difference.

For instance:

I want all the client that should receive the Fertilizer to 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 I want to do all the clients that should get Fertilizer AND 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 I want to do all the clients that should receive seeds in ZIP code 12235:

 Client_ID | Client_Name | Zip Code | Job_ID | Service_ID | Service_Name | ----------|-------------|----------|--------|------------|--------------| 3 | Allan | 12235 | 6 | 3 | Fertilizer | 

Please note that Mark is not included because it does not meet the requirements of 30 days since the last fertilizer service.

I tried many different options with all kinds of JOINS, but never found a solution that would work as described. The closest I got is to generate subqueries with PHP and join them in a large request.

For example, one of my attempts looked like this (for the last expected result above):

 SELECT c.Client_ID, c.Client_Name, c.Zip_Code, j.Job_ID, s.Service_ID, s.Service_Name FROM clients c LEFT JOIN jobs j ON j.Client_ID = c.Client_ID LEFT JOIN services s ON s.Service_ID = j.Service_ID WHERE s.Service_ID = "1" && c.Zip_Code = "12235" && c.Client_ID NOT IN ( SELECT Client_ID FROM jobs WHERE Status = "done" && Date_Done < (UNIX_TIMESTAMP() - 2592000) ) 

• Please note that a subquery was generated by a PHP script that searches for the restrictions corresponding to the requested service and the minimum time difference for this service, since there can be several restrictions for the same service, and I do not know if I can do this in pure SQL.

Now the request shown above works for this exact scenario (although it is very slow), it breaks, and I could not adapt it to my other needs (several services included or excluded).

Tell me if you need any other information or if you are ready to discuss it further.

Thanks to everyone who read the whole question (very long), and I hope that some of you will understand my needs and help me!

Answer 1

I found the answer to my question thanks to Waygood's help, which helped me understand the various uses of the ON LEFT JOIN clause.

Here is a query that fulfills all the needs we listed:

 # Select desired columns SELECT j.`Job_ID`, j.`Client_ID`, c.`Name`, j.`Service_ID`, s.`Service_Name`, FROM ( jobs j, clients c, Services s ) # Use LEFT JOIN to filter out services that meet the constraints LEFT JOIN ( time_difference diff, jobs j2 ) ON ( j.`Service_ID` = diff.`Service_ID_1` && j.`Client_ID` = j2.`Client_ID` && diff.`Service_ID_2` = j2.`Service_ID` && j2.`Date_Done` > (UNIX_TIMESTAMP() - diff.`Time_Diff`) && j2.`Status` = 'done' ) # Use LEFT JOIN to filter out jobs that have specific restrictions LEFT JOIN jobs_constraints jconst ON ( j.`id` = jconst.`Job_ID` && jconst.`value` > UNIX_TIMESTAMP() ) #Add one LEFT JOIN for every service that a client must or must not have LEFT JOIN jobs j3 ON ( j.`Client_ID` = j3.`Client_ID` && j3.`Status` = "active" && j3.`Service_ID` = "1" ) LEFT JOIN jobs j4 ON ( j.`Client_ID` = j4.`Client_ID` && j4.`Status` = "active" && j4.`Service_ID` = "2" ) LEFT JOIN jobs j5 ON ( j.`Client_ID` = j5.`Client_ID` && j5.`Status` = "active" && j5.`Service_ID` = "3" ) # END automatically generated blocks WHERE j.`Status` = "active" && j2.`Job_ID` IS NULL && j.`Client_ID` = c.`Client_ID` && j.`Service_ID` = s.`Service_ID` && jconst.`id` IS NULL # Add one AND clause for every service that a client must # or must not have use IS NULL if client must not have service # or IS NOT NULL if client must have && j3.`Job_ID` IS NULL && j4.`Job_ID` IS NOT NULL && j5.`Job_ID` IS NOT NULL 

This request still requires a bit of PHP to generate, but it is pretty simple, and there are only 2 areas that need to be configured on the fly.

I added a few other parameters as my question allows more control, but it still revolves around the same idea of ​​using multiple parameters in the ON LEFT JOIN clause that I am doing.

Thanks again Waygood for your help.

Answer 2

The following may be useful:

This will withstand outstanding orders (no limit)

 SELECT * FROM table_jobs AS T_job, table_services AS T_ser, table_clients AS T_cli WHERE T_job.Client_ID=T_cli.Client_ID AND T_job.Service_ID=T_ser.Service_ID AND T_job.Status='active' 

This should infer the order that previously placed orders with restrictions. BUT Time_Diff should be in days (i.e. Delete d )

 SELECT * FROM (table_jobs AS T_job, table_services AS T_ser, table_clients AS T_cli) LEFT JOIN (table_time_difference AS T_dif, table_jobs AS T_ojobs) ON ( AND T_job.Service_ID=T_dif.Service_ID_1 AND T_dif.Service_ID_2=T_ojob.Service_ID AND T_ojobs.Date_Done > DATE_SUB(CURDATE(), INTERVAL T_dif.Time_Diff DAY) AND T_ojobs.Status='done' ) WHERE T_job.Client_ID=T_cli.Client_ID AND T_job.Service_ID=T_ser.Service_ID AND T_job.Status='active' AND T_ojobs.Job_ID IS NULL 

You can then add your additional parameters for the error spread or zip code at the end, using T_job, T_ser or T_cli as table names. (i.e. not T_ojobs or T_dif)