'Find corresponding id values for records from different table

I have 2 tables with entries as:

Transition table:

From_Status_Id     To_Status_Id      PU_Id
    5                   1             868
    5                   2             868
    5                   3             868
    5                   4             868
    8                   5             868
    8                   2             868
    8                   3             868

Status table:

ProdStatus_Id      ProdStatus_Desc
        1                NoId-Staged
        2                NoId-Running
        3                Staged
        4                Running
        5                Complete
        8                Inprogress

I need the result in below format which would be basically the corresponding status desc value for a given status id.

Output:

from_status  to_status
Complete    NoId-Staged
Complete    NoId-Running
Complete    Staged
Complete    Running
Inprogress  Complete
Inprogress  NoId-Running
Inprogress  Staged

How can we achieve this result set using joins?

sqlsql-server


Solution 1:[1]

 SELECT S.ProdStatus_Des AS FROM_STATUS,S2.ProdStatus_Des AS TO_STATUS
 FROM Transition AS T
 JOIN Status  AS S ON T.From_Status_Id=S.ProdStatus_Id 
 JOIN Status  AS S2 ON T.To_Status_Id=S2.ProdStatus_Id

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Sergey

Related Questions