加入sql server中的多个表时出现问题 [英] Problem in joining the multiple tables in sql server

问题描述

大家好，



我在加入sql server中的多个表时遇到了问题。



i有三张桌子（Emp，Equipment& job）...



&我加入他们是这样的：

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

此查询正在执行多行。



i也尝试了'内部联接'。



例如，在工作ID 83我收到了，



83，e1，emp1

83，e2，emp1

83，e3 ，emp1

83，e1，emp2

83，e2，emp2

83，e3，emp2



但我正在寻找：



83，e1，emp1

83，e2，emp2

83，e3，null



任何人都可以帮助我

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

推荐答案

这看起来像是笛卡尔产品问题：您的表关系会在您的联接中产生多行。要真正解决问题，我需要查看架构和可能的数据。



JoinID的JoinID是否正确 - 它看起来对我很怀疑（如每个digimanaus'评论）。应该是EmployeeCode吗？

This looks like a "cartesian product" issue: your table relationships produce multiple rows across your joins. To really solve the problem, I'd need to see the schema and possibly the data.

Is the join LocationID to JobID correct - it looks suspect to me (as per digimanaus' comment). Should it maybe be EmployeeCode?

这篇关于加入sql server中的多个表时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！