Delete from multiple tables

[deshg]

Hey everyone,

I have a messaging system setup using two mysql tables, one for the message (inc text, subject, sender info etc) and another for the recipient info (containing messageid and recipientid) which allows for multiple recipients. I am trying to write a query to enable the deletion of a user (which in turn will delete all their messages). Obviously if i do:

$queryg = "DELETE TMessages.*, TMessageRecipients.* FROM TMessages, TMessageRecipients WHERE TMessages.Fid=TMessageRecipients.FMessageID AND TMessageRecipients.FUserID='$uid'";

Where uid is their userid then it will delete all related records in both tables (so all messages to and from them). The problem is this will also delete messages that were sent to them AND other people. Is there anyway to only delete records if the associated record in TMessageRecipients is unique (ie there isn't another record in there with the same MessageID)?

If that's not entirely clear then do ask me to clarify anything! Thanks so much for your help, i think there must be a way to do it, but i'm not sure how!

Dave

[webdevnoob]

You can query out all the records where each has a unique MessageID as follows:

SELECT FUserID FROM TMessages GROUP BY MessageID HAVING count(MessageID) ='1'

You can then loop through the results and do a delete for each one.

Jeff,
http://www.webdevnoobs.com

[webdevnoob]

Or instead of doing a delete every loop, you can use the loop to build your final delete critera, i.e., end up with something like this

WHERE TMessageRecipients.FUserID='1' OR TMessageRecipients.FUserID='2' OR TMessageRecipients.FUserID='3' OR etc

[dagon]

--- WHERE TMessageRecipients.FUserID in(1,2,3 ..) --- would be easier to build

[webdevnoob]

dagon;10957584 wrote:

--- WHERE TMessageRecipients.FUserID in(1,2,3 ..) --- would be easier to build

yep I second that.