CODEWITHSUNDEEP
mysqlsql
Zaki Aziz
Zaki Aziz

Reputation: 3852

Returning all rows with certain conditions

First of all let me give you guys the SQLFiddle: http://sqlfiddle.com/#!2/1d8bd/11

I've been working on this for a while and am facing some programmers (and writers) block.

This is a sample pseudo code of what I am trying to accomplish in MySQL:

Return all rows WHERE {
  IF `to_user_id` = '27' AND `to_delete` >= SUBDATE(NOW(), INTERVAL 720 HOUR) 
     RETURN to_delete as del_time, COUNT(*), MAX(subject),

      - OR -

  IF `from_user_id` = '27' AND `from_delete` >= SUBDATE(NOW(), INTERVAL 720 HOUR) )
     RETURN from_delete as del_time, COUNT(*), MAX(subject),
} GROUP BY thread_hash

I'm essentially building a private messaging system and this is the query that represents the trash bin. Rows with the same thread_hash represent a messages in a thread. When a user is deletes a thread from their inbox it sets all to_delete to NOW() for that particular thread_hash. When a user is deletes a thread from their sent-box it sets all from_delete to NOW() for that particular thread_hash.

The query I'm trying to build should return all rows if to_user_id = $user_id AND to_delete has a value OR if from_user_id = $user_id AND from_delete has a value, grouped by the thread_hash (ie threads you've deleted from your inbox, or sent-box)

I hope I'm being clear here. Please let me know if I can clear anything else up.

Upvotes: 1

Views: 76

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180867

You're close, a simple UNION ALL should do what you want;

SELECT MAX(del_time), COUNT(*) cnt, MAX(subject) subject FROM (
  SELECT to_delete del_time, thread_hash, subject
  FROM messages
  WHERE to_user_id = 27 AND `to_delete` >= SUBDATE(NOW(), INTERVAL 720 HOUR)
  UNION ALL
  SELECT from_delete del_time, thread_hash, subject
  FROM messages
  WHERE from_user_id = 27 AND `from_delete` >= SUBDATE(NOW(), INTERVAL 720 HOUR)
) a
GROUP BY thread_hash;

The modified SQLfiddle for testing.

Upvotes: 1

Related Questions