0

Say I have the following table giving a relation between oldID and newID, say when the ID of an item is changed.

+-------+-------+ 
| oldID | newID |
|-------|-------|   
|  1    |   2   |
|  2    |   3   |
|  3    |   4   |
|  4    |   5   |
|  7    |   9   |
+-------+-------+

There are three types of queries I need to make:

  1. Find all "chains of linked IDs", starting from oldest to latest.
  2. Given some ID, find the chain of IDs in its future.
  3. Given some ID, find the chain of IDs in its past.

So the first one should ive:

(1, 2), (2, 3), (3, 4), (4, 5)
(7, 9)

In the second example, suppose I give input 3, the output should be:

(3, 4), (4, 5)

The third example should output the following for the same input:

(1, 2), (2, 3)

What is the most efficient way to write the three SQL queries (in a portable manner)?

EDIT:

The results I need are as follows:

Query 1

chainID | chainIndex | ID
 -------+------------+----
   1          1         1
   1          2         2
   1          3         3
   1          4         4
   1          5         5
   2          1         7
   2          2         9

Query 2: Input 3

 itemID
 ______
   3
   4
   5

Query 3: Input 3

 itemID
 ______
   3
   2
   1

I am more interested in 2 and 3 as I can keep a separate table with the IDs of the head (first element) of each chain and implement 1 via 2. It is guaranteed that each ID has at most one child and parent, and I know the head of each chain.

Jus12
  • 17,824
  • 28
  • 99
  • 157
  • 3
    For general solution you need recursion, **SQL Server** has recursive CTE, **MySQL** can mimic it with variables, **H2** I don't know. So I doubt you will find one query to rule them all. – Lukasz Szozda Oct 04 '15 at 10:19
  • I removed all the excess databases. Feel free to tag with the one you are really using. – Gordon Linoff Oct 04 '15 at 12:27
  • @lad2025: pretty much every modern DBMS except MySQL has recursive CTEs nowadays. Not only SQL Server. –  Oct 04 '15 at 12:32
  • @a_horse_with_no_name I agree but they may differenitate in syntax like Oracle `CONNECT BY`. My point was that there is solution but not always one query can be runned on multiple RDBMS. – Lukasz Szozda Oct 04 '15 at 12:34
  • 2
    The subject here is "hierarchical SQL". See [What are the Options for Storing Hierarchical Data in a Relational Database?](http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database) for a detailed introduction to the subject. – Kristoffer Bohmann Oct 04 '15 at 12:35
  • What kind of structure is your results? Please, post in tabular or more clear way. – dani herrera Oct 04 '15 at 16:48
  • @danihp edited the question with result details. – Jus12 Oct 04 '15 at 18:31
  • Recursion CTE is needed. Not all db brands support it. You should to tag a db engine for a particular solution. – dani herrera Oct 04 '15 at 18:33
  • If I can set an upper bound on chain height (say 100), can I just make successive SQL queries to do this? Or is it considered a bad idea? – Jus12 Oct 04 '15 at 18:39

0 Answers0