CODEWITHSUNDEEP
mysql
mindSurf
mindSurf

Reputation: 55

Exclude row from MySQL older than today

There is a MySQL table:

+----+-------+------+------------+---------+
| id | name  | type |    date    | deleted |
+----+-------+------+------------+---------+
| 1  | nameA |  1   | 2016:10:15 |    1    |
+----+-------+------+------------+---------+
| 2  | nameB |  1   | 2016:10:20 |    0    |
+----+-------+------+------------+---------+
| 3  | nameC |  2   | 2016:09:26 |    0    |
+----+-------+------+------------+---------+
| 4  | nameD |  2   | 2016:09:30 |    0    |
+----+-------+------+------------+---------+
| 5  | nameE |  3   | 2016:09:26 |    0    |
+----+-------+------+------------+---------+

I want to SELECT everything except where deleted = 1 and except where type = 2 has a date older than today (2016:09:27). So I tried using NOT IN, but the way I do it excludes also type=3 which has a date older than today:

$currentDate = date("Y:m:d");
$sql = "SELECT * FROM table WHERE deleted != 1 AND date NOT IN       
   (SELECT date FROM table WHERE type = 2 AND date < '$currentDate') ORDER BY date";

Any help? Thanks!

Upvotes: 1

Views: 783

Answers (4)

Rohit Gaikwad
Rohit Gaikwad

Reputation: 3914

Table Structure: 

CREATE TABLE `example` (
`id` int(11) DEFAULT NULL,
`name` varchar(30) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`date` date DEFAULT NULL,
`deleted` int(11) DEFAULT NULL
)

/*Data for the table `example` */

insert  into `example`(`id`,`name`,`type`,`date`,`deleted`) values (1,'nameA',1,'2016-10-15',1),(2,'nameB',1,'2016-10-20',0),(3,'nameC',2,'2016-09-26',0),(4,'nameD',2,'2016-09-30',0),(5,'nameE',3,'2016-09-28',0),(6,'nameE',3,'2016-09-26',0);

Required Query:

SELECT * FROM EXAMPLE WHERE (deleted != 1 AND id NOT IN (SELECT id FROM EXAMPLE WHERE (TYPE = 2 AND  DATE < CURDATE()))) ORDER BY DATE

Upvotes: 2

Daniel W.
Daniel W.

Reputation: 32360

You have a fundamental mistake in your database structure. If you want to compare dates and times effectively, you need to use the appropriate datatype DATETIME. Don't use a string - MySQL can't tell how to order the results or what is lower or higher.

Change the type to DATETIME and form your query like:

WHERE datetime_column < DATE_SUB(CURDATE(),  INTERVAL 1 DAY)

If you don't want to use the time at all, use DATE instead of DATETIME.

Upvotes: 0

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

Use Str_to_date 

SELECT * FROM table 
WHERE deleted != 1 
AND date NOT IN       
(SELECT date FROM table 
WHERE type = 2 AND  str_to_date(date, '%Y:%m:%d' )  < curdate()) 
ORDER BY str_to_date(date", '%Y:%m:%d')

Upvotes: 0

ADyson
ADyson

Reputation: 62074

Try it like this:

$sql = "SELECT * FROM table WHERE deleted = 0 AND NOT (type = 2 AND date < DATE(NOW())) ORDER BY date";

I don't think you need to define currentDate in PHP either.

Upvotes: 0

Related Questions