CODEWITHSUNDEEP
mysqlsql
Asim Zaidi
Asim Zaidi

Reputation: 28284

is this a right query to get the info I need

SELECT

table1.id

From 
table1
Join 
table2
ON 
table1.id = table2.id 

WHERE 
table1.`date` BETWEEN CURDATE() - INTERVAL 1 MONTH AND CURDATE();

join table record set is 

id  customer    date
1   john        2011-01-01
2   john1       2011-02-02
3   john2       2011-03-03
4   john3       2011-04-04
5   john4       2011-05-05
6   john5       2011-06-06
7   john6       2011-07-07
8   john7       2011-08-08
9   john8       2011-09-09
10  john9       2011-10-10
11  john10      2011-10-11

I was wondering if this is the right where clause to find if the customer that has a date over 30 days due.

Upvotes: 1

Views: 68

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

WHERE 
table1.`date` BETWEEN CURDATE() - INTERVAL 1 MONTH AND CURDATE() ;

will give you all rows with date between today and one month ago. If that's what you want, keep the query.

If you want all rows with date older than one month ago, use this:

WHERE 
table1.`date` < CURDATE() - INTERVAL 1 MONTH ;

or this (not exactly the same results with previous):

WHERE 
table1.`date` < CURDATE() - INTERVAL 30 DAY ;

Upvotes: 2

dispake
dispake

Reputation: 3329

I don't see the importance of table2 in your SQL so perhaps this will do ... 

select id, customer, date
  from table1
 where datediff(curdate(), date) > 30

Upvotes: 4

Related Questions