Error - column specified multiple times

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

Error - column specified multiple times

Author

Topic

abc001
Starting Member

2 Posts

Posted - 2012-08-28 : 14:41:58

Hi Everyone,

I'm new to SQL and i tried to run the following script:

SELECT eesA.EmployeeId, eesA.effectiveStart, eesB.EmployeeId, eesB.EffectiveStart, EesA.EmployeeNumber as AfterEmpNumber, EesB.employeenumber as BeforeEmpNumber
FROM employeeemploymentstatus eesA
JOIN employeeemploymentstatus eesB
ON (eesB.EmployeeId = (SELECT TOP 1 (z.employeeId)
FROM employeeemploymentstatus z
WHERE z.effectiveStart < eesA.effectiveStart and eesB.employeeId = eesA.employeeId and eesB.employeeid = z.employeeid))
WHERE
eesA.effectiveStart <> eesB.effectiveStart
AND
(
(eesA.PayClassId <> eesB.PayClassId)
OR
(eesA.employeeNumber <> eesB.EmployeeNumber)
)

but i get the following errors:

The column 'EmployeeId' was specified multiple times for 'topicQuery'.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "eesA.effectiveStart" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "eesB.EffectiveStart" could not be bound.
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "eesA.effectiveStart" could not be bound.

Can someone please help me figure out whats wrong?
REally appreciate it.

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-28 : 15:20:05

Use aliases for the column names in the select list to avoid repeating the same name as shown in red below:

SELECT
	eesA.EmployeeId AS EESA_EmployeeID,
	eesA.effectiveStart AS EESA_effectiveStart,
	eesB.EmployeeId AS EESB_EmployeeID,
	eesB.EffectiveStart AS EESB_effectiveStart,
	EesA.EmployeeNumber AS AfterEmpNumber,
	EesB.employeenumber AS BeforeEmpNumber
FROM
	employeeemploymentstatus eesA
	JOIN employeeemploymentstatus eesB
		ON  (
		        eesB.EmployeeId = (
		            SELECT
		            	TOP 1(z.employeeId)
		            FROM
		            	employeeemploymentstatus z
		            WHERE
		            	z.effectiveStart < eesA.effectiveStart
		            	AND eesB.employeeId = eesA.employeeId
		            	AND eesB.employeeid = z.employeeid
		        )
		    )
WHERE
	eesA.effectiveStart <> eesB.effectiveStart
	AND (
	        (eesA.PayClassId <> eesB.PayClassId)
	        OR (eesA.employeeNumber <> eesB.EmployeeNumber)
	    )

abc001
Starting Member

2 Posts

Posted - 2012-08-29 : 13:49:28

Thank you!! - i will try this out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources