2

I have a history mapping table for UserId changes, where every time when UserId changes, a row for new UserId with old UserId inserted in the history table. Below is the sample table and data:

UserIdNew | UserIdOld
---------------------
    5     |     1    
    10    |     5    
    15    |     10   

The above data explains that UserId 1 has gone with following transition from UserId 1 -> 5-> 10 -> 15.

I want to query all the Old Ids for a give UserIdNew, how can I do it in a single query?

For this case if UserIdNew = 15, then it should return 1,5,10

3
  • If Your MySQL version is 8.0, You may build recursive CTE (dev.mysql.com/doc/refman/8.0/en/…), obtaining data You need. If not, stored procedure seems to be the only solution.
    – Akina
    May 11, 2018 at 10:29
  • You mean that you want a single row with all the members of the same "family" on that row starting with the oldest ancestor?
    – Vérace
    May 11, 2018 at 11:26
  • @Vérace Yes, fine even if in separate rows. May 11, 2018 at 11:44

1 Answer 1

1

System Version Tables in MariaDB-10.3 offers a range of features associated with versioning. This provides easy history viewing however its hard to trace changes.

Any table structure that preserves a unique unmodified key as the starting ID will make all required queries easier rather than resorting to CTE, procedures or other required recursive implementations.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.