The correlation name "MR" is specified several times in the FROM clause

Question

Create PROCEDURE [dbo].[K_RT_FixedAsset]
@fromdate datetime,
@todate datetime
AS
BEGIN

SET NOCOUNT ON;

 select convert(varchar,FT.date,103)as date, MR.branch as frombranch,PD.productname as product,FT.vehicleno,FT.dcno,FT.tobranch 
from K_RT_FixedAssetTransfer FT
inner join K_RT_MasterRetailStores MR on MR.sno = FT.frombranch 
inner join K_RT_ProductDetails PD on PD.sno=FT.product
inner join K_RT_MasterRetailStores MR on MR.sno = FT.tobranch 
where FT.date between @fromdate and  @todate


END

Answer 1

You must use a different alias for each K_RT_MasterRetailStores connection.

Answer 2

Others say they use a different alias, but it may not be clear to you what they mean:

 select convert(varchar,FT.date,103)as date, MR_from.branch as frombranch,
        PD.productname as product,FT.vehicleno,FT.dcno,FT.tobranch 
from K_RT_FixedAssetTransfer FT
inner join K_RT_MasterRetailStores MR_from --<-- First alias
           on MR_from.sno = FT.frombranch 
inner join K_RT_ProductDetails PD on PD.sno=FT.product
inner join K_RT_MasterRetailStores MR_to --<-- Second alias
           on MR_to.sno = FT.tobranch 
where FT.date between @fromdate and  @todate

I note that you are not using anything from the second connection in the list SELECT. Perhaps you wanted to use this instead FT.tobranch?

 select convert(varchar,FT.date,103)as date, MR_from.branch as frombranch,
        PD.productname as product,FT.vehicleno,FT.dcno,MR_to.branch 
from K_RT_FixedAssetTransfer FT
inner join K_RT_MasterRetailStores MR_from on MR_from.sno = FT.frombranch 
inner join K_RT_ProductDetails PD on PD.sno=FT.product
inner join K_RT_MasterRetailStores MR_to on MR_to.sno = FT.tobranch 
where FT.date between @fromdate and  @todate

Answer 3

K_RT_MasterRetailStores .

Depending on whether this connection should be doubled you need either

• Remove second connection
• Use a different alias