Web Development

Ask something
Question Locked

SQL Select in Table with 2 IDs

0 helpful
2 replies

by a former member

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Can those who have the know-how help me with this problem?

So it's a table with Id and Previous_Id (don't know why, it's just the way the developers have made it..)
Now, I need a Select that starts from Id, checks for possible Previous_Id and jumps to that Id which has no Previous_Id (Null).
I mean, if Id has Previous_Id (i.e. it's not Null), then the Previous_Id is read as the main Id and then again you look to find out if there's any Previous_Id facing it - if yes, you do the same until you get to Null. It's that last Id which I need along with the first Id where it all started.
But how can this be done with Select?

2 replies

by Former member

Clarifications

by tony hopkinson

Create a user defined Function Called
say GetPreviousID
takes MainID as an argument, returns the previousID

Then

Declare @MainID (somedatatype)
Declare @PreviousID (somedatatype)
Select @MainID = ?? // one to start at
While @MainID is not null
Begin
Select @PreviousID = @MainID
Select @MainID = GetPreviousID(@MainID)
If @MainID = @PreviousID
Begin
Select @MainID = null // avoid infinite loop
End
End
Select @PreviousID as EarliestMainID

or something similar.

HtHs

There are other ways to do it using Cursors or Temporary tables, but basically SQL and recursion aren't a good idea.

This discussion is closed to new replies.