SQL Server – Two Inner Joins on the Same Field to Retrieve Data

join;sql serversql-server-2008ssrsssrs-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?

Best Answer

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

Related Solutions

Sql-server – Select the newest data (inner join + group by maybe)

select idTopic, author, deleted, idText, datePosted
from
(
    select t.idTopic, t.author, t.deleted, x.idText, x.datePosted,
           row_number() over (partition by t.idTopic order by x.datePosted desc) rn
    from topics t
    left join texts x on t.idTopic = x.idTopic -- or INNER JOIN
) z
where rn=1

Check out the ROW_NUMBER() function which is used to give a unique sequential number to each row in each "partition". In this case partition by t.idTopic tells the function to independently number each set of records of the same t.idTopic.
Within each partition, the records are numbered 1,2,3... based on a specific order, which in this case is order by x.datePosted desc - this puts the newest posts first.
LEFT JOIN also known in full as "LEFT OUTER JOIN" is used to keep all records of the table on the left side even if there are no records from the right. If you don't need to see a topic if there are no texts for it, simply drop the "LEFT" keyword.
The query is moved into a derived table and aliased (unimportantly as z) so that we can filter on it for where rn = 1. Recall that each partition can only have one row of value rn=1, which is by definition of our ORDER BY clause in ROW_NUMBER(), the row with the latest text.

SQL INNER JOIN and OUTER JOIN in Same Table – How to Implement

The query is joining 2 tables (receipts, sales) that both have a many-to-one relationship with product. This creates a kind of cartesian (cross) product and will give wrong results in the SUM() calculations. To avoid that, you need to do the summations for the two tables in two different subqueries to avoid errors.

Something like this will work:

SELECT 
    m.month, 
    p.name, 
    COALESCE(r.receipts_quantity, 0) AS receipts_quantity, 
    COALESCE(s.sales_quantity, 0)    AS sales_quantity
FROM 
    product AS p
  CROSS JOIN
    ( VALUES (1),(2),(3),(4),(5),(6) ) AS m(month)     -- months wanted in the report
  LEFT OUTER JOIN 
    ( SELECT month, product_id, SUM(quantity) AS receipts_quantity
      FROM receipts
      GROUP BY month, product_id
    ) AS r
    ON  p.id_product = r.product_id
    AND m.month = r.month
  LEFT OUTER JOIN  
    ( SELECT month, product_id, SUM(quantity) AS sales_quantity
      FROM sales
      GROUP BY month, product_id
    ) AS s
    ON  p.id_product = s.product_id
    AND m.month = s.month ;

If you want to avoid having results for products that did not have any activity in a month, you can add the condition:

WHERE r.month IS NOT NULL 
   OR s.month IS NOT NULL

Best Answer

Related Solutions

Sql-server – Select the newest data (inner join + group by maybe)

SQL INNER JOIN and OUTER JOIN in Same Table – How to Implement

Related Question