Join master table's data to a key/value table data in one SELECT

Question

I have a table called Contacts that contains the columns

1. Id
2. FirstName
3. LastName

I have another table called ContactsExtra. It has the following columns:

1. Id
2. ContactId (FK refers to Contacts Id)
3. PropertyId (FK refers to Properties Id)
4. PropertyValue

This is a Key/Value table that stores some extra contact properties. E.g. if contact has a Salary property there is a record in this table for all Contacts that stores the Salary value.

All Properties (whether they are in the main Contacts table or not) are stored in a separate table called Properties. In this table, those properties that come from Contacts (FirstName and LastName) are locked. But the user can add or remove custom properties (these ones are not locked). The value of these new properties will be stored in ContactsExtra.

The Properties table contains the following columns:

1. Id
2. Name

What I would like to do is to display all the contact information using one SELECT. So e.g. in the above case there will be a result with columns Id,FirstName,LastName and also Salary. The first three come from Contacts and the last comes from the Key/Value table. How can I join these information together?

Answer 1

SELECT 
    Contacts.FirstName,
    Contacts.LastName,
    Properties.Name,
    ContactsExtra.PropertyValue
FROM
    Contacts
    LEFT OUTER JOIN ContactsExtra ON Contacts.Id = ContactsExtra.ContactId
    LEFT OUTER JOIN Properties ON ContactsExtra.PropertyId = Properties.Id

Okay, here's the update with a PIVOT (not UNPIVOT as I first thought)...

SELECT 
    * 
FROM
    (
    SELECT 
        Contacts.FirstName,
        Contacts.LastName,
        Properties.Name,
        ContactsExtra.PropertyValue
    FROM
        Contacts
        LEFT OUTER JOIN ContactsExtra ON Contacts.Id = ContactsExtra.ContactId
        LEFT OUTER JOIN Properties ON ContactsExtra.PropertyId = Properties.Id
    ) DerivedPivotable
    PIVOT
    (
        MAX(PropertyValue)
        FOR [Name] IN (<comma delimited list of values from your Properties.Name field, without string markup>)
        --FOR [Name] IN (Salary, Height, Status, SSN) --example row
    ) Pivoted

Please check against your data - I expect the output to be something like...

FirstName   LastName    Salary  Height  Status  SSN
-----------------------------------------------------------
Jane        Doe         NULL    5'7"    Single  NULL
Bob         Smith       70,000  6'1"    NULL    123-45-6789

The use of MAX is a bit of a kludge because it has to be an aggregate function there. The assumption is being made that there is only one value in the ContactsExtra table for each combination of user and property.