Page 1 of 1

Join In a Delete Query

Posted: Sun Aug 09, 2009 11:31 am
by jackpf
Hello everyone :)

Right, I have my forum. For my forum, I have a "subscriptions" table, where users can subscribe to a thread, and be PMed when new replies are posted in it.

When threads are deleted, I attempt to delete all subscriptions as well, just to keep things quick and tidy. However, I've been trying to make a function to delete all subscriptions to non-existent threads, which can be say....run by an admin as part of an "Optimize tables" operation or something.

Anyway, the subscriptions table is a many to many relationship (I think :/). So multple users can subscribe to multiple threads, and multiple threads can have multiple subscriptions.

Now, Instead of selecting all subscriptions, and looping through them to see if the relating thread exists, I was hoping to achieve the same effect with a join.

I tried:

Code: Select all

DELETE S #delete from subscriptions (alias S)
FROM `Subscriptions` S #...
LEFT OUTER JOIN `Forum` F ON S.`Subscription_ID` = F.`ID` #join the forum table on the subscription id
WHERE S.`Subscription_ID`!=F.`ID`#delete where the subscription id does not match a forum id (does not work!)
Yeah, this doesn't work...
I've managed to successfully delete all subscriptions that do exist...but that's the opposite of the desired effect.

Anyway, any help would be awesome.
Thanks a lot,
Jack.

Re: Join In a Delete Query

Posted: Sun Aug 09, 2009 11:41 am
by Eran

Code: Select all

...
LEFT JOIN `Forum` F ON S.`Subscription_ID` = F.`ID`
WHERE F.`ID` IS NULL
should do it.

Re: Join In a Delete Query

Posted: Sun Aug 09, 2009 12:29 pm
by jackpf
Oh, awesome.

Thanks a lot pytrin; once again, you are my saviour :bow:



:P
Cheers,
Jack.

Re: Join In a Delete Query

Posted: Sun Aug 09, 2009 2:34 pm
by Eran
glad to be of help :)