Being smart or bug?(mssql2000)

Why this query works:

use northwind
select distinct em.EmployeeID
from employees as em
inner join EmployeeTerritories as et
on em.EmployeeID = et.employeeid
where e.m.country='uk'

Pay attention on the las line.
As you see it is written "e.m.country" instead of "em.country".

This is a bug in the parser for SQL Server 2000. It is present in SQL Server 2005 also but only in compatibility modes below 90. If your database is in 90 compat mode then you will get an error like:

.Net SqlClient Data Provider: Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "e.m.country" could not be bound.

The behavior is retained in older compat modes for backward compatibility reasons and to ensure that existing code works fine upon upgrade to SQL Server 2005. Hope this clarifies.

Beginner question, stored procedure

Hello,

I have 2 tables: Employees(EmployeeID, FirstName, LastName),
EmployeeApplication(CreatedByID, EmployeeID,Date1, Date2, Content...)
I need to create stored procedure that will return employee First and
Last names (plus some other columns) instead *both* ID's in
EmployeeApplication.
I know how to do it with only one - I am using simple join, but with
two I have no idea...
Can anyone help me ?

Richard...Hi Richard,

You simply add one more join for the Employees table to get the names for
the other ID, something like this:

SELECT A.EmployeeID,
E.FirstName AS emp_fname,
E.LastName AS emp_lname,
A.CreatedByID,
C.FirstName AS creator_fname,
C.LastName AS creator_lname,
A.Date1,
A.Date2,
A.Content
FROM EmployeeApplication AS A
JOIN Employees AS E
ON A.EmployeeID = E.EmployeeID
JOIN Employees AS C
ON A.CreatedByID = C.EmployeeID

This is assuming the CreatedByID column is referring to the EmployeeID
column from the Employees table. If that is not the case then you have to
replace the Employees table in the second join with the appropriate table
and the appropriate column for the join.

HTH,

Plamen Ratchev
http://www.SQLStudio.com