Select rows that are not present in the table based on criteria

All we need is an easy explanation of the problem, so here it is.

I have two tables student table and fee table:

First Table (Student):

std_id std_name
1 A
2 B
3 C
4 D
5 E
6 F

Second Table(Fee):

fid std_id amount Month
1 1 500 2022-07
2 2 500 2022-07
3 4 500 2022-07
4 1 500 2022-08
5 2 500 2022-08

I want to fetch rows of those students from fee table who are not paid amount based on month. For Example want to fetch rows of those students from second table who did not pay fee in 2022-07

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Suggestion. Use proper date data type to store dates , 2022-07 is not a valid date data type, you will save a lot of pain for the future.

As per the question, one way using exists:

select s.std_name
from Student s 
where not  exists (select 1 from Fee f where s.std_id=f.std_id and f.Month='2022-07');

Another way using left join:

select s.std_name
from Student s 
left join Fee f on s.std_id=f.std_id and f.Month='2022-07' 
where f.Month is null ;

You will benefit from the following indexes

key std_month(`std_id`,`Month`)   --- > on Fee     table 
key `std_id`(`std_id`)            --- > on Student table 

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ecd96397f42659ba45e8e848e42cd90c

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply