Problem in joining the multiple tables in sql server

See more:

SQL

SQL-Server-2008R2

Hi guys,

I'm getting the problem in joining of multiple tables in sql server.

i have three tables(Emp, Equipment & job)...

& i'm joining them like this:

alter proc Sp_GetEmpEqp
@jobid int
as

-- Sp_GetEmpEqp 83

declare @EqpCode int

SELECT	
		j.JobID,
		eq.EquipmentCode,
		e.EmployeeCode,
	    e.EmployeeName,
		CASE WHEN dbo.GetEmployeeShift(e.EmployeeID) = 0 THEN 'Day'
			 WHEN dbo.GetEmployeeShift(e.EmployeeID) = 1 THEN 'Night'
			 END AS 'Shift'
 FROM Job j
left join Employee e on e.LocationID = j.JobID
left join Equipment eq on eq.JobID = j.JobID
where JobCategoryID = 3 and j.[Status] = 0 and j.JobID = @jobid

this query is executing multiple rows.

i have tried it with 'inner join' as well.

for eg, on job id 83 i'm getting,

83, e1, emp1
83, e2, emp1
83, e3, emp1
83, e1, emp2
83, e2, emp2
83, e3, emp2

but i'm looking for:

83, e1, emp1
83, e2, emp2
83, e3, null

can anyone plzzz help me