基于存储过程中Id的SQL联接表

Question

我需要使用JOIN存储过程提取索赔ID的所有策略的列。

伪代码：从策略(tbl(中选择*，其中parties.id=policy.policyNumber.

以下是我迄今为止所拥有的。。。

CREATE PROCEDURE [dbo].[usp_GetPolicyForClaimentByPolicyIdNumber]
(
@IdNumber varchar(255) = null
)
AS
BEGIN
SELECT *
FROM [BinderCurrent].[Policy]
LEFT JOIN [BinderCurrent].[Policy] ON ([BinderCurrent].[Parties].Id = [BinderCurrent].[PolicyRoles].PolicyId)
WHERE   [BinderCurrent].[PolicyRoles].PolicyRoleTypeId = 40
AND (@IdNumber IS NULL OR [BinderCurrent].[Parties].IdNumber LIKE ''+@IdNumber+'%') 
ORDER BY Id DESC
END

Answer 1

使用ON子句进行筛选：

SELECT *
FROM [BinderCurrent].[Policy] LEFT JOIN 
[BinderCurrent].[Policy] 
ON ([BinderCurrent].[Parties].Id = [BinderCurrent].[PolicyRoles].PolicyId AND
[BinderCurrent].[PolicyRoles].PolicyRoleTypeId = 40
WHERE (@IdNumber IS NULL OR [BinderCurrent].[Parties].IdNumber LIKE ''+@IdNumber+'%')
ORDER BY Id DESC;

注意：表[BinderCurrent].[PolicyRoles]也应与JOINs一起出现。这假设这是查询的一部分。