[SOLVED] Complex Query Help

[cmgmyr]

I have a mail table:

CREATE TABLE `mail` (
  `mid` int(11) NOT NULL auto_increment,
  `parentid` int(11) NOT NULL default '0',
  `u_to` int(11) NOT NULL default '0',
  `u_from` int(11) NOT NULL default '0',
  `subject` varchar(100) NOT NULL default '',
  `message` text NOT NULL,
  `status` tinyint(1) NOT NULL default '0',
  `status2` tinyint(1) NOT NULL default '1',
  `date_sent` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`mid`)
) ENGINE=MyISAM;

INSERT INTO `mail` (`mid`, `parentid`, `u_to`, `u_from`, `subject`, `message`, `status`, `status2`, `date_sent`) 
VALUES (1, 0, 1, 306, 'Hi!', 'This is the first message', 1, 1, '2007-08-10 00:11:44'),
(2, 1, 306, 1, '', 'This is the second', 1, 1, '2007-08-10 00:12:06'),
(3, 2, 1, 306, '', 'This is the third', 1, 1, '2007-08-10 00:12:35');

 

status is for the u_to, status2 is for u_from

the status codes are 0=unread, 1=read, 2=deleted

 

What I want to do is make a query that if one user deleted a message, the other user can still view it (if they didn't delete it). So in the exampe above both users read the thread. if you change status on mid #1 to '2' user number 1 shouldn't see it. User #306 should still be able to see it. How would I create this query?

 

I have this so far:

SELECT * FROM `mail` WHERE (u_to = $userid OR u_from = $userid) AND status != 2 AND parentid = 0 ORDER BY date_sent DESC

but this doesn't show the message to the opposite user that deleted it.

 

Please let me know if you need any more info.

Thanks!

[GingerRobot]

Unless i missunderstood, dont you need to check that status is not 2 if its the user is who the message was to, and check status2 if the message was from the user? I think this might be what you are after:

 

SELECT * FROM `mail` WHERE ((`u_to` = $userid AND `status`!=2) OR (`u_from` = $userid AND `status2` != 2)) AND `parentid` = 0 ORDER BY `date_sent` DESC

[cmgmyr]

Cool, that did the trick! I think I was reading into it a little more then I should have. Thanks!