Highlighted

Select all records where ONLY 1 condition is met in MySQL

Participant ,
Nov 04, 2018

Copy link to clipboard

Copied

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

TOPICS
Server side applications

Views

292

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Select all records where ONLY 1 condition is met in MySQL

Participant ,
Nov 04, 2018

Copy link to clipboard

Copied

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

TOPICS
Server side applications

Views

293

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Nov 04, 2018 0

Have something to add?

Join the conversation