SQL Query Needed – to Get data for previous date always, where date column is split into multiple column

I want to get rows where date is equal to Previous day,

Table Schema as below,

-- create a table
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL,
  xYear INTEGER,
  xMonth INTEGER,
  xDay INTEGER,  
);

— Sample Table —

—- Expected result —- considering today is 13th Jan, so i want data for 12th Jan—-

not sure , how to achieve this.

>Solution :

It should be quite clear this is a bad DB structure and the date should be stored as date in one single column instead in future.

Creating the desired result can be achieved by building a date out of those three columns and then check this date is yesterday.

There are lots of functions which can do this job, here one way with CAST and CONCAT:

SELECT 
id, name, gender,  
xYear, xMonth, xDay
FROM students
WHERE 
  CAST(CONCAT(xYear,'-',xMonth,'-',xDay) AS DATE) 
    = CURDATE() - INTERVAL 1 DAY;

You can also try above without the CAST and check if this is executed faster:

SELECT 
id, name, gender,  
xYear, xMonth, xDay
FROM students
WHERE 
  CONCAT(xYear,'-',xMonth,'-',xDay) = CURDATE() - INTERVAL 1 DAY;

Try out here: db<>fiddle