Delete Join Where Second Table Could Be Empty

[Greywacke]

hi there,i am trying to delete from two tables, one containing the leads and one containing the record of forwarding.however, this won't work if the second table returns an empty recordset.the left join seems to work, but it won't delete the record from the first table.here is the sql string:

DELETE FROM 10_serviceprospects LEFT JOIN 13_prospectleadsent ON 10_serviceprospects.bigint_ProspectID = 13_prospectleadsent.bigint_ProspectID WHERE 10_serviceprospects.bigint_ProspectID = 780;

a lead with id 780 does exist however.the query returns with the following error if i enable :

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 'LEFT JOIN

how could i modify this query to only attach the second table where that id exists? add a WHERE EXISTS subquery perhaps?how would i do this? i can't seem to find anything on the web...i guess i'm trying to do an optional delete join here.

[Greywacke]

well duh... i needed to use an OR not an AND originally...

DELETE FROM 10_serviceprospects, 13_prospectleadsent WHERE 10_serviceprospects.bigint_ProspectID = 780 OR 13_prospectleadsent.bigint_ProspectID = 780;

the 780 representing the ProspectID.