self join to select consecutive numbers

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 |     开发者_JAVA技巧 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

---

SELECT m1.seat_no
FROM   mybug m1
       JOIN mybug m2
         ON m1.row_no = m2.row_no
WHERE  m1.seat_no < m2.seat_no
       AND Abs(m1.col_no - m2.col_no) = 1; 

---

This should do what you need:

SELECT a.seat_no 
FROM mybug as a, mybug as b 
WHERE a.row_no = b.row_no 
    AND b.col_no = a.col_no +1

---

select m1.SEAT_NO, m2.SEAT_NO
from mybug m1, mybug m2
where m1.ROW_NO=m2.ROW_NO
AND ABS(m1.COL_NO-m2.COL_NO)=1