The correlation name 'MR' is specified multiple times in a 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 join of K_RT_MasterRetailStores.

Answer 2

The others are saying to use a different alias, but perhaps it's not 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're not using anything from the second join in the SELECT list. Maybe you wanted to use that instead of 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

You've joined K_RT_MasterRetailStores on twice with the same alias.

Depending on whether this join needs to be in twice you either need to

• Delete the second join
• Use a different alias