Wrestling with SQL

Question

I have this in a MySQL db:

table Message
    sender_id int
    recipient_id int
    created datetime
    [title, body, etc... omitted]

Is it possible to get from this, in a single query, a list of all the users who have been communicating with a given user with id N (meaning N appears in either sender_id or recipient_id), ordered by created, including the most recent value of created, without duplicates (no sender/recipient pairs N, M and M, N)?

I'm pretty sure the answer is No, but I'm having trouble convincing myself. Afternoon mental dullness.

If No, what's the most efficient alternative you would recommend?

---

EDIT: holy crap, Stack Overflow is hopping these days. 8 answers before I realize I forgot to specify that I would like to have the most-recent value of created present along with the other user ID in each row.

Answer 1

No, it's pretty simple to do.

SELECT DISTINCT IF(sender_id=4, recipient_id, sender_id) AS partner_id
FROM message
WHERE sender_id=4 OR recipient_id=4

Answer 2

Obviously you're having trouble convincing yourself, otherwise you wouldn't have asked. And I am pretty sure the answer is Yes. Yet, without knowing some particularities about the underlying data, it's a bit hard to find the right solution.

It probably goes into the direction of

select 
  sender_id,
  recipient_id
from
  message
where
 (sender_id    = n or 
  recipient_id = n   )
order by
  created desc

Answer 3

Try this

SELECT distinct recipient_id
FROM Message
WHERE sender_id = @id

UNION

SELECT distinct sender_id
FROM Message
WHERE recipient_id = @id

The union clause will remove duplicates across the two queries. Actually looking at it you won't need the distinct's either as the union will do that for you too (anyone any idea if it's more efficient to use distinct to pre-filter the clauses or just let the union handle all the duplication?)

Answer 4

Use:

SELECT x.friend,
       MAX(x.created)
  FROM (SELECT t.recipient_id 'friend',
               t.created
          FROM MESSAGE t
         WHERE t.sender_id = @id 
        UNION
        SELECT r.sender_id 'friend',
               r.created
          FROM MESSAGE r
         WHERE r.recipient_id = @id) x
GROUP BY x.friend

Answer 5

I think this will meet your requirements most:

SELECT *
FROM user
WHERE 
  user_id in 
    (select recipient_id FROM message m where m.sender_id = X)
  OR user_id in
    (select sender_id FROM message m where m.recipient_id = X)
order by
  created DESC

There are no duplicates, no unions and no SQL dialect specific stuff.

Answer 6

Hi,

Here is mine :)

select id, max(created) as lastMessage
(
    select sender_id as id, created
    from Message
    union
    select recipient_id as id, created
    from Message
) as MergedMessage
where id = ?id
group by id
order by max(created) desc

That's it :

• without duplicates
• order by created desc