[RESOLVED] could you give me some advice for DELETE with JOIN

steamPunk

I have 4 tables containing records that are all associated and I need to delete data from all the tables as follows :

delete photographer using his id
delete all productions associated with that photographer
delete all images associated with those productions
delete all sales associated with those images

I figured that the correct sql would be :

	$sql = "DELETE photo, production, image, sales FROM photo AS t1 
	LEFT JOIN production AS t2.WHERE t2.fo_id=t1.fo_id
	LEFT JOIN image AS t3 WHERE t3.prod_id=t2.prod_id
	LEFT JOIN sales AS t4 WHERE t4.image_id=t3.image_id
	WHERE t1.fo_id='".$fo_id."'";

but I have two questions :

should I be using INNER JOIN or RIGHT JOIN instead of LEFT JOIN ?
is there any way of doing a sort of simulated delete where I can output all the records that would be deleted (without actually deleting them) so that I can check that my sql is only going to delete the right data ?
I suppose that would be too good to be true, but you never know !!

thanks for any help or advice you may have on this

laserlight

Have you considered the use of foreign key constraints? You can specify that the deletion of the photographer will cascade in deletions of every row in other tables associated with the photographer.

steamPunk

laserlight;10912616 wrote:
Have you considered the use of foreign key constraints? You can specify that the deletion of the photographer will cascade in deletions of every row in other tables associated with the photographer.

ooh I've never heard of that - sounds good, I'll check it out

thanks 🙂

NogDog

Note that if using MySQL, the tables involved must be InnoDB tables for foreign keys to work.

PS: As far as your mutli-table delete syntax, the "WHERE" should be "ON" for the JOIN statements.

steamPunk

NogDog;10912624 wrote:
Note that if using MySQL, the tables involved must be InnoDB tables for foreign keys to work.

ok I'll do that, thanks

NogDog;10912624 wrote:
PS: As far as your mutli-table delete syntax, the "WHERE" should be "ON" for the JOIN statements.

yes - a silly mistake from writing it out too quickly and not testing it before posting :o

steamPunk

I've been reading up on foreign key constraints and it sounds like just what i need - however, it raises a question that i haven't found an answer for :

in your earlier post you said that using constraints means that i can cascade the delete across all four tables with the photographer as the root - this is excellent, but does it become obligatory that all data is deleted if i delete a photographer ?

the problem is that a production (the second table) can have 1 or more photographers associated with it - so I'll be checking if the photographer i want to delete shares any productions with other photographers. If this is the case then I only want to delete the photographer data but not the productions/images/sales (as they are still linked to other photographers)

having just written that, I think i may use the constraints only from the productions level down through the images and sales. that way I can get a list of the productions for the photographer and check which ones are shared and deal with the deletes accordingly.

but still, i'd be interested to know the answer to my question

laserlight

steamPunk wrote:
in your earlier post you said that using constraints means that i can cascade the delete across all four tables with the photographer as the root - this is excellent, but does it become obligatory that all data is deleted if i delete a photographer ?

Yes, it does.

steamPunk wrote:
the problem is that a production (the second table) can have 1 or more photographers associated with it - so I'll be checking if the photographer i want to delete shares any productions with other photographers. If this is the case then I only want to delete the photographer data but not the productions/images/sales (as they are still linked to other photographers)

That is a many to many relationship: a photographer can work on many productions and a production can be worked on by many photographers. As such, the deletion cascade will only affect the third table used to express the many to many relationship, so there is no problem.

EDIT:
Okay, there might be one problem: since the deletion cascade will not directly affect the production table, you can have the case where a production is left with no photographers. Aside from the use of say, a trigger or another SQL statement, I am not sure how to avoid this.