CODEWITHSUNDEEP
phpmysql
fheo
fheo

Reputation: 163

SQL: Get unique data between two dates

Hi I am using a mysql table "student" which has 4 columns:

Id Name   Date          StudentId
1  John   2010-01-15      3
2  Matt   2010-01-10      5
3  Jane   2010-02-10      8
4  John   2010-02-11      3
5  Matt   2010-02-11      5
6  Jane   2010-02-11      8

I want to fetch only new entries in the table between 2010-02-10 and 2010-02-12. If a student had a previous entry in the table then the query should not return that value. So in the above case the query should only return both entries of Jane since John and Matt had an entry each previous to the date specified.

This is what I have but it is not working:

SELECT *  FROM student 
WHERE date(Date) 
between '2010-02-10' and '2010-02-12' 
and date(Date) 
not between '0000-00-00' and '2015-02-09';

Upvotes: 2

Views: 3578

Answers (1)

Honza Haering
Honza Haering

Reputation: 812

GROUP BY and HAVING is what you are looking for if you want single record per student:

SELECT * FROM student 
  GROUP BY Name 
  HAVING DATE(Date) BETWEEN '2010-02-10' AND '2010-02-12';

Or I would use subquery if you want all the records:

SELECT * FROM student 
  WHERE DATE(Date) BETWEEN '2010-02-10' AND '2010-02-12'
  AND Name NOT IN 
     (SELECT DISTINCT Name FROM student WHERE DATE(Date) < '2010-02-10');

How it works: the subquery selects all the names that have records prior to the date range, i.e. the ones you don't want in your result. It produces set like ('John', 'Matt'). The main query then selects all the records in the given date range where Name NOT IN ('John', 'Matt').

Upvotes: 2

Related Questions