Code:
CREATE TABLE `test` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`MemberID` int(11) DEFAULT NULL,
`Year` int(11) DEFAULT NULL,
`Ammount` decimal(11,0) DEFAULT NULL,
PRIMARY KEY (`ID`)
)
INSERT INTO `test` (`ID`, `MemberID`, `Year`, `Ammount`) VALUES
(1, 1, 2015, 500),
(2, 2, 2016, 1000),
(3, 1, 2016, 1000),
(4, 1, 2014, 1000),
(5, 2, 2017, 1000),
(6, 2, 2018, 1000),
(7, 2, 2019, 1000),
(8, 2, 2020, 1000),
(9, 1, 2020, 1000),
(10, 1, 2015, 500),
(11, 1, 2018, 1000),
(12, 1, 2017, 1000),
(13, 1, 2019, 1000),
(14, 3, 2016, 500),
(15, 3, 2016, 500),
(16, 3, 2017, 500),
(17, 3, 2017, 500),
(18, 3, 2018, 1000),
(19, 3, 2019, 1000),
(20, 3, 2020, 1000),
(21, 4, 2020, 1000),
(22, 5, 2019, 1000),
(23, 5, 2020, 1000);
CREATE TABLE `test` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`MemberID` int(11) DEFAULT NULL,
`Year` int(11) DEFAULT NULL,
`Ammount` decimal(11,0) DEFAULT NULL,
PRIMARY KEY (`ID`)
)
INSERT INTO `test` (`ID`, `MemberID`, `Year`, `Ammount`) VALUES
(1, 1, 2015, 500),
(2, 2, 2016, 1000),
(3, 1, 2016, 1000),
(4, 1, 2014, 1000),
(5, 2, 2017, 1000),
(6, 2, 2018, 1000),
(7, 2, 2019, 1000),
(8, 2, 2020, 1000),
(9, 1, 2020, 1000),
(10, 1, 2015, 500),
(11, 1, 2018, 1000),
(12, 1, 2017, 1000),
(13, 1, 2019, 1000),
(14, 3, 2016, 500),
(15, 3, 2016, 500),
(16, 3, 2017, 500),
(17, 3, 2017, 500),
(18, 3, 2018, 1000),
(19, 3, 2019, 1000),
(20, 3, 2020, 1000),
(21, 4, 2020, 1000),
(22, 5, 2019, 1000),
(23, 5, 2020, 1000);
Treba mi upit koji će da izdvoji samo one koji imaju iznos u 2020 godini ali i da nemaju iznose u prethodnim godinama.
Iz prilozene test date očekivani rezultat treba da bude samo record (21, 4, 2020, 1000)
Tnx i pozdrav.