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 :
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.