CODEWITHSUNDEEP
t-sqlsql-server-2005
Lill Lansey
Lill Lansey

Reputation: 4915

What is wrong with this t-sql query using left join?

I have two sqlserver 2005 tables: Task table and Travel table.

I am trying to write a query that selects all records from the Task table for a specific Empid, and if the Task is travel, also get the mileage value from another table.

I expect only one row to be selected, but all rows in the Task table are returned.

The query I am using is 

Select Tasklog.TaskLogPkey, Tasklog.Empid , Tasklog.Task, TravelLog.Mileage from 
Tasklog
left join TravelLog
on   
   TaskLog.EmpId = 12 and TaskLog.Task ='Travel'   and 
   TaskLog.TaskLogPkey = TravelLog.TaskLogPkey

But it makes no difference if I don’t include 

  -- TaskLog.EmpId = 12 and TaskLog.Task ='Travel'   and

What am I doing wrong?

Clearly the following doesn't work:

Select Tasklog.TaskLogPkey, Tasklog.Empid , Tasklog.Task, TravelLog.Mileage from 
Tasklog where   TaskLog.EmpId = 12 and TaskLog.Task ='Travel' 
left join TravelLog
on   
   TaskLog.TaskLogPkey = TravelLog.TaskLogPkey

These are the tables I am using: 

Create table TaskLog(
TaskLogPkey int not null,
Empid  int,
Task varchar(30)
)

Insert Tasklog values(1,12,'Sales')
Insert Tasklog values(2,4,'Travel')
Insert Tasklog values(3,63,'Meeting')
Insert Tasklog values(4,12,'Travel')
Insert Tasklog values(5,12,'Email')
Insert Tasklog values(6,4,'Travel')
Insert Tasklog values(7,63,'Meeting')
Insert Tasklog values(8,12,'PhoneCall')


Create table TravelLog(
TaskLogPkey int not null,
Mileage  int
)

Insert TravelLog values(2,45)
Insert TravelLog values(4,25)
Insert TravelLog values(6,18)

Upvotes: 0

Views: 83

Answers (3)

Taryn
Taryn

Reputation: 247680

You should try placing a WHERE clause with the additional criteria:

Select Tasklog.TaskLogPkey, Tasklog.Empid , Tasklog.Task, TravelLog.Mileage 
from Tasklog
left join TravelLog
    on  TaskLog.TaskLogPkey = TravelLog.TaskLogPkey
where TaskLog.EmpId = 12 
    and TaskLog.Task ='Travel'

See SQL Fiddle with Demo

Upvotes: 2

Germann Arlington
Germann Arlington

Reputation: 3353

TaskLog.EmpId = 12 and TaskLog.Task ='Travel' is part of the selection criteria, NOT join criteria.

And "Charles Bretana" beats me by less than 1 minute.

Upvotes: 2

Charles Bretana
Charles Bretana

Reputation: 146499

When you perform an Outer join ( a left join is a Left Outer Join), you get all the records in the inner side of that join, --- no matter what predicates (filters) you specify on the other outer side in the join conditions.
To filter the other side you need to add a Where clause. 

Select t.TaskLogPkey, t.Empid , t.Task, v.Mileage 
From Tasklog t 
    Left Join TravelLog v 
         On t.TaskLogPkey = v.TaskLogPkey 
Where t.Task ='Travel' 
    And t.EmpId = 12

Upvotes: 3

Related Questions