Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SQL Select in Table with two IDs

SQL Select in Table with two IDs

SQL Select in Table with two IDs

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?

RE: SQL Select in Table with two IDs

the answer is: recursively

and there's no way to do that in ANSI SQL

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

RE: SQL Select in Table with two IDs

Perhaps a data sample and an expected result sample would be helpful.


RE: SQL Select in Table with two IDs

In Ruby, you'd do something like that - but when it comes to handling the SQL, it's pseudocode, since I don't know anything about the way you're set up.


table = (select id, previous_id from mytable)
# table = [ [1, nil], [2, 1], [3, nil], [4, nil], [5, 2], ...]
id_and_original_id = []
# That array will hold our results

table.each do |id, prev_id|
  # start recursion if there's a previous_id
  original_id = prev_id ? get_original_id prev_id : nil
  # Store the result
  id_and_original_id << [id, original_id]

def get_original_id oldid
# Find the row where the oldid is the original id
  previous_set = (select id, previous_id from mytable where id = oldid)
  return previous_set[0] if previous_set[1].nil?
  get_original_id previous_set[1]

puts id_and_original_id

Completely untested. Wrap your mind around it before running this, just in case it triggers the Singularity.

Tao Te Ching Discussions : Chapter 9 (includes links to previous chapters)
What is the nature of conflict?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close