Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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
Posted 26-Sep-12 4:30am
Edited 26-Sep-12 4:36am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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[^]
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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
  Permalink  

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

  Print Answers RSS
0 _Amy 310
1 OriginalGriff 294
2 Nirav Prabtani 268
3 Sergey Alexandrovich Kryukov 211
4 CHill60 150
0 OriginalGriff 7,889
1 Sergey Alexandrovich Kryukov 7,006
2 Maciej Los 4,014
3 Peter Leow 3,708
4 CHill60 2,762


Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 26 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100