Count total where owner_user_id = some_value and deleted_by_owner = 0 or to_user_id = some_value and deleted_by_to = 0

Question

I didn't know if this is possible using just a query or if I need some procedural logic on this one.

I have this table:

CREATE TABLE `default_messages_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message_id` int(11) NOT NULL,
  `owner_user_id` mediumint(8) NOT NULL,
  `to_user_id` mediumint(8) NOT NULL,
  `read_message` tinyint(1) NOT NULL DEFAULT '0',
  `date_read` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_by_owner` tinyint(1) NOT NULL DEFAULT '0',
  `deleted_by_to` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `message_id_owner_user_id_to_user_id_UQ`
      (`message_id`,`owner_user_id`,`to_user_id`)
) ENGINE=MyISAM 
  AUTO_INCREMENT=2 
  DEFAULT CHARSET=utf8 
  COLLATE=utf8_unicode_ci

And a simple record for testing purpose:

INSERT INTO `default_messages_users` (`id`, `message_id`, `owner_user_id`, 
    `to_user_id`, `read_message`, `date_read`, `deleted_by_owner`, `deleted_by_to`) 
VALUES
    (1, 1, 1, 2, 0, '2012-09-04 14:09:14', 0, 0);

For the query:

SELECT Count(*) 
FROM default_message_users
WHERE owner_user_id = @SomeValue and deleted_by_owner = 0 
    or to_user_id = @SomeValue and deleted_by_to = 0

If @SomeValue = 1 then the query should return this row if owner_user_id = 1 && deleted_by_owner = 0 or to_user_id = 1 && deleted_by_to = 0, is the same as owner_user_id == to_user_id == 1 but always checking in pairs as explained before.

Is that possible using just a query or do I need programming help here?

Answer 1

Unless I'm misunderstanding your question, I think this solves your problem:

SELECT Count(*) 
FROM default_message_users
WHERE (owner_user_id = @SomeValue and deleted_by_owner = 0)
    or (to_user_id = @SomeValue and deleted_by_to = 0)

Note the addition of ( and ) to denote the order of operations for the AND and OR operators.

EDIT: As andry-m pointed out in the comments below my answer, order of operations dictates that AND conditions will be evaluated prior to OR conditions. My intention with this answer is that in more complicated cases it is easier to explicitly define the desired logic rather than to rely on the implicit order of operations.