Inner join for table

Garima Bansal

4y919SQL

I want to add inner joinin which "IsParcelAllotment='N' " and where i have to apply

ALTER PROCEDURE [dbo].[store procedure name]     
@SData nvarchar(50),        
@ID bigint,    
@DID int   ,
@User varchar(50)       
AS          
BEGIN          
SELECT [RowID]      
,[FID]      
,[IID]      
,[IsComplete]      
,CONVERT(VARCHAR(10),comm_Table1.CreateDate,103) as CreateDate      
,comm_Table1.ChangeDate      
,[CurrentStatus]      
,[StatusDate]
FROM [MedleaprLab].[dbo].[comm_Table1]   
 where   FID like '%'+@SData+'%' and IID=@IID and [DID]=@DivisionID    and CurrentHOD=@User
 and  
 FID not in (select top 1 FID from comm_Table2 where FID like '%'+@SData+'%'  and IsParcelAllotment='Y' )   
 and
 FID not in(
select FNo from
(
select isnull(COALESCE(RDetails,PDetail,PaDetail,EDetail,NWhom,pdetail,cdetail,ahod),0) as Value,FslCaseNo from Table3 ) as temp
where Value <> 1
)
END

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh4y ago

Accepted answer

test this

ALTER PROCEDURE [dbo].[store procedure name]       
@SData nvarchar(50),          
@ID bigint,      
@DID int   ,  
@User varchar(50)         
AS            
BEGIN            
SELECT [RowID]        
,[FID]        
,[IID]        
,[IsComplete]        
,CONVERT(VARCHAR(10),comm_Table1.CreateDate,103) as CreateDate        
,comm_Table1.ChangeDate        
,[CurrentStatus]        
,[StatusDate]  
FROM [MedleaprLab].[dbo].[comm_Table1]     
 where   FID like '%'+@SData+'%' and IID=@IID and [DID]=@DivisionID    and CurrentHOD=@User  
 and    
 FID not in (select  FID from comm_Table2 where FID like '%'+@SData+'%'  and IsParcelAllotment='Y' )     
 and  
 FID not in(  
select FNo from  
(  
select isnull(COALESCE(RDetails,PDetail,PaDetail,EDetail,NWhom,pdetail,cdetail,ahod),0) as Value,FslCaseNo from Table3 ) as temp  
where Value <> 1  
)  
END

Garima Bansal4y ago
i have list where all case are shown in a list but i want only those case which are not alloted to other user
Garima Bansal4y ago
already changed the condtion IsParcelAllotment='Y' to IsParcelAllotment='N'

Sachin Singh4y ago

Can you explain more?

ALTER PROCEDURE [dbo].[store procedure name] @SData NVARCHAR(50)  
    ,@ID BIGINT  
    ,@DID INT  
    ,@User VARCHAR(50)  
AS  
BEGIN  
    SELECT [RowID]  
        ,[FID]  
        ,[IID]  
        ,[IsComplete]  
        ,CONVERT(VARCHAR(10), comm_Table1.CreateDate, 103) AS CreateDate  
        ,comm_Table1.ChangeDate  
        ,[CurrentStatus]  
        ,[StatusDate]  
    FROM [MedleaprLab].[dbo].[comm_Table1]  
    WHERE FID LIKE '%' + @SData + '%'  
        AND IID = @IID  
        AND [DID] = @DivisionID  
        AND CurrentHOD = @User  
        AND FID NOT IN (  
            SELECT TOP 1 FID  
            FROM comm_Table2  
            WHERE FID LIKE '%' + @SData + '%'  
                AND IsParcelAllotment = 'N' --here  
            )  
        AND FID NOT IN (  
            SELECT FNo  
            FROM (  
                SELECT isnull(COALESCE(RDetails, PDetail, PaDetail, EDetail, NWhom, pdetail, cdetail, ahod), 0) AS Value  
                    ,FslCaseNo  
                FROM Table3  
                ) AS TEMP  
            WHERE Value <> 1  
            )  
END

Can't you change the condition where in line no 24