How to properly exclude rows with references to each other?

Question

This question is based on the not very trivial question How to remove two duplicate column. I already suggested solution on that question, but I think there is some more suitable and elegant solution than mine.

There is some table of private messages with columns msg_id, from, to.
And we have this data in it:

msg_id from   to
----------------
1      46     0
2      46     18
3      46     50
4      46     39
5      46     11  
6      11     46
7      46     12
8      46     56
9      46     11

We need to exclude rows with the conversations, in which there are more than one message (like rows with msg_id = 5, 6 and 9) and in the same time we need to leave first row in output among these rows. In general output should be like this (note: without msg_id = 6 and msg_id = 9):

msg_id from   to
----------------
1      46     0
2      46     18
3      46     50
4      46     39
5      46     11  
7      46     12
8      46     56

My solution is:

select distinct pm.`from`, pm.`to`
from `tsk_private_message` pm
left join
    (select distinct pm.`from`, pm.`to`
     from `tsk_private_message` pm
     inner join `tsk_private_message` pm2
     on (pm.`to` = pm2.`from`) and (pm2.`to` <> pm.`from`)) a
     using (`from`, `to`)
where a.`from` is null;

I just search unnecessary rows among these conversations via subquery and "subtract" result from the main table. What do you think? Is there more elegant and more simple solution? I just really don't like this tricky code.

Here is SQL Fiddle

wildplasser · Accepted Answer · 2013-12-09T23:23:46.120

SELECT mx.msg_id, pm.ffrom, pm.tto
FROM tsk_private_message pm
WHERE NOT EXISTS (
    SELECT * FROM tsk_private_message nx1
    WHERE nx1.ffrom = pm.ffrom AND nx1.tto = pm.tto
    AND nx1.msg_id < pm.msg_id
    )
AND NOT EXISTS (
    SELECT * FROM tsk_private_message nx2
    WHERE nx2.ffrom = pm.tto AND nx2.tto = pm.ffrom
    AND nx2.msg_id < pm.msg_id
    );

Note: I renamed the to and from columns to tto and ffrom, because to and from both are keywords in SQL and I don't like quoting identifiers.

Extra: sqlfiddle (courtesy of Alexander Myshov)

score 1 · Answer 2 · answered Dec 08 '13 at 10:36

SELECT *
FROM
  tsk_private_message INNER JOIN (
    SELECT MIN(id) min_id
    FROM tsk_private_message
    GROUP BY
      LEAST(`from`, `to`),
      GREATEST(`from`, `to`)) min_msg
  ON tsk_private_message.id = min_msg.min_id
ORDER BY
  id

Please see fiddle here.

How to properly exclude rows with references to each other?

2 Answers2