G
guyinkalamazoo3
Guest
I have the following and it all was working fine until I included locations.
CurrentEmployeeList = From emp In empdb.CurrentLawsonADFiles
From ext In empdb.EmployeeExtensions.Where(Function(ext) ext.EmpID = emp.EmpID).DefaultIfEmpty()
From m In empdb.CurrentLawsonADFiles.Where(Function(m) emp.ManagerID = m.EmpID).DefaultIfEmpty()
From el In empdb.EmployeeLocations.Where(Function(el) emp.EmpID = el.EmpID).DefaultIfEmpty()
From l In empdb.Locations.Where(Function(l) el.LocationID = l.LocationID).DefaultIfEmpty()
Join loc In empdb.Locations On l.LocationID Equals loc.LocationID
Order By emp.LastName, emp.FirstName Ascending
Select New Employee With {
.FName = emp.FirstName,
.MI = emp.MI.TrimEnd,
.LName = emp.LastName,
.FullName = emp.LastName & ", " & emp.FirstName,
.Job = emp.JobDescr,
.Location = loc.LocationTitle,
.Dept = emp.DeptName,
.Empid = emp.EmpID,
.Manager = m.LastName & ", " & m.FirstName,
.Email = emp.Email,
.Extension = ext.Extension}
I was having issues where employees without phone extensions were not showing. Now I need to show their location and there is a linking table with just empID and locID, but I need to join the Locations table on that locID to get the Location Title. This query currently DOES work with those who do have a location, but does not with employees that do not.
Thanks for any help and information
Brad Allison
Continue reading...
CurrentEmployeeList = From emp In empdb.CurrentLawsonADFiles
From ext In empdb.EmployeeExtensions.Where(Function(ext) ext.EmpID = emp.EmpID).DefaultIfEmpty()
From m In empdb.CurrentLawsonADFiles.Where(Function(m) emp.ManagerID = m.EmpID).DefaultIfEmpty()
From el In empdb.EmployeeLocations.Where(Function(el) emp.EmpID = el.EmpID).DefaultIfEmpty()
From l In empdb.Locations.Where(Function(l) el.LocationID = l.LocationID).DefaultIfEmpty()
Join loc In empdb.Locations On l.LocationID Equals loc.LocationID
Order By emp.LastName, emp.FirstName Ascending
Select New Employee With {
.FName = emp.FirstName,
.MI = emp.MI.TrimEnd,
.LName = emp.LastName,
.FullName = emp.LastName & ", " & emp.FirstName,
.Job = emp.JobDescr,
.Location = loc.LocationTitle,
.Dept = emp.DeptName,
.Empid = emp.EmpID,
.Manager = m.LastName & ", " & m.FirstName,
.Email = emp.Email,
.Extension = ext.Extension}
I was having issues where employees without phone extensions were not showing. Now I need to show their location and there is a linking table with just empID and locID, but I need to join the Locations table on that locID to get the Location Title. This query currently DOES work with those who do have a location, but does not with employees that do not.
Thanks for any help and information
Brad Allison
Continue reading...