Joining data from two result rows on a numerical range

Question

I am trying to create a custom interface for a system that tracks tickets.

I have got tickets in a table of the form:

+----------------------+
| Section | Row | Seat |
+----------------------+
|   15    |  A  |  100 |
|   15    |  A  |  102 |
|   15    |  A  |  103 |
|   15    |  A  |  110 |
|   15    |  A  |  111 |
|   15    |  B  |  102 |
|   15    |  B  |  103 |
|   15    |  B  |  104 |
|   15    |  C  |   99 |
|   15    |  C  |  100 |
|   15    |  C  |  101 |
|   15    |  C  |  102 |
|   15    |  C  |  103 |
|   15    |  C  |  104 |
+----------------------+

I am trying to display the ticket 'blocks' where seats behind each other are marked as such. i.e. I'd like to be able to display:

+------------------------------------------------+
| Section | Row | Seat Range | Overlaps Previous |
+------------------------------------------------+
|   15    |  A  |  100 - 103 |         No        |
|   15    |  B  |  102 - 104 |        Yes        |
|   15    |  C  |   99 - 104 |        Yes        |
|   15    |  A  |  110 - 111 |         No        |
+------------------------------------------------+

Any thoughts?

Answer 1

You could have an additional relation that assignes all neighbouring seats to a given one. This will then also work better than any soly numerical scheme for any sort of separation of your seats. And you could allow for a neighbourhood across rows. From there you could then iteratively define any block of free seats.

If this is about supporting a cashier, I tend to think I would not solely address that in the database but seek for an integration with the GUI to identify the blocks via some backtracking upon a click on a first free seat.