how to overcome with null value in join condition

See more:

SQL-Server

hi,

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