CODEWITHSUNDEEP
sqlms-accessrecursion
user1688175
user1688175

Reputation:

SQL recursion query

I am stuck with an issue here. I have the following table

ID     TX_NAME     DT_DATE        ID_SUCCESSOR    
1      Task 1      Jan/01/2012    2
2      Task 2      Jan/02/2012    3
3      Task 3      Jan/01/2012

My objective is to create an SQL statement which brings me TX_NAME which its DT_DATE is later and its ID_SUCCESSOR.

For example, ID = 2 is the one I am looking for as it happens after its successor (ID = 3)

Is it possible in a single query?

Thanks

Upvotes: 0

Views: 141

Answers (2)

Flavia Obreja
Flavia Obreja

Reputation: 1237

select t.* from 
TestTable t 
inner join TestTable t2 on t2.id = t.ID_SUCCESSOR
where t.DT_DATE > t2.DT_DATE

Upvotes: 1

Ian Yates
Ian Yates

Reputation: 1354

Not tested, but ought to be ok :)

SELECT TX_NAME
from MyTable T1
where DT_DATE > (
    SELECT DT_DATE
    from MyTable T2
    where T1.ID_SUCCESSOR = T2.ID
    )

Upvotes: 0

Related Questions