Can someone please have a look at these to sql statements. I am getting the following error on the second one which is a copy of the first one other then replacing the select with Delete.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN (tbl_module INNER JOIN tbl_material ON tbl_module.module_id = tbl_mat' at line 2

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN (tbl_module INNER JOIN tbl_material ON tbl_module.module_id = tbl_mat' at line 2
Works:

SELECT tbl_material.date_stamp, tbl_material.part
FROM tbl_project
INNER JOIN (
tbl_module
INNER JOIN tbl_material ON tbl_module.module_id = tbl_material.module_id
) ON tbl_project.project_id = tbl_module.project_id
WHERE tbl_project.project = '3418499'
AND tbl_material.date_stamp <= DATE_SUB( NOW( ) , INTERVAL 60
MINUTE )

No Work

DELETE
FROM tbl_project INNER JOIN (tbl_module INNER JOIN tbl_material ON tbl_module.module_id = tbl_material.module_id) ON tbl_project.project_id = tbl_module.project_id
WHERE tbl_project.project='3418499'
AND tbl_material.date_stamp<=DATE_SUB(NOW(),INTERVAL 60 MINUTE)

DELETE
FROM tbl_project INNER JOIN (tbl_module INNER JOIN tbl_material ON tbl_module.module_id = tbl_material.module_id) ON tbl_project.project_id = tbl_module.project_id
WHERE tbl_project.project='3418499'
AND tbl_material.date_stamp<=DATE_SUB(NOW(),INTERVAL 60 MINUTE)

What I have detemined is that you can not use JOIN and Delete in the same statement. What you need to do is nest the queries so that each query is executed and the result is passed ot the next query.

$sql='DELETE FROM tbl_material WHERE tbl_material.module_id IN('.
'SELECT tbl_module.module_id FROM tbl_module WHERE tbl_module.project_id IN ('.
'SELECT tbl_project.project_id FROM tbl_project INNER JOIN tbl_project_details ON tbl_project.project_id = tbl_project_details.project_id'.
'WHERE project='.$project.' OR network_number='.$project.')'.
')'.
'AND tbl_material.date_stamp<=DATE_SUB(NOW(),INTERVAL 6 MINUTE)';

You can use a multi table delete using JOINs BUT the storage engine needs to be InnoDb

http://dev.mysql.com/doc/refman/5.0/en/delete.html

Just checked and all my tables are InnoDb. At one point I actually got an error that made some sense and indicated that I could not use delete files that were connected with the JOIN. Can't remember the wording exactly. Either way, it is working. Thanks for the commentary.

How common is InnoDB? Just how likely is it to already be configured for you, on the average web-host's server?

It's built in to EVERY MySql server, as are several storage engines InnoDb just needs to be set as default storage engine or set by a CREATE TABLESPACE or ALTER TABLESPACE command using the ENGINE directive

Oh okay... I remember this tripping me out when I ran into it before (with InnoDB)