SQL Server 2008
Error Message: The column ‘LOCK_ID’ was specified multiple times for ‘BB’.
select BB.LDC_ACCT_ID from
select * from STUDENTS S
JOIN RECORDS R
on S.STUDENT_ID = R.STUDENT_ID
) as TEMP
If you run this query in SQL SERVEr 2008, you will get the above error.
The problem is
select * from <– this line
What is happening is (possibly), both students table and records table have the same field name. When you use * to put two fields with the same name in one table, you get this error. Note that the subquery runs fine by itself. But when you put it inside TEMP table, it causes the error.
select S.STUDENT_ID, …. from <– this line
This way not duplicate fields are inserted in the TEMP table.