In article <[EMAIL PROTECTED]>,
"Jigal van Hemert" <[EMAIL PROTECTED]> writes:
> 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
Try the following:
SELECT t1.msg_id
FROM msg_content t1
LEFT JOIN msg_addressee t2 ON t1.msg_id = t2.msg_id
AND FIND_IN_SET ('deleted', t2.status) = 0
WHERE t2.id IS NULL
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
