如何克服联接条件中的空值 [英] how to overcome with null value in join condition
问题描述
对于我的应用程序,
我需要从两个表Member和Account中获取数据,对于其中的特定Member_Id值,我想获取Member和Account表Details.但是这里的一个member_Id在Account表中没有帐户.对于这个无效的帐户,我需要将Member Details和Account_No一起获取为NULL.
但对于另一个Member_Id,则存在1个或多个帐户,
我需要获取以上两个条件都必须满足的详细信息.
我编写了以下过程,但仅获取JOIN条件的值
for my application ,
i need to get data from two tables Member and Account , in which for a particular Member_Id value i want to get Member and Account tables Details . but here for one member_Id there is no accounts in Account table .for this invalid account i need to get Member Details along with Account_No as NULL .
but for another Member_Id there is 1 or more accounts is present ,
i need to get details in which both of the above two conditions must satisfy.
i written the below procedure but i am getting values for the JOIN condition only
ALTER PROCEDURE [dbo].[sp_CSP_GetEMIloanDetails]
@ID int,
@Customer_Type TinyInt
AS
BEGIN
DECLARE @Member_Id INT = 0
DECLARE @Valid_Acc INT = 0
IF @Customer_Type = 1
BEGIN
SET @Member_Id = (SELECT Member_Id FROM GLNo_Registration WHERE ID = @ID AND Is_Active = 1)
IF @Member_Id = 0 OR @Member_ID IS NULL
BEGIN
SELECT 'Invalid ID' AS Valid_Msg
RETURN
END
END
ELSE
BEGIN
SET @Member_Id = (SELECT ID FROM Member WHERE ID = @ID AND Is_Active = 1)
IF @Member_Id = 0 OR @Member_ID IS NULL
BEGIN
SELECT 'Invalid ID' AS Valid_Msg
RETURN
END
END
SET @Valid_Acc =(SELECT COUNT(A.Account_No) FROM Account A,Member M WHERE A.Member_Id=M.ID AND M.ID=@Member_Id)
IF @Valid_Acc IS NULL
BEGIN
SELECT M.First_Name AS Name,
M.DOB,
M.Occupation,
(case when m.TDS=1 then m.TDS_RefNo end) as Pan_Number,
(SELECT A.Account_No FROM Account A WHERE A.Member_Id=M.ID)AS Account_No
FROM Member M,Account A WHERE A.Member_Id =M.ID AND M.ID=@Member_Id
END
ELSE
BEGIN
SELECT M.First_Name AS Name,M.ID,
M.DOB,
M.Occupation,
(case when m.TDS=1 then m.TDS_RefNo end) as Pan_Number,
A.Account_No
FROM Member M JOIN Account A ON A.Member_Id =M.ID AND M.ID=@Member_Id
END
END
推荐答案
您需要使用的是外部联接而不是普通联接.
请参阅本文以获取信息.它适用于SQL2000,但仍然有效,并且也与更新版本相关.
http://msdn.microsoft.com/en-us/library/aa213228 (v = sql.80).aspx [
What you need to use is an outer join instead of a plain join.
See this article for information. It is for SQL2000, but is still valid and relevant to newer versions as well.
http://msdn.microsoft.com/en-us/library/aa213228(v=sql.80).aspx[^]
在其他情况下尝试以下查询
try the below query in your last else condition
SELECT M.First_Name AS Name,M.ID,
M.DOB,
M.Occupation,
(case when m.TDS=1 then m.TDS_RefNo end) as Pan_Number,
A.Account_No
FROM Member M LEFT JOIN Account A ON A.Member_Id =M.ID AND M.ID=@Member_Id
这篇关于如何克服联接条件中的空值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!