Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Thanks @max-vernon it works didn't see it before –  ReynierPM Sep 12 '12 at 2:46
    
That's strange. In such cases as this, the parentheses usually do not affect the result. –  Andriy M Sep 12 '12 at 13:03
    
@AndriyM - you are correct - in a simple statement such as this order of operations indicates to evaluate the AND conditions prior to the OR conditions. So this statement without the ( and ) actually evaluates to the same result set. My intention was to point out that in more complicated cases it is easier to explicitly define the desired logic rather than to rely on the implicit order of operations. –  Max Vernon Sep 12 '12 at 15:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.