Какой лучший способ присоединиться к одной таблице дважды?

Это немного сложно, но у меня есть 2 таблицы. Скажем, структура выглядит примерно так:

*Table1*
ID
PhoneNumber1
PhoneNumber2

*Table2*
PhoneNumber
SomeOtherField

Таблицы могут быть объединены на основе Table1.PhoneNumber1 → Table2.PhoneNumber или Table1.PhoneNumber2 → Table2.PhoneNumber.

Теперь я хочу получить набор результатов, содержащий PhoneNumber1, SomeOtherField, который соответствует PhoneNumber1, PhoneNumber2 и SomeOtherField, который соответствует PhoneNumber2.

Я подумал о двух способах сделать это - либо путем присоединения к таблице дважды, либо путем соединения один раз с OR в предложении ON.

Метод 1:

SELECT t1.PhoneNumber1, t1.PhoneNumber2, 
   t2.SomeOtherFieldForPhone1, t3.someOtherFieldForPhone2
FROM Table1 t1
INNER JOIN Table2 t2
   ON t2.PhoneNumber = t1.PhoneNumber1
INNER JOIN Table2 t3
   ON t3.PhoneNumber = t1.PhoneNumber2

Кажется, что это работает.

Метод 2:

Чтобы каким-то образом получить запрос, который выглядит примерно так:

SELECT ...
FROM Table1
INNER JOIN Table2 
   ON Table1.PhoneNumber1 = Table2.PhoneNumber OR
      Table1.PhoneNumber2 = Table2.PhoneNumber

Я еще не получил этого, и я не уверен, есть ли способ сделать это.

Какой лучший способ достичь этого? Ни один из способов не кажется простым или интуитивным... Есть ли более простой способ сделать это? Как это требование обычно выполняется?

Ответ 1

Сначала я попытался бы реорганизовать эти таблицы, чтобы избежать использования телефонных номеров в качестве естественных ключей. Я не поклонник натуральных ключей, и это отличный пример. Естественные клавиши, особенно такие, как номера телефонов, могут меняться и часто так. Обновление вашей базы данных при этом изменении будет ОГРОМНОЙ, подверженной ошибкам головной боли. *

Метод 1, как вы описали, это ваш лучший выбор. Это выглядит немного кратким из-за схемы именования и коротких псевдонимов, но... aliasing - ваш друг, когда дело доходит до присоединения к одной таблице несколько раз или с помощью подзапросов и т.д.

Я бы немного починил вещи:

SELECT t.PhoneNumber1, t.PhoneNumber2, 
   t1.SomeOtherFieldForPhone1, t2.someOtherFieldForPhone2
FROM Table1 t
JOIN Table2 t1 ON t1.PhoneNumber = t.PhoneNumber1
JOIN Table2 t2 ON t2.PhoneNumber = t.PhoneNumber2

Что я сделал:

Не нужно указывать INNER - это подразумевается тем, что вы не указываете LEFT или RIGHT
Не n-суффикс вашей первичной таблицы поиска
N-Suffix псевдонимы таблицы, которые вы будете использовать несколько раз, чтобы сделать это очевидным.

* Односторонние администраторы баз данных избегают головных болей обновления естественных ключей - не указывать первичные ключи и ограничения внешнего ключа, что еще больше усугубляет проблемы с плохим дизайном db. Я на самом деле видел это чаще, чем нет.

Ответ 2

Первое хорошо, если ни Phone1, ни, скорее всего, phone2 не могут быть нулевыми. В этом случае вы хотите использовать левое соединение вместо внутреннего соединения.

Обычно это плохой знак, когда у вас есть таблица с двумя номерами телефонных номеров. Обычно это означает, что ваш дизайн базы данных испорчен.

Ответ 3

Первый метод - это правильный подход и будет делать то, что вам нужно. Однако с внутренними соединениями вы будете выбирать строки только из Table1, если оба номера телефонов существуют в Table2. Вы можете сделать LEFT JOIN так, чтобы были выбраны все строки из Table1. Если номера телефонов не совпадают, то значение SomeOtherField будет равно нулю. Если вы хотите убедиться, что у вас есть хотя бы один соответствующий номер телефона, вы можете сделать WHERE t2.PhoneNumber IS NOT NULL OR t3.PhoneNumber IS NOT NULL

Второй способ может иметь проблему: что произойдет, если Table2 имеет как PhoneNumber1, так и PhoneNumber2? Какая строка будет выбрана? В зависимости от ваших данных, внешних ключей и т.д. Это может быть или не быть проблемой.

Ответ 4

Вы можете использовать UNION для объединения двух соединений:

SELECT Table1.PhoneNumber1 as PhoneNumber, Table2.SomeOtherField as OtherField
  FROM Table1
  JOIN Table2
    ON Table1.PhoneNumber1 = Table2.PhoneNumber
 UNION
SELECT Table1.PhoneNumber2 as PhoneNumber, Table2.SomeOtherField as OtherField
  FROM Table1
  JOIN Table2
    ON Table1.PhoneNumber2 = Table2.PhoneNumber

Ответ 5

Моя проблема заключалась в отображении записи, даже если нет или только один номер телефона существует (полная адресная книга). Поэтому я использовал LEFT JOIN, который берет все записи слева, даже если не существует соответствующего права справа. Для меня это работает в Microsoft Access SQL (требуется скобка!)

SELECT t.PhoneNumber1, t.PhoneNumber2, t.PhoneNumber3
   t1.SomeOtherFieldForPhone1, t2.someOtherFieldForPhone2, t3.someOtherFieldForPhone3
FROM 
(
 (
  Table1 AS t LEFT JOIN Table2 AS t3 ON t.PhoneNumber3 = t3.PhoneNumber
 )
 LEFT JOIN Table2 AS t2 ON t.PhoneNumber2 = t2.PhoneNumber
)
LEFT JOIN Table2 AS t1 ON t.PhoneNumber1 = t1.PhoneNumber;