Post reply

Schema Changes from SQL 2000 to 2005 and Table Joins

  • January 26, 2010 at 12:28 pm

    #140913

    Hi, I am used to working with TSQL code developed for SQL Server 2000 even though where I work we have now converted all our database servers to SQL Server 2005. Note the databases I work with were developed for use in SQL 2000 or earlier. So now I am using SQL Server 2005 books online as a reference and I was reviewing an example on how to use the Exists keyword. Then I noticed that I might be missing something about the change in the use of Schemas in SQL Server 2005.

    Here is the sample code from SQL 2005 books online that I have a question about:

    USE AdventureWorks ;

    GO

    SELECT DISTINCT c.FirstName, c.LastName, e.DepartmentID

    FROM Person.Contact c JOIN HumanResources.Employee e

    ON e.ContactID = c.ContactID WHERE EXISTS

    (SELECT *

    FROM HumanResources.Department d

    WHERE e.DepartmentID = d.DepartmentID

    AND d.Name = 'Purchasing') ;

    GO

    What I see is in the select list is two columns from contact table and then the departmentID which looks to be in the Employee table based on the "e" table alias. The problem I have looking at the AdventureWorks diagram the DepartmentID column is not in the Employee table. DepartmentID is in the EmployeeDepartmentHistory and Department tables. So my question is this join above legal due to the change in how Schemas are used from SQL 2000 to 2005?

  • January 26, 2010 at 1:32 pm

    #1108920

    Ran your code using my SQL Server 2005 and got the following error messages.

    Msg 207, Level 16, State 1, Line 6

    Invalid column name 'DepartmentID'.

    Msg 207, Level 16, State 1, Line 1

    Invalid column name 'DepartmentID'.

    It appears that either both of us have an outdated copy of AdventureWorks (which is not likely), or you have identified an error in Books OnLine

    Just checked my copy on 2008 and same error returned.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • January 26, 2010 at 1:49 pm

    #1108931

    Thanks for the quick response. You know I did not even think to try and run it, I just assumed it was correct since it was in Books on line. I ran it and got the same error. I guess that answers my question.

    ChrisG

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply