MySQL multi table deletes when data doesn't always exist in all tables

MySQL mutli table deletes

[58sniper]

MySQL 4.1.x

I have the following tables:
psor_offenders
psor_offenses
psor_aliases

all have a common field, 'id'.

I need to delete from psor_offenses and psor_aliases where their respective ID field matches that in psor_offenders. In my test case, I'm limiting to those where psor_offenders.zip = '48313'.

I can do a delete on psor_offenses that match, or on psor_alises that match, but I can't seem to get it to work on both at the same time.

Using:
DELETE FROM psor_offenses, psor_aliases using psor_offenses, psor_aliases, psor_offenders WHERE psor_offenses.id = psor_offenders.id and psor_aliases.id = psor_offenders.id and psor_offenders.zip = '48313'

only appears to have deleted from the two tables when there were records in both with the same ID (a three-way match). I need to delete from both regardless of whether their are matches in the other (there is always a match in psor_offenses, but not always in psor_aliases).

Does this make sense? I'm new to multi table deletes, and haven't been able to figure this out.

[txbakers]

I'm not sure what you can delete if the match doesn't exist in the third table. The way your query is written is correct, (typo understood) so it will only delete where there is a match.

What do you want to delete if there isn't a match? Isn't that dangerous - you could delete everything that doesn't match.

You can also write three delete statements, which I always feel is safer.

[58sniper]

I don't want it to delete anything in the psor_aliases table if it doesn't match anything there. But I do want it to delete from EITHER table if there is a match with psor_offenders. Right now, it only deletes if it matches in all three tables.

I spent about 3 hours at the book store tonight reading through some stuff, and it looks like I need to explore outer joins.

[valder]

My brain is in stand-by atm, so I may be off target..
but will it help to switch the AND to OR in

psor_offenses.id = psor_offenders.id and psor_aliases.id = psor_offenders.id

in the WHERE clause?

[58sniper]

Actually, I tried that, and it just hung.

Currently, I have two deletes that do the job, I'm just looking for a way to combine them:

mysql_query("DELETE FROM psor_offenses using psor_offenses, psor_offenders WHERE psor_offenses.id = psor_offenders.id and psor_offenders.zip = '$zip'");

mysql_query("DELETE FROM psor_aliases using psor_aliases, psor_offenders WHERE psor_aliases.id = psor_offenders.id and psor_offenders.zip = '$zip'");

[txbakers]

just use two deletes. It's safer that way. With a database you want to be safe.

[58sniper]

I don't mind using the two deletes, except I have them in a loop, that ultimately, will have them run EACH more than 1000 times per day. I'm just trying to optimize things.