如何克服联接条件中的空值

问题描述

对于我的应用程序，

我需要从两个表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

推荐答案

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

这篇关于如何克服联接条件中的空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！