How to remove duplicates in mysql query?

Question

MySQL

There is a table

id |  from_id | to_id 
--------------------
1  | 5  | 6
2  | 5  | 7
3  | 5  | 8
4  | 6  | 5
5  | 8  | 6

It is necessary to get from it all from_id & to_id associated with 5, i.e.
user with id 5 wrote a message to user 7, user 8 and received, for example, a message from 7 and 6.
You need to find out all users, in other words, display who he communicated with

Answer 1

SELECT distinct to_id FROM messages WHERE from_id = 5
SELECT distinct from_id FROM messages WHERE to_id = 5

Read more here: www.mysql.ru/docs/man/DISTINCT_optimisation.html
One query can be done through UNION (combining multiple queries)

SELECT to_id as user_id FROM messages WHERE from_id = 5
UNION
SELECT from_id as user_id FROM messages WHERE to_id = 5

More about UNION and DISTINCT with his participation here: www.mysql.ru/docs/man/UNION.html
And one more way with the usual DISTINCT in one query:

SELECT
    DISTINCT IF(to_id = 5, from_id, to_id) as user_id
FROM messages
WHERE from_id = 5
    OR to_id = 5

For those who are too lazy to read links: DISTINCT in many cases expands to GROUP BY and uses a temporary table. Conclusion - if it is important to filter out duplicates only in a particular column, then:
Counting the extra unread column and displaying the date of the last post :

SELECT
    IF(to_id = 5, from_id, to_id) as user_id,
    SUM(unread) as count_unread,
    MAX(create_date) as last_create_date
FROM messages
WHERE from_id = 5
    OR to_id = 5
GROUP BY user_id

Add LEFT JOIN (although you shouldn't use them)

SELECT
    IF(m.to_id = 5, m.from_id, m.to_id) as user_id,
    SUM(m.unread) as count_unread,
    MAX(m.create_date) as last_create_date,
    u.name,
    u.login
FROM messages as m
    LEFT JOIN users as u ON (u.user_id = m.from_id OR u.user_id = m.to_id)
WHERE m.from_id = 5
    OR m.to_id = 5
GROUP BY user_id

Answer 2

select from_id from table where to_id = 5
union
select to_id from table where from_id = 5