Предотвращение дублирования значений в LEFT JOIN

Ответ 1

Мне нравится называть эту проблему "cross join by proxy" . Поскольку нет информации (условие WHERE или JOIN), как предполагается, что таблицы department и contact совпадают, они пересекаются через прокси-таблицу person - давая вам Декартовский продукт. Очень похоже на это:

• Два SQL LEFT JOINS создают неверный результат

Больше объяснений там.

Решение для вашего запроса:

SELECT p.id, p.person_name, d.department_name, c.phone_number
FROM   person p
LEFT   JOIN (
  SELECT person_id, min(department_name) AS department_name
  FROM   department
  GROUP  BY person_id
  ) d ON d.person_id = p.id
LEFT   JOIN (
  SELECT person_id, min(phone_number) AS phone_number
  FROM   contact
  GROUP  BY person_id
  ) c ON c.person_id = p.id;

Вы не определили, какой отдел или номер телефона выбрать, поэтому я произвольно выбрал первый. Вы можете использовать его любым другим способом...

Ответ 2

Думаю, вам просто нужно получить списки отделов и телефонов для определенного человека. Поэтому просто используйте array_agg (или string_agg или json_agg):

SELECT
    p.id,
    p.person_name,
    array_agg(d.department_name) as "department_names",
    array_agg(c.phone_number) as "phone_numbers"
FROM person AS p
LEFT JOIN department AS d ON p.id = d.person_id
LEFT JOIN contact AS c on p.id = c.person_id
GROUP BY p.id, p.person_name

Ответ 3

Несмотря на то, что таблицы, очевидно, упрощены для обсуждения, похоже, они структурно ошибочны. Таблицы должны быть структурированы для отображения отношений между объектами, а не просто списков сущностей и/или атрибутов. И в этом случае я считаю, что номер телефона является атрибутом (лица или подразделения).

Первым шагом будет создание таблиц с отношениями, каждый из которых имеет первичный ключ и, возможно, внешний ключ. В этом примере было бы полезно, чтобы таблица Person использовала person_id для первичного ключа, а таблица отдела использовала department_id для своего первичного ключа. Затем найдите отношения "один ко многим" или "многие ко многим" и соответствующим образом установите свои внешние ключи:

• Если один человек может находиться только в одном отделе за раз, то у вас есть один (отдел) -то-многие (лица). Нет внешнего ключа в таблице отдела, но department_id будет внешним ключом в таблице лиц.
• Если один человек может находиться в более чем одном отделе, у вас есть много-ко-многим, и вам понадобится дополнительная таблица соединений с person_id и department_id как внешние ключи.

Подводя итог, в вашем сценарии должны быть только две таблицы: одна таблица для человека и другая таблица для отдела. Даже с учетом личных телефонных номеров (столбец в таблице лиц) и номеров отделов в таблице отделов это было бы лучшим подходом.

Единственное предостережение - когда один отдел имеет много номеров (или более одного отдела разделяет один номер телефона), но это выходит за рамки исходного вопроса.

Ответ 4

Используйте этот тип запроса: SQL Server
(Вы можете изменить id ORDER BY id на каждый столбец, который вы хотите)

SELECT 
    p.id, 
    p.person_name, 
    d.department_name, 
    c.phone_number
FROM
    person p
    LEFT JOIN 
    (SELECT *, ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY id) AS seq
     FROM department) d 
    ON d.person_id = p.id And d.seq = 1
    LEFT JOIN 
    ( SELECT *, ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY id) AS seq
     FROM contact) c 
    ON c.person_id = p.id And c.seq = 1;

Ответ 5

SELECT p.id, p.person_name, d.department_name, c.phone_number 
FROM person p
  LEFT JOIN department d 
    ON p.id = d.person_id
  LEFT JOIN contact c 
    ON p.id = c.person_id 
group by p.id, p.person_name, d.department_name, c.phone_number