CODEWITHSUNDEEP
sqlt-sql
James Wilson
James Wilson

Reputation: 5150

Selecting information from the same table twice

I have a single table I need to pull information from.

SELECT [workID], [name], [status], [nextStep] FROM [JM_AccountWorkFlowDetail]

[nextStep] points to the next step in the chain which if available will match something in workID.

The above query will return:

workID  name                        status  nextStep
7       Name                        Status     0
9       Garnishment to Court        WWW        7

As you can see for workID 9 it points to 7 which means workID 7 is next on the list.

Is it possible to show the name for nextStep rather than the number?

A query that would return the following:

workID  name                        status  nextStep
7       Name                        Status     0
9       Garnishment to Court        WWW        Name

Upvotes: 1

Views: 105

Answers (3)

Alex Gordon
Alex Gordon

Reputation: 60711

   ; with
    cte1 as
    (
        SELECT 

        JM.[workID]
        ,JM.[name]
        ,JM.[status]
        ,(select top 1 name from JM_AccountWorkFlowDetail where nextStep = JM.nextStep) nextStep
        FROM [JM_AccountWorkFlowDetail] JM
    )

    select workid, name, status, case when nextstep=0 then 'no next step' else nextstep end
    from cte1

Upvotes: 1

Alex Gordon
Alex Gordon

Reputation: 60711

SELECT a.[workID], a.[name], a.[status], b.name as nextstep FROM [JM_AccountWorkFlowDetail] a
right outer join [JM_AccountWorkFlowDetail] b
on b.nextstep=a.nextstep

Upvotes: 0

John Woo
John Woo

Reputation: 263703

join it with the table itself so you can get the name of the nextStep via linking it with workid.

SELECT  a.workID,
        a.name,
        a.status,
        COALESCE(b.name, CAST(a.nextStep AS VARCHAR(5))) nextStepName
FROM    JM_AccountWorkFlowDetail a
        LEFT JOIN JM_AccountWorkFlowDetail b
            ON a.nextStep = b.workID

Upvotes: 4

Related Questions