CODEWITHSUNDEEP
sqldatabase-designsql-server-2012
MattMcGowan
MattMcGowan

Reputation: 117

Linking a chain of IDs (self referential table)

We have a League table, and now want the ability to roll leagues over (when a league finishes, start it again from the beginning). The proposed solution was to just add a RolloverLeagueID to the table:

My question here is when we inevitably come to reporting. How would you show the full history of a league?

SELECT
    *
FROM
    League AS L1
    LEFT OUTER JOIN League AS L2
        ON L1.Id = L2.Id
    LEFT OUTER JOIN League AS L3
    etc etc

That's obviously not practical. Is there a way to query this information recursively or something?

My inclination is to add another table like this:

With a composite key on it, where RolloverChainId persists along the chain. And then just return all leagues with that RolloverChainId in order of date, or Id, or something.

Is there a better way of linking a chain of IDs?

Upvotes: 0

Views: 133

Answers (2)

Jay
Jay

Reputation: 27512

This is a classic problem with SQL. Oracle has a feature to do it cleanly -- it's been a long time since I used Oracle and I don't remember the details, but it's there.

Failing SQL extensions, you can do this with a sproc or with code, you just can't do it with a single query. It's easy enough to write a loop that keeps calling the query again, plugging in the value from the previous run. You could do it with a sproc so it's not multiple trips to the database engine if performance is an issue.

Upvotes: 1

Rhys Jones
Rhys Jones

Reputation: 5518

SQL Server can do recursive queries, and there are various ways of modelling linked lists, but do you really need to? Could you take a different approach and have a league-definition table that holds the constant details (i.e. league name), then a league-instance table that holds the details for each instance of the league (i.e. start and end date).

Upvotes: 1

Related Questions