how to construct this query?

I have a table that holds info about videos on a website. It has fields for id (primary 开发者_C百科key),title, series (int, foreign key constraint), and episode number. There is another table called "series" that indexes video series. So for example, series "American Dad" would have a series id of 1, and the id column in the series table is the same as the series column in the videos table.

I am trying to create a query that, when given a video id, pulls the title and video id of the previous and next videos in the series, e.g. if I input an id for episode 5 for American Dad, I would also get back the info for episodes 4 and 6.

I know how to write the query to find the series id when given a video id, and I know how to write the query to find all the episodes of a series when given a series id. I am having trouble putting it all together in one query, and also limiting it to the results immediately preceding and following (based on episode number) of a given video.

Hopefully that wasn't too convoluted. Any help is appreciated!

If the episode numbers are always sequential, you can just do something like

SELECT ... WHERE (episode = :episode - 1 OR episode = :episode + 1) AND series = :series