Two Inner join on the same field to get same data I'm developing a Report in SSRS for MS CRM 2011.

There is a table named StringMapBase which contains Option Set we have declared in different entities.
This table has Value and AttributeValue column.
Every option Set I have created in any entity, has the Value(which is the name of that) and AttributeValue(which is the name I have choosed for that option set) in the StringMapBase table.
If I named an Option Set, I should read the the name from StringMapBase.
Like this : 

    select  
SalesOrder.new_Order_Code,
orderreceiptstep.new_NextFollow,
orderreceiptstep.new_realization,
StringMapBase.Value,
orderreceiptstep.AccountIdName, 
orderreceiptstep.TotalReceivableAmount,
orderreceiptstep.name,  

orderreceiptstep.DueDate,
orderreceiptstep.ReceiptStepTypeIdName,
StringMapBase.Value, 

from orderreceiptstep
inner join StringMapBase on (orderreceiptstep.OrderReceiptStepStatus=StringMapBase.AttributeValue and StringMapBase.LangId=1065)


It gives me the name of one Options Set.
Now, The problem is that I have another Option set on the same entity that should get the name of that.
It means I need another Inner Join Like above,  Like this : 

    inner join StringMapBase on (orderreceiptstep.OrderReceiptStepPrioriy=StringMapBase.AttributeValue and StringMapBase.LangId=1065)


In the first  I Used orderreceiptstep.OrderReceiptStepStatus of orderreceiptstep table and in the second I used orderreceiptstep.OrderReceiptStepPrioriy in the Inner Join clause.

I want to Have These two joins on the same field, Like this : 

        select  
SalesOrder.new_Order_Code,
orderreceiptstep.new_NextFollow,
orderreceiptstep.new_realization,
StringMapBase.Value,
orderreceiptstep.AccountIdName, 
orderreceiptstep.TotalReceivableAmount,
orderreceiptstep.name,  

orderreceiptstep.DueDate,
orderreceiptstep.ReceiptStepTypeIdName,
StringMapBase.Value, 

from orderreceiptstep
inner join StringMapBase on (orderreceiptstep.OrderReceiptStepStatus=StringMapBase.AttributeValue and StringMapBase.LangId=1065)
    inner join StringMapBase on (orderreceiptstep.OrderReceiptStepPrioriy=StringMapBase.AttributeValue and StringMapBase.LangId=1065)


This means StringMapValue in the first Should return something, and in the second Inner Join must return another thing.
How Can I do that?
Ah, you're right there. 

It's easier if you use aliases. SQL Server needs some way to distinguish between the copies of the tables.

    select o.*, s1.Value, s2.Value
    from orderreceiptstep o
    inner join StringMapBase s1 on o.OrderReceiptStepStatus=s1.AttributeValue
    inner join StringMapBase s2 on o.OrderReceiptStepPrioriy=s2.AttributeValue
    where s1.LangID = 1065
    and s2.LangID = 1065


You might get better performance with a cte like this:

    with StringMaps as (select AttributeValue, Value from StringMapBase where LangID = 1065)

    select o.*, s1.Value, s2.Value
    from orderreceiptstep o
    inner join StringMaps s1 on inner join StringMapBase on o.OrderReceiptStepStatus=s1.AttributeValue
    inner join StringMaps s2 on o.OrderReceiptStepPrioriy=s2.AttributeValue