Вопрос по сложным MySQL убрать дубли

270

26 апреля 2017, 13:00

Дано:

1) Две таблицы в базе

1.1 | id | contacts_name |
1.2 | id | id_contact | id_contact_friend

2) Контакты могут дужить друг с другом, но если А дружит с В, - это не значит, что В дружит с А.

Надо составить запрос, отображающий все пары контактов, которые дружат друг с другом, исключив дубликаты.

Я застопорился на этом:

SELECT Table1.contacts_name, Table2.contacts_name
FROM
    (SELECT C1.contacts_name, T1.id 
     FROM
        (SELECT cf1.id_contact, cf1.id
         FROM contacts_friend cf1, contacts_friend cf2
         WHERE cf1.id_contact=cf2.id_contact_friend AND cf2.id_contact=cf1.id_contact_friend
        ) T1,
        contacts C1
     WHERE
        T1.id_contact = C1.id
     ) Table1,
     (SELECT C2.contacts_name, T2.id
      FROM
        (SELECT cf1.id_contact_friend, cf1.id
        FROM contacts_friend cf1, contacts_friend cf2
        WHERE cf1.id_contact=cf2.id_contact_friend AND cf2.id_contact=cf1.id_contact_friend
        ) T2,
        contacts C2
      WHERE
        T2.id_contact_friend = C2.id
      ) Table2
WHERE Table1.id = Table2.id

Очень прошу помощи довести запрос до ума, Сейчас работает, но выдает дубли:

Maksim и Igor дружат друг с другом
Igor и Maksim дружат друг с другом
Olga и David дружат друг с другом
David и Olga дружат друг с другом
Den и John дружат друг с другом
John и Den дружат друг с другом

А должен выдать:

Maksim и Igor дружат друг с другом
Olga и David дружат друг с другом
Den и John дружат друг с другом

Answer 1

Вам не нужны вложенные запросы. Просто сделайте JOIN таблицы contacts_friend к ней самой. И в условии отфильтруйте строки, где одно из двух полей id_contact и id_contact_friend основной таблицы больше (или меньше) второго. Пример:

SELECT 
    c1.contacts_name
    ,c2.contacts_name
FROM contacts_friend cf1
JOIN contacts_friend cf2 ON cf1.id_contact = cf2.id_contact_friend
                          AND cf1.id_contact_friend = cf2.id_contact
JOIN contacts c1 ON c1.id = cf1.id_contact
JOIN contacts c2 ON c2.id = cf1.id_contact_friend
WHERE cf1.id_contact < cf1.id_contact_friend

Answer 2

Получаем записи из таблицы друзей таким образом, что бы первым всегда был меньший ID, а вторым больший. группируем таблицу по этим двум полям. Если в итоге группировки оказалось 2 записи - значит они дружат друг с другом. Доклеиваем таблицы с именами:

select C1.contacts_name, C2.contacts_name
  from (
    select least(id_contact,id_contact_friend) f1,
           greatest(id_contact,id_contact_friend) f2
      from contacts_friend
     group by f1,f2
    having count(1)=2
  ) fr,
  contacts C1,
  contacts C2
 where C1.id=fr.f1 and C2.id=fr.f2

Тест на sqlfiddle

Answer 3

Просто добавьте условие на имена, которое автоматически уберет дубли:

SELECT Table1.contacts_name, Table2.contacts_name
FROM
    (SELECT C1.contacts_name, T1.id 
     FROM
        (SELECT cf1.id_contact, cf1.id
         FROM contacts_friend cf1, contacts_friend cf2
         WHERE cf1.id_contact=cf2.id_contact_friend AND cf2.id_contact=cf1.id_contact_friend
        ) T1,
        contacts C1
     WHERE
        T1.id_contact = C1.id
     ) Table1,
     (SELECT C2.contacts_name, T2.id
      FROM
        (SELECT cf1.id_contact_friend, cf1.id
        FROM contacts_friend cf1, contacts_friend cf2
        WHERE cf1.id_contact=cf2.id_contact_friend AND cf2.id_contact=cf1.id_contact_friend
        ) T2,
        contacts C2
      WHERE
        T2.id_contact_friend = C2.id
      ) Table2
WHERE Table1.id = Table2.id
    and Table1.contacts_name <= Table2.contacts_name

А вообще можно гораздо проще написать запрос (как позже выяснил, повторяет ответ выше):

select concat(co1.name, ' и ', co2.name, ' дружат друг с другом') as answer
from contracts_friend c1
    join contacts_friend c2 ON c1.id_contact = c2.id_contact_friend
         and c1.id_contact_friend = c2.id_contact /* образуем взаимные пары друзей */
    join contacts co1 ON co1.id = c1.id_contact /* джойним к ним имена */
    join contacts co2 ON co2.id = c2.id_contact
where c1.id_contact < c2.id_contact