Joining two different tables with a common third table on a common column

Here are the tables

Table: Status

ID, StatusDesc
1,  New
2,  Active
3,  Cancelled
4,  Complete

Table: Order (foreign key relationship with Status table above)

ID, OrderNumber, StatusID
1,  1001 , 1
2,  1002,  1
3,  1003, 2
4,  1004, 3
5,  1500, 4

Table: LineItem(foreign key relationship with Order and Status tables above)

ID, OrderNumber, LineItemNumber, StatusID
1,  1001 , 1, 1
2,  1001 , 2, 1
3,  1002 , 1, 2
4,  1002 , 2, 1
5,  1003 , 1, 2
6,  1004 , 1, 3
7,  1004 , 2, 4
8,  1500 , 1, 3

As you can see, the table Status holds the statuses common for both Order and LineItem tables.

I want to produce the result which will include columns like this, status description for both Order and LineItem:

OrderNumber, LineItemNumber, StatusDesc_Order, StatusDesc_LineItem

How to do this?

Answer

You could join the Status table twice to achieve this:

SELECT
           o.OrderNumber
         , li.LineItemNumber
         , orderStatus.StatusDesc    AS StatusDesc_Order
         , lineItemStatus.StatusDesc AS StatusDesc_LineItem
FROM       [LineItem] AS li
INNER JOIN [Status]   AS lineItemStatus ON li.StatusID = lineItemStatus.ID
INNER JOIN [Order]  AS o ON li.OrderNumber = o.OrderNumber
INNER JOIN [Status]   AS orderStatus ON o.StatusID = orderStatus.ID

I do suggest however you try and stay away from table names using reserved keywords like Order and Status, it also is good practice to explcitly add schema prefixes before the table names in the query (i.e. dbo.Status or another user defined schema).