Click here to Skip to main content
14,974,899 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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


SQL
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
Posted
Updated 26-Sep-12 4:36am
v2

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

SQL
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
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900