Joining 3 Tables

join;

I have 3 tables as per below:

**ATTRIBUTE TABLE**
PeopleAttrCODE  PersonCODE  AttributeCODE   Value
      1             1       End Date        20151231
      2             1       Start Date      20150101
      3             1       Employee Type   Consultant
      4             2       End Date        20151231
      5             2       Start Date      20150101 
      6             2       Employee Type   Employee

**IDENTIFIER TABLE**
PeopleIDCODE    PersonCODE  IdentifierCODE
      1             1       AAA
      2             2       BBB

**OBJECT TABLE**

ObjectCODE  Name
      1     User 1
      2     User 2

How can i view it as:

Name    IdentifierCODE  Employee   Type Start    Date       End Date
User          1         AAA        Consultant    20150101   20151231
User          2         BBB        Employee      20150601   20161231

Thanks,
Mel

Best Answer

Here is a way to do this. I hope , the join are on the right fields.

--Creating the source tables
DECLARE @ATTRIBUTE TABLE
(   PeopleAttrCODE INT PRIMARY KEY CLUSTERED,
    PersonCODE INT,
    AttributeCODE  VARCHAR(50),
    Value VARCHAR(50))
INSERT INTO @Attribute(PeopleAttrCODE,PersonCODE,AttributeCODE,Value)
VALUES (1,1,'End Date','20151231'),
       (2,1,'Start Date','20150101'),
       (3,1,'Employee Type','Consultant'),
       (4,2,'End Date','20151231'),
       (5,2,'Start Date','20150101'),
       (6,2,'Employee Type','Employee')

DECLARE @IDENTIFIER TABLE
(   PeopleIDCODE  INT PRIMARY KEY CLUSTERED,
    PersonCODE INT,
    IdentifierCODE VARCHAR(50))
INSERT INTO @IDENTIFIER(PeopleIDCODE,PersonCODE,IdentifierCODE)
VALUES(1,1,'AAA'),
      (2,2,'BBB')

DECLARE @OBJECT TABLE
( ObjectCODE INT PRIMARY KEY CLUSTERED,
  Name VARCHAR(50))
INSERT INTO @OBJECT(ObjectCODE,Name)
VALUES (1,'User 1'),
       (2,'User 2')

SELECT
    OBJ.Name,
    ID.PersonCODE,
    ID.IdentifierCODE,
    CA.[Employee Type] AS [Type],
    CA.[Start Date] AS [Start Date],
    CA.[End Date] AS [End Date]
    --*
FROM
    @OBJECT AS OBJ
    INNER JOIN @IDENTIFIER AS ID
    ON OBJ.ObjectCODE = ID.PeopleIDCODE
    --ON OBJ.ObjectCODE = ID.PeopleCODE 
    INNER JOIN 
    (SELECT 
            PersonCODE
            ,A.[Start Date]
            , A.[End Date]
            ,A.[Employee Type]
     FROM 
        (SELECT PersonCODE,AttributeCODE,Value 
        FROM @ATTRIBUTE
        WHERE AttributeCODE IN ('End Date','Start Date','Employee Type')) AS ATT
        PIVOT
        ( MAX(Value) FOR AttributeCODE IN ([End Date],[Start Date],[Employee Type]))A    
    )CA
    ON CA.PersonCODE = ID.PersonCODE

The output:

Name       PersonCODE  IdentifierCODE Type            Start Date      End Date
User 1     1           AAA            Consultant      20150101        20151231
User 2     2           BBB            Employee        20150101        20151231
Related Question