deleting "orphan" rows from table

[ingernet]

Hey folks,

Any idea how I can do this? I've got two tables: subscriptions and subbedUsers.

here's subscriptions:
pk_sId
s_SubUId (this uses subbedUsers.pk_suId)
s_NId (this uses newsletters.pk_nId, not necessary to describe this here)

...and subbedUsers:
pk_suId
suEmail
suName

I'd like to periodically "clean house" on the subbedUsers table by deleting users who aren't subscribed to any of the newsletters managed. I've spent way too much time gnashing my teeth over this and would love to hear your ideas.

thanks,
inger

 

[sleipnir214]

Unfortunately, MySQL does not yet provide sub-selects in a way that would be useful to do the deletion in a single query.

However, the query:

select
pk_suId
from
subbedUsers
left join
subscriptions
on pk_suId = s_SubUId
group by
u.id
having
count(s_SubUId) = 0;

will provide you with a list of those subscriber IDs that have no current subscriptions. Using the return from that query and a loop in your scripting language would allow you to delete the users in question. Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!