The correlation name 'ej' is specified multiple times in a FROM clause

Im trying to do this but keep getting the error shown in the title

Not sure what im missing
select DISTINCT
e.EmployeeID,
es.EmployeeStatus AS Status,
e.EmployeeDescNoID AS Emp_Name,
e.WindowsUserName AS LoginID,
e.DisplayEmployeeID AS UserID,
e.ExtNoUD AS Extension,
e.FFWMobileUD AS FFWMobile,
e.WorkEmail AS Email,
-- profitcentre
-- department
e.EliteNoUD AS SectionNumber,
e.SecretaryNameUD AS Secretary,
e.LocationUD AS Location,
ej.PostID AS Position
 
FROM [Cascade].dbo.Employee e
 
JOIN [Cascade].dbo.EmployeeJobs ej
  ON e.EmployeeID = ej.EmployeeID
 
JOIN [Cascade].dbo.Employee_Status es
  ON e.EmployeeID = es.EmployeeID
 
LEFT OUTER JOIN (select t1.JobTitle, t1.EmployeeID
                   from [Cascade].dbo.EmployeeJobs t1 
                   join (select employeeId, min(Sequence) as sequence 
                           from [Cascade].dbo.EmployeeJobs 
                          group by employeeID
                        ) t2
                     on t1.employeeid = t2.employeeID 
                    and t1.Sequence  = t2.Sequence
                 ) ej
  ON e.EmployeeID = ej.EmployeeID
 
WHERE es.EmployeeStatus = 'Current'

Open in new window

mooriginalAsked:
Who is Participating?
 
appariConnect With a Mentor Commented:
you are using same alias name ej in two places,
change one of them to a different name like ej1

select DISTINCT
e.EmployeeID,
es.EmployeeStatus AS Status,
e.EmployeeDescNoID AS Emp_Name,
e.WindowsUserName AS LoginID,
e.DisplayEmployeeID AS UserID,
e.ExtNoUD AS Extension,
e.FFWMobileUD AS FFWMobile,
e.WorkEmail AS Email,
-- profitcentre
-- department
e.EliteNoUD AS SectionNumber,
e.SecretaryNameUD AS Secretary,
e.LocationUD AS Location,
ej.PostID AS Position
 
FROM [Cascade].dbo.Employee e
 
JOIN [Cascade].dbo.EmployeeJobs ej
  ON e.EmployeeID = ej.EmployeeID
 
JOIN [Cascade].dbo.Employee_Status es
  ON e.EmployeeID = es.EmployeeID
 
LEFT OUTER JOIN (select t1.JobTitle, t1.EmployeeID
                   from [Cascade].dbo.EmployeeJobs t1 
                   join (select employeeId, min(Sequence) as sequence 
                           from [Cascade].dbo.EmployeeJobs 
                          group by employeeID
                        ) t2
                     on t1.employeeid = t2.employeeID 
                    and t1.Sequence  = t2.Sequence
                 ) ej1
  ON e.EmployeeID = ej1.EmployeeID
 
WHERE es.EmployeeStatus = 'Current'

Open in new window

0
 
erikTsomikSystem Architect, CF programmer Commented:
appari: is correct
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.