Reputation: 657
I want to make an SQL request to display a list of users but only the one who haven't accepted a mission since at least 60 days. A user have multiple user missions attached to him, so I need to look at all of them and display the user only if no missions have been accepted since 60 days.
Here is what I have so far, but it is wrong, the user is in the list even if he have accepted a mission less than 60 days ago, but the mission doesn't show up though. So this request just display every missions that have been accepted more than 60 days ago. That is not what I want.
SELECT
u.username, u.id, u.email, date_part('days', now() - um.date_accepted) as "days since last mission"
FROM
users_user u
INNER JOIN
users_usermission um
ON
u.id=um.user_id
WHERE
date_part('days', now() - um.date_accepted) > 60
Upvotes: 2
Views: 1301
Reputation: 260
This should give you some idea...
select workername, workerdoj, now()::date - workerdoj::date as days
from workers
where now()::date - workerdoj::date > 60
It returns all the exployees whose number of days from DOJ is > 60, you can modify it according to your scenario. Hope this helps
Upvotes: 0
Reputation: 44931
I think a query using a correlated not exists
predicate would work:
SELECT
*
FROM
users_user u
WHERE NOT EXISTS (
SELECT 1 FROM users_usermission um
WHERE u.id = um.user_id
AND um.date_accepted > CURRENT_DATE - interval '60 days'
);
This would only return users for which there doesn't exists any missions within the last 60 days.
See this sample SQL Fiddle for an example.
Upvotes: 1
Reputation: 1271241
You can do what you want by using aggregation and a left join. I would do it like this:
SELECT u.username, u.id, u.email,
date_part('days', now() - um.date_accepted) as "days since last mission"
FROM users_user u LEFT JOIN
(SELECT um.user_id, MAX(date_accepted) as maxda
FROM users_usermission um
GROUP BY um.user_id
) um
ON u.id = um.user_id
WHERE maxda <= CURRENT_DATE - interval '60 days' or
maxda IS NULL;
I'm not sure what the complicated calculation is in the SELECT
clause, but I'm leaving it in. It doesn't appear to be part of your question.
Upvotes: 1