CODEWITHSUNDEEP
sqlsql-serverinner-joinwhere-clause
coderookie
coderookie

Reputation: 15

WHERE Clause SAME ID different TABLES vs JOIN

I read several postings but couldn't find one addressing the issue. IN a T-SQL query I'm to do the following:

list the (employee last name, project number, assigned job from the (Employee AND Assignment tables) -- where the employee table –(employee number) is equal to the assignment table (employee number).

Does this require an inner join, or can it be done with WHERE clause?

I tried the following but got back ambiguous errors:

SELECT  
    EMP_LNAME, PROJECT_NUM, ASSIGN_JOB
FROM 
    ASSIGNMENT, EMPLOYEE
WHERE 
    EMP_ID = EMP_ID

Upvotes: 0

Views: 90

Answers (1)

Nate Anderson
Nate Anderson

Reputation: 690

You got an ambiguous error because you need to alias your tables. But to answer your question, your current query is the old style of doing a join and will work the same. I prefer the newer style inner join because it is more easily readable, imo.

SELECT e.EMP_LNAME, a.PROJECT_NUM, a.ASSIGN_JOB 
FROM ASSIGNMENT a,EMPLOYEE e 
WHERE a.EMP_ID = e.EMP_ID

effectively the same as:

SELECT e.EMP_LNAME, a.PROJECT_NUM, a.ASSIGN_JOB 
FROM ASSIGNMENT a
    INNER JOIN EMPLOYEE e ON a.EMP_ID = e.EMP_ID

Upvotes: 5

Related Questions