Date-range queries using two related tables

This example links the student and student_fee tables and then applies date-range conditions. It also shows totals for a month and payments made by one student.

Sample tables

Records between two dates

SELECT * FROM student_fee
WHERE dt BETWEEN '2013-01-09' AND '2013-01-30';

The result contains the two rows dated 2013-01-10 and 2013-01-24.

Join the fee and student tables

SELECT sf.*, s.*
FROM student_fee AS sf
INNER JOIN student AS s ON sf.id = s.id
WHERE sf.dt BETWEEN '2013-01-09' AND '2013-01-30';

Using explicit INNER JOIN makes the table relationship clearer than the older comma-join form.

Total fee collected in February 2013

SELECT SUM(amount) FROM student_fee
WHERE MONTH(dt) = 2 AND YEAR(dt) = 2013;

Output:

596

The same task can be written with DATE_FORMAT(). For large indexed tables, a direct date range is usually easier for the optimizer than applying a function to every date value.

SELECT SUM(amount) FROM student_fee
WHERE DATE_FORMAT(dt, '%b-%Y') = 'Feb-2013';

Or with the full month name:

SELECT SUM(amount) FROM student_fee
WHERE DATE_FORMAT(dt, '%M-%Y') = 'February-2013';

Payments made by John Deo in 2013

SELECT sf.*, s.*
FROM student_fee AS sf
INNER JOIN student AS s ON sf.id = s.id
WHERE sf.dt BETWEEN '2013-01-01' AND '2013-12-31'
  AND s.name = 'John Deo';

The sample data returns the two January payments, 200 and 100.

Download SQL dump of the student table
Download SQL dump of the student_fee table

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: