Левое внешнее соединение с одним результатом на совпадение и "приоритетом" совпадения, установленным другим полем в совпадении SQL Server 2005

Question

Я пытаюсь получить один результат (PHONE) за матч (CONTACT_ID) в левом внешнем соединении. Я полагаю, что есть способ сделать это, установив предпочтение (или порядок) с помощью другого столбца / поля - типа телефона (TYPE), но я не смог понять это. Ниже приведен список фактов, которые помогут лучше объяснить, что я пытаюсь выполнить, а затем приведем примеры таблиц A и B с желаемым результатом. Я посмотрел на min() и группу по, но я не знаю, как заставить их работать здесь. Как примечание, после того, как это сработает, я присоединю его к большему количеству таблиц слева от него более простым способом.

• Студент может иметь неограниченное количество CONTACT_ID.
• Контакт не всегда имеет все типы телефонов.
• Предпочтительный порядок типов телефонов (TYPE) - C,H,W (который, к счастью, бывает алфавитным)
• игнорировать совпадение и переходить к следующему по приоритету, если PHONE равен нулю

TableA:

STUDENT_ID  CONTACT_ID
----------  ----------
X           1
X           2
Y           3
Y           4

TableB:

CONTACT_ID    TYPE    PHONE
----------    ----    -----
1             H       21
1             C
1             W       44
2             H       78
2             C       92
2             W       11

Желаемый результат:

STUDENT_ID     CONTACT_ID   TYPE   PHONE
----------     ----------   ----   -----
X              1            H      21
X              2            C      92
Y              3
Y              4

Вот мой запрос, который объединит все телефонные совпадения (за исключением всех моих безумных попыток получить то, что я хочу).

SELECT *
FROM Table TableA T1
LEFT OUTER JOIN TableB T2 ON T1.CONTACT_ID = T2.CONTACT_ID

Вся помощь с благодарностью!

Отредактированный код из решения Стефана Онофрея:
(приводит к нескольким повторным записям)

SELECT
    T1.STUDENT_ID,
    T1.CONTACT_ID,
    T2.PHONE_TYPE,
    T3.PHONE
FROM REG_STU_CONTACT T1
INNER JOIN 
    (SELECT MIN(PHONE_TYPE) AS PHONE_TYPE, CONTACT_ID 
    FROM REG_CONTACT_PHONE
    WHERE PHONE IS NOT NULL
    GROUP BY CONTACT_ID) T2 ON T1.CONTACT_ID = T2.CONTACT_ID
INNER JOIN REG_CONTACT_PHONE T3 ON T2.CONTACT_ID = T3.CONTACT_ID AND T2.PHONE_TYPE = T3.PHONE_TYPE
ORDER BY T1.STUDENT_ID

Answer 1

Select A.STUDENT_ID     A.CONTACT_ID   B.TYPE   c.PHONE 
from TableA A
inner join 
    (select MIN(type ) as type, Contact_ID 
    from Tableb
    where phone is not null
    group by contactid) B
on A.contactid = b.contactid
inner join Tableb C 
on B.contactid = c.conatctid and b.type = c.type