Need to write a select query to pull required data from 3 tables.

  • Hi,

    I have three tables EmpIDs,EmpRoles and LatestRoles. I need to write a select Query to get roles of all employees present in EmpIDs table by referring EmpRoles and LatestRoles.

    Where I stuck : The condition is first look into table EmpRoles and if it has more than one entry for a particular Employee ID than only need to get the Role from LatestRoles other wise consider the role from EmpRoles .

    Example:

    Create Table #EmpIDs

    (

    EmplID int

    )

    Create Table #EmpRoles

    (

    EMPID int,

    Designation varchar(50)

    )

    Create Table #LatestRoles

    (

    EmpID int,

    Designations varchar(50)

    )

    Insert into #EmpIDs values (1),(2),(3)

    Insert into #EmpRoles values (1,'Role1'),(2,'Role1'),(2,'Role2'),(3,'Role1')

    Insert into #LatestRoles values (2,'Role2')

    Employee ID 2 is having two roles defined in EmpRoles so for EmpID 2 need to fetch Role from LatestRoles table and for remaining ID's need to fetch from EmpRoles .

    My Final Output of select query should be like below.

    EmpID Role

    1 Role1

    2 Role2

    3 Role1

    Please help.

  • Can't help thinking this would be a whole lot easier if you had a date field in the EmployeeRoles table...

    Then you could just get the EmployeeID and Role With the MAX Date, and that would be the latest one, and it would be super easy.

    use TempDB;

    GO

    CREATE TABLE Employee(

    EmployeeID INT IDENTITY(100,1)

    , FirstName VARCHAR(20)

    , LastName VARCHAR(20)

    CONSTRAINT pkEmployee PRIMARY KEY (EmployeeID));

    GO

    INSERT INTO Employee(FirstName,LastName) VALUES ('Homer','Simpson'),('Marge','Simpson'),('Bart','Simpson'),('Lisa','Simpson');

    CREATE TABLE EmployeeRole(

    EmployeeID INT

    ,RoleName VARCHAR(25)

    ,StartDate DATE

    CONSTRAINT pkEmployeeRole PRIMARY KEY (EmployeeID,RoleName),

    CONSTRAINT fkEmployeeID FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID));

    INSERT INTO EmployeeRole(EmployeeID,RoleName,StartDate) VALUES (100,'Husband','3-1-1985'),

    (100,'Father','6-6-1986'),

    (101,'Wife','3-1-1985'),

    (101,'Mother','6-6-1986');

    -- return the latest role for each employee...

    SELECT er.EmployeeID, RoleName, StartDate

    FROM EmployeeRole er

    INNER JOIN

    (SELECT EmployeeID

    ,MAX(StartDate) AS LastDate

    FROM EmployeeRole

    GROUP BY EmployeeID) lr

    ON (er.EmployeeID = lr.EmployeeID AND er.StartDate = lr.LastDate);

  • WITH CTE (EmplID,previousRole,currentRole) AS

    (

    SELECT #EmpIDs.EmplID,

    #EmpRoles.Designation AS [Previous Role],

    #LatestRoles.Designations AS [Current Role]

    from #EmpIDs

    INNER JOIN #EmpRoles ON #EmpIDs.EmplID = #EmpRoles.EmpID

    LEFT OUTER JOIN #LatestRoles ON #EmpIDs.EmplID = #LatestRoles.EmpID

    )

    SELECT EmplID AS ID ,previousRole AS [Role]

    FROM CTE

    WHERE currentRole IS NULL

    UNION ALL

    SELECT DISTINCT EmplID AS ID ,currentRole AS [Role]

    FROM CTE

    WHERE currentRole IS NOT NULL

    ORDER BY ID

    ___________________________________
    Computer Enterprise Masoud Keshavarz
    I don't care about hell.
    If I go there I've played enough Diablo to know how to fight my way out.

  • SELECT EI.EmplID,ER.Designation FROM #EmpIDs AS EI

    INNER JOIN #EmpRoles AS ER

    ON EI.EmplID = ER.EMPID

    WHERE EI.EmplID IN (1,3)

    UNION ALL

    SELECT * FROM #LatestRoles

    ORDER BY EmplID

  • kumariannapureddy (3/19/2014)


    SELECT EI.EmplID,ER.Designation FROM #EmpIDs AS EI

    INNER JOIN #EmpRoles AS ER

    ON EI.EmplID = ER.EMPID

    WHERE EI.EmplID IN (1,3)

    UNION ALL

    SELECT * FROM #LatestRoles

    ORDER BY EmplID

    You shouldnt write queries that work only with specific values 😉

    ___________________________________
    Computer Enterprise Masoud Keshavarz
    I don't care about hell.
    If I go there I've played enough Diablo to know how to fight my way out.

  • This works with the data provided.

    Selectdistinct

    a.EmplID,

    Case when c.EmpID is null then b.Designation else c.Designations End as [Role]

    from #EmpIDs a

    inner join #EmpRoles b on a.EmplID = b.EmpID

    left outer join #LatestRoles c on a.EmplID = c.EmpID

    If you have any control over the way the application is written, my preference would be for the current role to be written to #LatestRoles always regardless of whether there are previous roles or not. Then you just have a simple 2 table inner join to perform between #EmpIDs and #LatestRoles.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply