MYSQL get previous record of selected record

Question

i have two tables table1: PERIODS and table2:PROBATIONARY, i want to know if the STUDENT exists in PROBATIONARY table, i want to check if the student is on probationary in the previous SEMESTER prior to the active SEMESTER. So if the active SEMESTER is (20182) i want to check if the student is probationary in (20181) semester, everytime i change the active period it should query and check the previous record. PERIODS are arranged by YEAR and TERM.

something like this, i really dont have deep background of MYSQL for now so any help will.

SELECT
period.code,
period.name,
period.`year`,
period.term,
probationary.student,
probationary.onprob
FROM
probationary
Inner Join period ON probationary.period = period.id
WHERE
period.id = (PREVIOUS PERIOD OF CURRENT PERIOD)


-- PERIOD TABLE

  CREATE TABLE IF NOT EXISTS `period` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(12) NOT NULL DEFAULT '',
  `name` varchar(40) NOT NULL DEFAULT '',
  `year` smallint(5) unsigned NOT NULL DEFAULT '0',
  `term` char(1) NOT NULL DEFAULT '',
  `nstart` date NOT NULL DEFAULT '0000-00-00',
  `nend` date NOT NULL DEFAULT '0000-00-00',
  `ext` date NOT NULL DEFAULT '0000-00-00',
  `enrstart` date NOT NULL DEFAULT '0000-00-00',
  `enrend` date NOT NULL DEFAULT '0000-00-00',
  `enrext` date NOT NULL DEFAULT '0000-00-00',
  `addstart` date NOT NULL DEFAULT '0000-00-00',
  `addend` date NOT NULL DEFAULT '0000-00-00',
  `addext` date NOT NULL DEFAULT '0000-00-00',
  `orvalidate` date NOT NULL DEFAULT '0000-00-00',
  `idmask` varchar(12) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `code` (`code`),
  KEY `name` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;  

-- PERIOD VALUES

-- Dumping data for table `period`
--

INSERT INTO `period` (`id`, `code`, `name`, `year`, `term`, `nstart`, `nend`, `ext`, `enrstart`, `enrend`, `enrext`, `addstart`, `addend`, `addext`, `orvalidate`, `idmask`) VALUES
(1, '20181', 'First Semester, 2018-2019', 2018, '1', '2018-08-13', '2018-12-13', '2019-05-01', '2018-07-13', '2018-09-13', '2019-03-29', '2018-08-13', '2018-09-13', '2019-03-29', '2018-12-13', '181'),
(2, '20182', 'Second Semester, 2018-2019', 2018, '2', '2019-01-14', '2019-05-14', '2019-05-14', '2018-12-14', '2019-02-14', '2019-05-27', '2019-01-14', '2019-02-14', '2019-05-27', '2019-05-14', '182'),
(3, '20171', 'First Semester, 2017-2018', 2017, '1', '2017-08-14', '2017-12-14', '2017-12-14', '2017-07-14', '2017-09-14', '2017-09-14', '2017-08-14', '2017-09-14', '2017-09-14', '2017-12-14', '171'),
(4, '20172', 'Second Semester, 2017-2018', 2017, '2', '2017-01-09', '2017-05-09', '2017-05-09', '2016-12-09', '2017-02-09', '2017-02-09', '2017-01-09', '2017-02-09', '2017-02-09', '2017-05-09', '172'),
(5, '20173', 'Short Term 2017', 2017, '3', '2017-06-05', '2017-08-05', '2017-08-05', '2017-05-05', '2017-07-05', '2017-07-05', '2017-06-05', '2017-07-05', '2017-07-05', '2017-08-05', '173');

-- PROBATIONARY TABLE

-- Table structure for table `probationary`

CREATE TABLE IF NOT EXISTS `probationary` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student` int(11) NOT NULL,
  `period` int(11) NOT NULL,
  `totalunits` varchar(5) NOT NULL,
  `passedunits` varchar(5) NOT NULL,
  `onprob` int(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

-- PROBATIONARY VALUES

INSERT INTO `probationary` (`id`, `student`, `period`, `totalunits`, `passedunits`, `onprob`) VALUES
(1, 753, 1, '29', '12', 1),
(2, 753, 3, '29', '12', 1),
(3, 753, 4, '29', '12', 1),
(5, 754, 5, '29', '10', 1),
(6, 754, 4, '29', '10', 1);

I have the sample records above, if 20182 is current period, STUDENT 753 will be TRUE while STUDENT 754 will be FALSE

Answer 1

I think this query will give you the results you want. You put the "active" semester code into the WHERE clause in the subquery, and that will then return the previous semesters code, which is then used to select the students who were on probation e.g.

SELECT p.code,
       p.name,
       p.`year`,
       p.term,
       pr.student,
       pr.onprob
FROM probationary pr
INNER JOIN period p ON pr.period = p.id
WHERE p.code = (SELECT MAX(code)
              FROM period p2
              WHERE p2.code < 20173)

Output (for your sample data):

code    name                        year    term    student onprob
20172   Second Semester, 2017-2018  2017    2       753     1
20172   Second Semester, 2017-2018  2017    2       754     1

Demo on dbfiddle