Two Inner join on the same field to get same data

1 vote

1 answer

459 views

sql-server sql-server-2008 join ssrs ssrs-2008-r2

                          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?
                        

Asked by Alireza (13 rep)

May 30, 2018, 01:50 PM
Last activity: May 30, 2018, 02:53 PM

Two Inner join on the same field to get same data

Related Questions