self join to select consecutive numbers
- by shantanuo
CREATE TABLE `mybug` (
`SEAT_NO` decimal(2,0) NOT NULL,
`ROW_NO` decimal(2,0) NOT NULL,
`COL_NO` decimal(2,0) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `mybug` VALUES ('1','1','1'),('26','7','2'),('31','8','2'),('32','8','1'),('33','9','1'),('34','9','2'),('35','9','5'),('36','9','6'),('37','10','6'),('38','10','5'),('39','10','2'),('40','10','1'),('41','11','1'),('42','11','2'),('43','11','4'),('44','11','5');
+---------+--------+--------+
| SEAT_NO | ROW_NO | COL_NO |
+---------+--------+--------+
| 1 | 1 | 1 |
| 26 | 7 | 2 |
| 31 | 8 | 2 |
| 32 | 8 | 1 |
| 33 | 9 | 1 |
| 34 | 9 | 2 |
| 35 | 9 | 5 |
| 36 | 9 | 6 |
| 37 | 10 | 6 |
| 38 | 10 | 5 |
| 39 | 10 | 2 |
| 40 | 10 | 1 |
| 41 | 11 | 1 |
| 42 | 11 | 2 |
| 43 | 11 | 4 |
| 44 | 11 | 5 |
+---------+--------+--------+
16 rows in set (0.00 sec)
In the above chart, I need to select 2 seats those are in the same row AND column numbers are next to each other.
For e.g. Seat Numbers 38 & 39 can not be issued even if both the seats are from the same row because the column numbers 2 & 5 are not adjacent.
The expected results are as follows:
31
33
35
37
39
41
43
These are the starting numbers and the next seat will be automatically booked as well. for e.g. 31 & 32
