How to delete all orphan records in MySQL?

Question

I have 3 tables MySQL (MyIsam):

user (id), message (id, userId, ...), archivedMessage (id, userId, ...)

How can I delete all the users having no message AND no archivedMessage?

Answer 1

You could use not exists:

delete from user
where not exists (select * from message m where m.userid = user.id)
      and not exists (select * from archivedMessage am where am.userid = user.id)