MariaDB – Create query that matches value to a range in another table

For example lets say we were wanting to allocate transactions to a financial year but allow the users to adjust that financial year depending on their circumstances. How could we do that.

Here we have a table called
t0165financeyear
whose structure is

pkid
taxyear
startdate
enddate

And a table of transactions called t023finance

CREATE VIEW v0004financeyear
AS SELECT a.pkid pkidt0023,
a.banked,
a.transactiondate,
a.description,
a.category,
a.Direction,
a.hmrcint,
a.pkidt0001,
a.pkidt0007,
a.vamount,
a.invoiceno,
a.pkidt0011,
a.dupdated,
a.dcreated,
b.pkid pkidt00165,
b.taxyear from t0023finance a, t00165taxyears b
where
a.banked between b.startdate and b.enddate

Remarkably simple – I suspect that we might get cartesian join issues if your start date and end dates in the t00165 table overlap.