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
@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?
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
) to denote the order of operations for the
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.