CODEWITHSUNDEEP
mysql
jzbs
jzbs

Reputation: 11

MYSQL: Select items that do not contain a certain date

Say I have a table

CustID   |   OrderDate
1        |   2017-05-30 05:15:18
2        |   2017-04-18 05:15:18
2        |   2017-04-15 05:15:18
3        |   2017-02-17 05:15:18
4        |   2017-05-29 05:15:18
4        |   2017-03-24 05:15:18

Any I only want to return back the CustIDs that do not contain an order date newer than 30 days (Today being 2017-05-30). So the above example would only return 2 and 3.

I have:

SELECT DISTINCT CustID
FROM TABLE
WHERE NOT EXISTS (SELECT CustID FROM TABLE WHERE OrderDate > DATE_ADD(NOW(),INTERVAL-30DAY));

But I only get syntax errors.

Thanks again, I am quite new with SQL.

Upvotes: 1

Views: 407

Answers (3)

cn0047
cn0047

Reputation: 17091

You can try this:

select distinct CustIDs from YourTableName where OrderDate < now() - interval 30 day;

PS: In your query, you're using FROM TABLE - it isn't right, you must use FROM {YourTableName}, where {YourTableName} is real name of your table in database, like (customers, clients, etc.)

Upvotes: 1

Asav Vora
Asav Vora

Reputation: 61

as far as i understand your question.

your query will be:

SELECT DISTINCT CustID FROM TABLE WHERE OrderDate < GETDATE()-30

Let me know if you require changes like < or > OrderDate in Where Clause.

Upvotes: 0

Yusuf Hassan
Yusuf Hassan

Reputation: 2013

because you're missing a vital component in where clause: and t1.id = t2.id

select distinct CustID from table t1 where not exists (select 1 from table t2 where [whatever_your_conditions_are] and t1.id = t2.id);

A table similar to yours... It just has date instead of timestamp (and a relaxed date condition in where clause, just for the sake of simplicity):

enter image description here

selecting the users whose date > current_date (which is order_date in your case):

enter image description here

Upvotes: 0

Related Questions