Help with complex query + left join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with complex query + left join

Author

Topic

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-08-20 : 23:06:26

Hi

I got a complex query from many tables as below that is working perfectly. However, I am required to left join HumanResources.Employee E to Procurement.PurchaseApprovalSetup (PAS)
on
E.EmployeeID = P.EmployeeID
to select the confirmer from the PAS Table

i.e. Procurement.PurchaseApprovalSetup may not have all employeeID.

How do I do this?

SELECT distinct E.[EmployeeID]
,E.[ManagerID]as ManagerID
,E.[ManagerID2] as ManagerID2
,isnull(E.[NationalIDNumber],'') as NationalIDNumber
,isnull(E.[Name],'') as EmployeeName
,isnull(E.[EmailAddress],'') as EmailAddress
,isnull(E.[Phone],'') as Phone
,isnull(E.[Mobile], '') as Mobile
,convert(varchar, E.[BirthDate], 106) as birthdate
,isnull(E.[BirthCountryRegionCode],'') as BirthCountryRegionCode
,isnull(E.[NationalityCountryRegionCode],'') as NationalityCountryRegionCode
,isnull(E.[MaritalStatus], '') as MaritalStatus
,isnull(E.[Gender],'') as Gender
,(Select Top(1) ECPH.Designation
from HumanResources.EmployeeCareerPathHistory ECPH
where ECPH.EmployeeID = E.EmployeeID
order by ModifiedDate Desc) as JobTitle
,convert(varchar, E.[HireDate], 106) as JoinDate
,isnull(E.[Photo],'') as Photo
,isnull(E.[CurrentFlag], '') as CurrentFlag
,E.[AppraisingManagerID] as AppraisingManagerID
,E.[ModeratingManagerID] as ModeratingManagerID
,isnull(E.[CategoryID], '') as CategoryID
,isnull(E.[IsInternational], '') as IsInternational
,isnull(E.[AppraisalForm],'') as AppraisalForm
,isnull(E.[DesignationPoint],'') as DesignationPoint
,isnull(C.[Code],'') as schoolcode
,isnull(D.[Name],'') as departmentname
,isnull(O.[Code],'') as officeCode
,isnull(EL.SickLeave, '') as SickLeave
,isnull(EL.AnnualLeave, '') as AnnualLeave
,(Select ELevel.[Name] from Humanresources.EmployeeLevel Elevel, HumanResources.EmployeeCareerPathHistory ECPH2
where Elevel.ElevelID = ECPH2.ElevelID and
ECPH2.EmployeeID = E.EmployeeID and ECPH2.ModifiedDate =
(Select Top(1) ECPH.ModifiedDate
from HumanResources.EmployeeCareerPathHistory ECPH
where ECPH.EmployeeID = E.EmployeeID
order by ModifiedDate Desc) ) as WorkLevel
,isnull(EL.EmploymentType, '') as EmployementType
,isnull(EL.StartDate, '') as EmployeeContractDate
,(Select Top(1) ECPH.EffectiveDate
from HumanResources.EmployeeCareerPathHistory ECPH
where ECPH.EmployeeID = E.EmployeeID
order by ModifiedDate Desc) as CareerJoinDate
, Confirmer

FROM

[RafflesEdu].[HumanResources].[Employee] E
HumanResources.EmployeeContract EL,
HumanResources.EmployeeDepartmentHistory EDH,
HumanResources.Department D,
HumanResources.Company C,
Humanresources.Office O

WHERE

E.EmployeeId = EL.EmployeeID
and EDH.EmployeeID = E.EmployeeID
and D.DepartmentID = EDH.DepartmentID
and EDH.CompanyID = C.CompanyID
and EDH.StartDate = (Select top (1) StartDate from HumanResources.EmployeeDepartmentHistory EDH2
where EDH2.EmployeeID = E.EmployeeID and enddate is null order by StartDate Desc)
and E.Currentflag = 'true'
and e.OfficeID = O.OfficeID
and EL.StartDate = (Select top(1) StartDate from humanResources.EmployeeContract EC where EC.EmployeeID = E.EmployeeID order by StartDate desc)

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-08-20 : 23:18:31

I apologies. I figure this out -

SELECT distinct E.[EmployeeID]
,E.[ManagerID]as ManagerID
,E.[ManagerID2] as ManagerID2
,isnull(E.[NationalIDNumber],'') as NationalIDNumber
,isnull(E.[Name],'') as EmployeeName
,isnull(E.[EmailAddress],'') as EmailAddress
,isnull(E.[Phone],'') as Phone
,isnull(E.[Mobile], '') as Mobile
,convert(varchar, E.[BirthDate], 106) as birthdate
,isnull(E.[BirthCountryRegionCode],'') as BirthCountryRegionCode
,isnull(E.[NationalityCountryRegionCode],'') as NationalityCountryRegionCode
,isnull(E.[MaritalStatus], '') as MaritalStatus
,isnull(E.[Gender],'') as Gender
,(Select Top(1) ECPH.Designation
from HumanResources.EmployeeCareerPathHistory ECPH
where ECPH.EmployeeID = E.EmployeeID
order by ModifiedDate Desc) as JobTitle
,convert(varchar, E.[HireDate], 106) as JoinDate
,isnull(E.[Photo],'') as Photo
,isnull(E.[CurrentFlag], '') as CurrentFlag
,E.[AppraisingManagerID] as AppraisingManagerID
,E.[ModeratingManagerID] as ModeratingManagerID
,isnull(E.[CategoryID], '') as CategoryID
,isnull(E.[IsInternational], '') as IsInternational
,isnull(E.[AppraisalForm],'') as AppraisalForm
,isnull(E.[DesignationPoint],'') as DesignationPoint
,isnull(C.[Code],'') as schoolcode
,isnull(D.[Name],'') as departmentname
,isnull(O.[Code],'') as officeCode
,isnull(EL.SickLeave, '') as SickLeave
,isnull(EL.AnnualLeave, '') as AnnualLeave
,(Select ELevel.[Name] from Humanresources.EmployeeLevel Elevel, HumanResources.EmployeeCareerPathHistory ECPH2
where Elevel.ElevelID = ECPH2.ElevelID and
ECPH2.EmployeeID = E.EmployeeID and ECPH2.ModifiedDate =
(Select Top(1) ECPH.ModifiedDate
from HumanResources.EmployeeCareerPathHistory ECPH
where ECPH.EmployeeID = E.EmployeeID
order by ModifiedDate Desc) ) as WorkLevel
,isnull(EL.EmploymentType, '') as EmployementType
,isnull(EL.StartDate, '') as EmployeeContractDate
,(Select Top(1) ECPH.EffectiveDate
from HumanResources.EmployeeCareerPathHistory ECPH
where ECPH.EmployeeID = E.EmployeeID
order by ModifiedDate Desc) as CareerJoinDate
, Confirmer
FROM [HumanResources].[Employee] E left join
[Procurement].[PurchaseApprovalSetup] PAS on (E.EmployeeID = PAS.EmployeeID)
inner join HumanResources.EmployeeContract EL on (E.EmployeeId = EL.EmployeeID)
inner join HumanResources.EmployeeDepartmentHistory EDH on ( EDH.EmployeeID = E.EmployeeID )
inner join HumanResources.Department D on (D.DepartmentID = EDH.DepartmentID)
inner join HumanResources.Company C on (EDH.CompanyID = C.CompanyID )
inner join Humanresources.Office O on (e.OfficeID = O.OfficeID)
WHERE

EDH.StartDate = (Select top (1) StartDate from HumanResources.EmployeeDepartmentHistory EDH2
where EDH2.EmployeeID = E.EmployeeID and enddate is null order by StartDate Desc)
and E.Currentflag = 'true'

and EL.StartDate = (Select top(1) StartDate from humanResources.EmployeeContract EC where EC.EmployeeID = E.EmployeeID order by StartDate desc)

For those who want to see multiple join with left joins may like to follow my example. Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources