I have a table for persons, anothor for types of contacts(email, tel, etc.) and a junction table to save person contacts.
I'w like to construct a query to get email, tel, po for each person in one record,
so I will have 4 columns result set UserName, Tel, Email, PO
As I use SQL Server 2000, XML is not an option.
Any help to get this to work will be appreciated.
You have failed to provide any useful details about columns so I had to guess but I think you are looking for something like.
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('[email protected]' 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('[email protected]' 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
Returns
UserName Tel Email PO
-------- ------------------- ------------------- ------------------
Bob (01223) 123456 [email protected] 1 Acacia Avenue
Bill (01223) 654321 [email protected] NULL
GROUP BY takes care of that. This is a fairly standard PIVOT/cross tab technique.
I think you want this:
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'
So you basically join on the contacts table 3 times because the contacts table contains 3 different forms of contacts. This is a polymorphic approach and it doesn't map well to SQL
The result of this query should be something like:
username | email | tel | po
John | [email protected] | 012364342 | 1234AA
Sarah | [email protected] | NULL | NULL
I think martin's solution will work as well but I'm not too happy with the CASE-WHEN-THEN-END
We have to make guesses about your schema because you didn't give us the crucial information in the initial version of the question:
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
Note that there are some complex constraints that should be enforced to make the query work sanely. The primary one is that there is either zero or one email address for a given person, and zero or one telephone number, and zero or one PO (is that a Post Office address?). You simply don't tell us what might be appropriate output if someone has more than one of any of these entries. And the constraints are complex to enforce because the simplest version would require a unique constraint on the combination of user name and contact type, but those columns are not stored together in any single table.