MySQL: get all values of one column for which there is no row matching a condition on a second column

Let's say I have a MySQL join that gives me a bunch of rows with the following columns: PERSON_ID, ENTRY_ID, DATE

Each person can have multiple entries, but an entry can't belong to multiple people. What I want to retrieve is a list of all people who have not posted an entry in the last month - (all PERSON_IDs for which there is no row with a DATE within 30 days of NOW()). Can I get that result in a single query?

My current join is basically:

SELECT P.ID,P.NAME,P.EMAIL,E.ID,E.DATE FROM PERSON P, ENTRY E WHERE P.ID = E.PERSON_ID.

That gives the list of all entries, joined to additional informat开发者_Python百科ion about the person who posted each one. The entries table is a big one, so scalability is somewhat important.

You probably want some kind of left null join... however, I'm slightly puzzled. Is there another table with just a single row for each possible person, or do you mean you want a list of everyone with at least 1 row in your example table, but no entry if the last month?

Assuming the latter (although tbh the former seems more likely):

select distinct(t1.person_id) from mytable as t1
left join mytable as t2 on t1.person_id = t2.person_id and
t2.`date` > date_sub(now(), interval 30 day)
where t2.person_id is null
;

This is basically saying "give me a list of distinct person ids where you can't join to that person id in the last 30 days"

(untested btw)

... as another comment, you might be tempted to do something like:

select distinct(t1.person_id) from mytable as t1 where t1.person_id not in
  (select person_id from mytable where `date` > date_sub(now(), interval 30 day)
);

That's fine as long as your dataset is small and will stay small, but will scale very badly on a large number of rows...

I would recommend outer-joining the entries table to the persons table, and group by the PERSON_ID.

This will cause all the values in the date column to be NULL whenever the are no matching rows, but will still display the PERSON_ID.

After that all is left is to check if the date column is NULL.

• if you include your table structure i will be able to add a MySQL statement which can explain a lot better

Assuming your query goes something like

Select tablea.person_id, entry_id, date from tablea 
left join 
(select * from tableb tableb.date between subdate(now(),30) and adddate(now(),30) as temptable on tablea.person_id=temptable.person_id where temptable.date is null