mysql: finding gaps in a sequence (no autoincrement!)

[xblue]

Hi,

I'm playing around with the nested set model once again. To check data integrity, I would like to idendify gaps in a sequence.

As said in the subject, I do not want to re-index an autoincrement column, though the task is somewhat similar. To make it more complicated, the numbers that are supposed to be sequential span over two columns.

Valid data would look like this

name | l | r
-----+---+---
ROOT | 1 | 10
A    | 2 | 7
A1   | 3 | 4
A2   | 5 | 6
B    | 8 | 9

where each number between 1 and # of rows*2 must be either in column l or in column r.

What I currently do is gathering all l and r values in an array, and then comparing this to another array that contains a range of numbers with array_diff to identify the gaps.

However I still wonder if there may be better ways to do this?

[nemonoman]

I can think of a number of variations on your approach, but they are all fundamentally the same as your approach, not something radically different. One always prefers to use the scalpel to the sledgehammer, but there are times when the sledgehammer is required. Pound away!