hello - i'm using mysql version 4.0.20 - i asked this question in a couple of other forums, but i'm afraid i made it too long-winded, and haven't received any responses : ( - i really need to get this figured out - so i will try to shorten it here:
I want to perform a typical "cascade" type delete across 3-4 tables for an image gallery type application: My current approach is to Delete using inner joins, as i have built referential integrity into each table when i designed them. The following SQL ALMOST does what i want but not quite: (my current logic is to get it working as a SELECT, and then just switch it to a DELETE) The value for topcat.id will be passed to the PHP script as a variable - in this case hard-coded to 1
select topcat.,subcat1.,subcat2.,imageattributes. from topcat,subcat1,subcat2,imageattributes where topcat.id = 1
AND
topcat.id = subcat1.topcat_id
AND
subcat1.id = subcat2.subcat1_id
AND (
(imageattributes.subcat1entry = 1 AND imageattributes.subcat1or2_id = subcat1.id)
OR
(imageattributes.subcat2entry = 1 AND imageattributes.subcat1or2_id = subcat2.id)
)
the problem is (hope i say this right) - if there is NO id in a child table that refers back to a parent table, it will obviously not be returned by this query, however i STILL want to delete the parent value. For example take this part of the statement:
"AND
subcat1.id = subcat2.subcat1_id"
this says a row in subcat2 (column name subcat1_id) must exist which matches at least one row under the ID column of subcat1 - what i'm saying is even if none such values exist in subcat2, i STILL want to delete the subcat1 entry....
Right now i'm thinking from what i've read that InnoDB is the key to this thing, but I would very much appreciate any help you could provide.
