Join same table twice

[Ringers]

Hi All,

I have 3 tables,

Employee:
Id, code, name, pos_def_id
213, Z300, Diana Benk, 108

ShiftAssigment:
Id, shiftId, EmployeeID, date, assigmentReason_def_id
180, 3, 213, 2011-09-23, 198

Definition:
Id, DefinitionCategoryID, description
108, 2, Full Time
198, 1, Medical

You can see employee Diana ID 213 is full time(108) and has been assigned shift 3 which is a medical(198) shift type.

The sql i have written shows the employee details and shift but not the full time or part time as the case maybe. I need to reference the definition table for each shift assigned and for each employee.

Current SQL:

SELECT Sh.Description, Sh.LocationID, Sh.StartTime, sh.Duration,
sa.ShiftID, sa.EmployeeID, sa.Date,
E.Code AS EmpCode, E.Name, E.IsActive,
D.DefinitionCategoryID,AssignmentReason_DefID, D.Description AS [Hearing Type],
E.Position_DefID, D.Description AS [Shift Type]

FROM
ShiftAssignment sa
INNER JOIN
Definition d ON D.ID = sa.AssignmentReason_DefID
INNER JOIN
Shift sh ON Sh.ID = sa.ShiftID
INNER JOIN
Employee e ON E.ID = sa.EmployeeID

Current Results:
Description LocationID StartTime Duration ShiftID EmployeeID Date EmpCode Name IsActive DefinitionCategoryID AssignmentReason_DefID Hearing Type Position_DefID Shift Type
NSW HR1 AM 52 00:00.0 210 3 213 00:00.0 Z300 Diana Benk 1 1 198 Show Cause 108 Show Cause

I need the last column to be full time not show cuase.

Thanks

 

[Ringers]

*Sorry can't find the edit post button for existing posts.*

Hi All,

I have 4 tables,

Employee:
Id, code, name, pos_def_id
213, Z300, Diana Benk, 108

ShiftAssigment:
Id, shiftId, EmployeeID, date, assigmentReason_def_id
180, 3, 213, 2011-09-23, 198

Shift:
Id,code,desc,locationID,duration
3, NSW HR1 AM, 52,210

Definition:
Id, DefinitionCategoryID, description
108, 2, Full Time
198, 1, Medical

You can see employee Diana ID 213 is full time(108) and has been assigned shift 3 which is a medical(198) shift type.

The sql i have written shows the employee details and shift but not the full time or part time as the case maybe. I need to reference the definition table for each shift assigned and for each employee.

Current SQL:

SELECT Sh.Description, Sh.LocationID, Sh.StartTime, sh.Duration,
sa.ShiftID, sa.EmployeeID, sa.Date,
E.Code AS EmpCode, E.Name, E.IsActive,
D.DefinitionCategoryID,AssignmentReason_DefID, D.Description AS [Hearing Type],
E.Position_DefID, D.Description AS [Shift Type]

FROM
ShiftAssignment sa
INNER JOIN
Definition d ON D.ID = sa.AssignmentReason_DefID
INNER JOIN
Shift sh ON Sh.ID = sa.ShiftID
INNER JOIN
Employee e ON E.ID = sa.EmployeeID

Current Results:
Description LocationID StartTime Duration ShiftID EmployeeID Date EmpCode Name IsActive DefinitionCategoryID AssignmentReason_DefID Hearing Type Position_DefID Shift Type
NSW HR1 AM 52 00:00.0 210 3 213 00:00.0 Z300 Diana Benk 1 1 198 Show Cause 108 Show Cause

I need the last column to be 'full time' not 'show cuase'.

Thanks

 

[drlex]

Looks like you'll need to join your Definition table to the Employee table on Employee.pos_def_id = Description.ID

SELECT     Sh.Description, Sh.LocationID, Sh.StartTime,  sh.Duration,
           sa.ShiftID, sa.EmployeeID, sa.Date,
           E.Code AS EmpCode, E.Name, E.IsActive,
           D.DefinitionCategoryID,AssignmentReason_DefID, D.Description AS [Hearing Type],
           E.Position_DefID, [b][red]D2[/red][/b].Description AS [Shift Type]

FROM         
ShiftAssignment sa
    INNER JOIN
        Definition d ON D.ID = sa.AssignmentReason_DefID
    INNER JOIN
        Shift sh ON Sh.ID = sa.ShiftID
    INNER JOIN
        Employee e ON E.ID = sa.EmployeeID
    [b][red]INNER JOIN
        Definition d2 ON D2.ID = e.pos_def_id[/red][/b]

This assumes that every employee has a pos_def_id - to check, make it a LEFT OUTER JOIN rather than INNER JOIN.

soi là, soi carré

 

[Ringers]

thanks for the reply, thats what i ended up doing and it works fine.

SELECT Sh.LocationID, sh.code,Sh.Description, Sh.StartTime, Sh.Duration,
sa.id as [Venue Booking ID],sa.ShiftID, sa.EmployeeID, sa.Date,
E1.Code AS EmpCode,E1.Name, E1.IsActive,
AssignmentReason_DefID,
D1.[ShortDescription] AS [Hearing Type],
E1.Position_DefID,D2.Description AS [Shift Type]


FROM ShiftAssignment sa
INNER JOIN
Definition d1 ON d1.ID = sa.AssignmentReason_DefID
INNER JOIN
Shift sh ON Sh.ID = Sa.ShiftID
INNER JOIN
Employee E1 ON e1.id= sa.EmployeeID
INNER JOIN
Definition d2 ON d2.id = E1.position_defid