Блог

У меня есть таблица для persons, другая для типов контактов (email, tel и т.д.) и таблица соединений для сохранения контактов пользователей.

Я хотел бы создать запрос, чтобы получить email, tel, po для каждого пользователя в одной записи,

итак, у меня будет результат в 4 столбцах: имя пользователя, телефон, электронная почта, PO

Поскольку я использую SQL Server 2000, XML не подходит.

Любая помощь, чтобы заставить это работать, будет оценена.

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

 WITH Persons(PersonId, UserName) AS
(
SELECT 1, 'Bob' UNION ALL
SELECT 2, 'Bill'
), ContactTypes(ContactTypeId, Name) AS
(
SELECT 1, 'Tel' UNION ALL
SELECT 2, 'Email' UNION ALL
SELECT 3, 'PO'
),PersonContacts(PersonId, ContactTypeId, Value) As
(
SELECT 1,1,CAST('(01223) 123456' AS VARCHAR(50)) UNION ALL
SELECT 1,2,CAST('bob@example.com' AS VARCHAR(50)) UNION ALL
SELECT 1,3,CAST('1 Acacia Avenue' AS VARCHAR(50)) UNION ALL
SELECT 2,1,CAST('(01223) 654321' AS VARCHAR(50)) UNION ALL
SELECT 2,2,CAST('bill@example.com' AS VARCHAR(50)) 
)

/*The above Common Table Expressions are just for demo purposes and so you 
can see the assumptions. They will not work on SQL Server 2000. You just need 
the below.*/
SELECT P.UserName, 
       /*I've just used the Ids rather than bothering to join on ContactTypes*/
       MAX(CASE WHEN C.ContactTypeId = 1 then C.Value END) Tel,
       MAX(CASE WHEN C.ContactTypeId = 2 then C.Value END) Email,
       MAX(CASE WHEN C.ContactTypeId = 3 then C.Value END) PO
FROM Persons P
LEFT JOIN PersonContacts C ON C.PersonId = P.PersonId
GROUP BY P.PersonId,P.UserName
  

ВОЗВРАТ

 UserName Tel                 Email               PO
-------- ------------------- ------------------- ------------------
Bob      (01223) 123456      bob@example.com     1 Acacia Avenue
Bill     (01223) 654321      bill@example.com    NULL
  

Я думаю, вы хотите это:

 SELECT p.username, email.value as email, tel.value as tel, po.value as po
FROM persons p
LEFT JOIN contacts as email ON contacts.person_id = person.id
LEFT JOIN contacts as tel ON contacts.person_id = person.id
LEFT JOIN contacts as po ON contacts.person_id = person.id
WHERE email.type = 'email'
AND tel.type = 'tel'
AND po.type = 'po'
  

Таким образом, вы в основном объединяетесь в таблице contacts 3 раза, потому что таблица contacts содержит 3 разные формы контактов. Это полиморфный подход, и он плохо соотносится с SQL

Результатом этого запроса должно быть что-то вроде:

 username | email           | tel       | po
John     | john@john.com   | 012364342 | 1234AA
Sarah    | sarah@sarah.com | NULL      | NULL
  

Я думаю, что решение Мартина также будет работать, но я не слишком доволен случаем, КОГДА ЗАКАНЧИВАЕТСЯ

Мы должны строить догадки о вашей схеме, потому что вы не предоставили нам важную информацию в начальной версии вопроса:

Предполагаемая схема

• Таблица: Persons — Имя пользователя (PK), …
• Таблица: Контакты — ContactType (‘Tel’, ‘Email’, ‘PO’), ContactInfo, ContactID (PK), …
• Таблица: Соединение — имя пользователя, ContactID — PK в списке, FK в каждом столбце

Возможный запрос

 SELECT P.UserName, T.Tel, E.Email, O.PO
  FROM Persons AS P
  LEFT JOIN (SELECT J.UserName, C.ContactInfo AS Tel
               FROM Contacts AS C
               JOIN Junction AS J ON C.ContactId = J.ContactID
              WHERE C.ContactType = 'Tel') AS T
       ON P.UserName = T.UserName
  LEFT JOIN (SELECT J.UserName, C.ContactInfo AS Email
               FROM Contacts AS C
               JOIN Junction AS J ON C.ContactId = J.ContactID
              WHERE C.ContactType = 'Email') AS E
       ON P.UserName = E.UserName
  LEFT JOIN (SELECT J.UserName, C.ContactInfo AS PO
               FROM Contacts AS C
               JOIN Junction AS J ON C.ContactId = J.ContactID
              WHERE C.ContactType = 'PO') AS O
       ON P.UserName = O.UserName
  

Обратите внимание, что существуют некоторые сложные ограничения, которые должны быть соблюдены, чтобы запрос работал нормально. Основной из них заключается в том, что для данного пользователя существует либо ноль, либо один адрес электронной почты, либо ноль, либо один номер телефона, либо ноль, либо один почтовый ящик (это адрес почтового отделения?). Вы просто не говорите нам, какой может быть подходящий вывод, если у кого-то есть более одной из этих записей. И эти ограничения сложно применять, потому что простейшая версия потребовала бы уникального ограничения на комбинацию имени пользователя и типа контакта, но эти столбцы не хранятся вместе ни в одной таблице.