I have a database with the following records:
| id | sequence | destination |
|---|---|---|
| 2 | 1 | Johannesburg |
| 2 | 2 | Durban |
| 2 | 3 | Cape Town |
| 3 | 1 | Queenstown |
| 3 | 2 | East London |
| 3 | 3 | Gqeberha |
When I search for a city in the destination column for example Queenstown, I would like to have the following results:
| id | sequence | destination |
|---|---|---|
| 3 | 2 | East London |
| 3 | 3 | Gqeberha |
I want the sequence to be greater than that of the current record found and the id to be the same.
>Solution :
Use a self-join:
SELECT t1.*
FROM yourTable AS t1
JOIN yourTable AS t2 ON t1.id = t2.id AND t1.sequence > t2.sequence
WHERE t2.destination = 'Queenstown'
ORDER BY t1.sequence
t1.id = t2.id makes the id the same, and t1.sequence > t2.sequence makes the sequence higher than the current record.