Select all records where ONLY 1 condition is met in MySQL

[Prince_Mike]

Hello All,

Good evening!

Please has anyone had this issue before. If yes, please kindly assist me. 

I am trying to filter out ONLY all members that have not registered for the current year. All members in the register table have been registered in the previous years; 2015, 2016 and 2017 and there registration details are stored in the validated table. Now some of them have registered in 2018.

The issue here is that after registering a member for the current year which is 2018, I want the system to filter out and list ONLY those that have not been registered for the current year so it will be easy for the registrars to register only those that has not been registered for the current year. So I wrote the SQL statement below but its not given me the correct listing.

SELECT r.id, r.title, r.surname, r.othernames, r.age, r.state, r.country, r.assembly, r.sex, r.maritalstatus, r.phone, r.email, v.eyear,  @curRow := @curRow + 1 AS sn

FROM register r

INNER JOIN validated v ON register.regno = validated.regno JOIN (SELECT @curRow := 0) r

WHERE validated.eyear <= 2017 AND validated.eyear <> 2018

GROUP BY register.regno

The SQL statement works by not showing all records with the year 2018 but because all members were registered in the previous years, their names are still coming out in the list under the previous registration.

For example:  Assuming there is a member by name Michael Collins. Michael Collins was registered in 2015, 2016, 2017 and now he is currently registered in the current year 2018. This means Michael Collins will have 4 records of registration in the validated table. This is OK. Now when I run the SQL query, the system hides ONLY the record of Michael Collins that has the current year which is 2018 instead of hiding all his entere records because we only want to see those that have not registered in the current year. Once a member has been registered for the current year, all his records should be hidden but not deleted.

This is what I want to achieve but the query is not helping out.

PLEASE I NEED HELP. THANK YOU IN ADVANCE