Как убрать дубликаты в mysql запросе?

Question

[exxagw]

Есть таблица

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

Нужно из нее получить все from_id & to_id связанные с 5, т.е.

пользователь с id 5 написал сообщение пользователю 7, пользователю 8 и получил, например, сообщение от 7 и 6
Нужно узнать всех пользователей, проще говоря вывести с кем он общался

Answer 1

[Oleg Shevelev]

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

Почитайте более подробно тут: www.mysql.ru/docs/man/DISTINCT_optimisation.html

Одним запросом можно сделать через UNION (объединение нескольких запросов)

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

Подробнее про UNION и DISTINCT с его участием тут: www.mysql.ru/docs/man/UNION.html

Если не используется ключевое слово ALL для UNION, все возвращенные строки будут уникальными, так как по умолчанию подразумевается DISTINCT для всего результирующего набора данных. Если указать ключевое слово ALL, то результат будет содержать все найденные строки из всех примененных команд SELECT.

И ещё один способ с обычным DISTINCT одним запросом:

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

Для тех кому лень читать ссылки: DISTINCT во многих случаях разворачивается в GROUP BY и использует временную таблицу. Вывод - если важно отфильтровать дубли только в какой-то конкретной колонке то:

1. можно указать distinct у конкретного поля (distinct from_id), не для этого случаях, вспомнить подходящий с ходу не удалось
   
2. можно указать GROUP BY и перечислить нужные поля не указывая DISTINCT
   

Подсчёт дополнительной колонки unread и вывод даты последнего сообщения:

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

Добавляем LEFT JOIN (хотя их использовать и не стоит)

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

Comments

[exxagw]

Считает не правильно, у меня unread может быть 1 или 0. А он выводит кол-во сообщений вообще.

[exxagw]

исправил это на sum, спасибо!

[Oleg Shevelev]

exxagw: поправил:)

[exxagw]

Oleg Shevelev: еще вопросик, у меня leftjoin выдает ошибку куда не сую. Нужно зайти в таблицу `test_users` по user_id = `test_users`.`id` и выбрать поле `username`

[Oleg Shevelev]

Сделайте LEFT JOIN отдельным запросом, что бы не гонять кучу дублей (если подставить LEFT JOIN в выше-описанный вариант то будет вначале выборка, а потом GROUP BY)
Впрочем, можно сделать вложенный SELECT, почитайте тут: www.mysql.ru/docs/man/ANSI_diff_Sub-selects.html

[Oleg Shevelev]

Так же можно сохранить первую выборку во временную таблицу и сделать LEFT JOIN по ней.

Если LEFT JOIN не желателен то временную таблицу можно обновить через INNER JOIN пропуская те строчки где соответствий нету и забрать готовые данные уже из временной таблицы.

[exxagw]

Oleg Shevelev: Можно пример? пишу после всего этого select * from.... выдает ошибку

[exxagw]

Oleg Shevelev: SELECT
IF(`to_id` = 5, `from_id`, `to_id`) as `user_id`,
sum(`unread`) as `unread`,
MAX(`createdon`) as `last`,
`modx_users`.`username` as `name`
FROM `modx_messenger_messages`
LEFT JOIN `modx_users` ON `modx_messenger_messages`.user_id=`modx_users`.id
WHERE `from_id` = 5
OR `to_id` = 5
GROUP BY `user_id`

вот тут LEFT JOIN `modx_users` ON `modx_messenger_messages`.user_id=`modx_users`.id не понимет **.user_id

[Oleg Shevelev]

exxagw: написал пример в ответе. В кратце вот что не хватает вашему варианту:
1. когда используется несколько таблиц для однозначного определения к какой таблице относится колонка используется запись table.column
2. user_id от messages является вычисляемым, а в ON можно использовать только колонки, либо "вычислять" повторно опять таки в ON, я это сделал через условие аналогичное выборке.
ON (u.user_id = m.from_id OR u.user_id = m.to_id)

[Oleg Shevelev]

LEFT JOIN modx_users as u ON (u.id = m.from_id OR u.id = m.to_id)

Думаю так должно быть понятней для конкретно вашего примера.

[exxagw]

Oleg Shevelev: спасибо, работает. но есть проблема, не правильно считает sum теперь. показывает 4 а должно быть 2, без джойнов показывает 2.

SELECT
IF(m.to_id = 5, m.from_id, m.to_id) as user_id,
SUM(m.unread) as count_unread,
MAX(m.createdon) as last,
u.username
FROM `modx_messenger_messages` as m
LEFT JOIN `modx_users` as u ON (u.id = m.from_id OR u.id = m.to_id)
WHERE m.from_id = 5
OR m.to_id = 5
GROUP BY user_id

[exxagw]

из-за джойнов умножает все на 2 :с

[Oleg Shevelev]

exxagw: попробуйте решить это сами, а потом поделиться со всеми:)

Answer 2

[Петр]

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

Comments

[exxagw]

Огромное спасибо! Еще вопрос, еще там есть поле "unread" и у некоторых сообщений оно unread = 1. Каким образом можно вывести кол-во непрочитанных сообщений? получается count where unread =1... не силен

[exxagw]

блин, там еще и по дате последнего сообщения. оно в поле date... я наверно слишком много прошу :c

[Петр]

exxagw: Сделайте подсчет unread отдельным запросом.

[Oleg Shevelev]

По умолчанию в таком запросе название колонки будет взято из первого запроса (конкретно тут название будет from_id).
Некоторых это может поставить в тупик. По этому предпочтительнее указывать название через as:

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

[exxagw]

Oleg Shevelev: про подсчет unread кол-во и сортировку по дате последнего сообщения не ответите?

[Петр]

Oleg Shevelev: И что? Результат то не поменяется и mysql в тупик поставлен не будет

[Oleg Shevelev]

Петр: это упоминание было для удобства пользователей которые, возможно, будут читать этот вопрос.

[Oleg Shevelev]

exxagw: написал вам в своём ответе.