Two tables (simplified, because other fields are not used in query; indexes
other than primary key removed):
CREATE TABLE `msg_content` (
`msg_id` int(14) NOT NULL auto_increment,
`subject` varchar(255) NOT NULL default '',
`content` mediumtext NOT NULL,
PRIMARY KEY (`msg_id`),
) TYPE=InnoDB COMMENT='contains actual content of messages';
CREATE TABLE `msg_addressee` (
`id` int(14) NOT NULL auto_increment,
`account_id` int(14) NOT NULL default '0',
`msg_id` int(14) NOT NULL default '0',
`status` set('deleted','replied','forwarded','admin') default NULL,
PRIMARY KEY (`id`),
) TYPE=InnoDB COMMENT='link table to link accounts to messages';
A message is inserted once in the `msg_content` table and for each
from/to/cc/.. an entry is inserted in the `msg_addressee` table.
If someone deletes the message from his/her mailbox the entry in the
`msg_addressee` table is marked 'deleted' by setting the `status` field
accordingly.
I want to do some garbage collection and find the messages for which *all*
entries in the msg_addressee table have the status field set to 'deleted'
This is what I came up with:
SELECT t1.`msg_id`
FROM `msg_content` AS t1
JOIN `msg_addressee` AS t2 ON t1.`msg_id` = t2.`msg_id` AND
FIND_IN_SET( t2.`status` , 'deleted' ) >0
LEFT JOIN `msg_addressee` t3 ON t1.`msg_id` = t3.`msg_id` AND
FIND_IN_SET( t3.`status` , 'deleted' ) =0
GROUP BY t2.`msg_id` , t3.`msg_id`
HAVING COUNT( t3.`msg_id` ) =0
First I JOIN the tables to find the records which have at least one entry in
the msg_addressee table set to 'deleted' and then I select the records which
have no entries in the msg_addressee table that are *not* set to 'deleted'.
I've tried a few other queries, but this was the only one that seems to
work...
Anybody have shorter/faster ideas (whithout changing the table structure)?
Does this query result in false positives or false negatives?
Thanks in advance. Regards, Jigal.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
