SET @current_episode = 1; SET @current_season = 2; SET @current_serial = 2; SELECT * FROM `episode` _episode LEFT JOIN season _season ON _episode.season_id = _season.id LEFT JOIN serial _serial ON _season.serial_id = _serial.id WHERE (_episode.episode < @current_episode AND _season.season = @current_season) OR (_episode.episode > @current_episode AND _season.season = (@current_season - 1)) ORDER BY _season.season DESC, _episode.episode ASC LIMIT 0,1

Episode table:

 +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | season_id | int(11) | YES | MUL | NULL | | | poster_id | int(11) | YES | MUL | NULL | | | episode | int(11) | NO | | NULL | | +-------------+--------------+------+-----+---------+----------------+

Season table:

 +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | serial_id | int(11) | YES | MUL | NULL | | | season | int(11) | NO | | NULL | |

Table Serial :

 +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment |
  • And what is the sql dialect? ms-sql , my-sql , oracle , etc.? - Vadim Ovchinnikov
  • @VadimOvchinnikov MySQL - Rohan Warwar
  • Or maybe in order by episode DESC, because we need the maximum episode less than this - Mike
  • But we have 2 seasons: In season 1 there are 12 episodes, in the second - 8 episodes. It turns out if we are looking for the previous series from season 1 series 2 - it should be equal to series 12 of season 1. - Rohan Warwar
  • Those. the previous series must be equal to the last series of the previous season. - Rohan Warwar

2 answers 2

You can view the answer on the English version of the site: https://stackoverflow.com/questions/42144441/mysql-find-previous-episode-in-serial/42144753#42144753

For the lazy:

 SET @current_episode = 1; SET @current_season = 2; SET @current_serial = 2; SELECT * FROM `episode` _episode LEFT JOIN season _season ON _episode.season_id = _season.id LEFT JOIN serial _serial ON _season.serial_id = _serial.id WHERE (_episode.episode < @current_episode AND _season.season = @current_season) OR (_season.season < @current_season) ORDER BY _season.season DESC, _episode.episode DESC LIMIT 0,1 
     WHERE CONCAT(serial,season,episode) < CONCAT(@serial,@season,@episode) ORDER BY CONCAT(serial,season,episode) DESC LIMIT 1

    If the values ​​can be multi-valued, add LPAD() .