CODEWITHSUNDEEP
sqlpostgresql
user399421
user399421

Reputation: 857

Only return results if all rows that match 1st where clause also match the 2nd

Tables

Actions
----------
action_id

Tasks
---------
task_id
action_id
status_id  (can be: 1=waiting, 2=processing, 3=failed)
dt_created

I am trying to write a query that will return all 'actions' that:

if have tasks created in the last 24 hours all have 'status' failed

My difficulty is making sure there is not a task created in the last 24 hours that did not fail.

Thus a simple query fails as it will just return the results that match both.

select ....
WHERE tasks.dt_created > tasks.dt_created > now () - interval '1 day' AND
tasks.status_id=3

Am sure this is a simple query, but I must be searching using the wrong terminology as I find nothing

Upvotes: 1

Views: 62

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

If I understand the logic correctly, you want all actions older than one day. Then you want newer actions where all the tasks are failed. The following query implements this logic:

select action_id
from tasks t
group by action_id
having sum(case when t.dt_created < now () - interval '1 day' then 1 else 0 end) > 0 or
       sum(case when status_id <> 3 then 1 else 0 end) = 0;

Perhaps, though, you want the following:all actions that have tasks in the last 24 hours that did not fail:

select action_id
from tasks t
where t.dt_created >= now () - interval '1 day'
group by action_id
having sum(case when status_id <> 3 then 1 else 0 end) = 0;

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125224

select action_id
from
    actions
    inner join
    tasks using(action_id)
where tasks.dt_created > now () - interval '1 day'
group by action_id
having bool_and(status_id = 3)

bool_and will evaluate to

true if all input values are true, otherwise false

http://www.postgresql.org/docs/current/static/functions-aggregate.html

This version using bool_or might be slightly faster

select action_id
from
    actions
    inner join
    tasks using(action_id)
where tasks.dt_created > now () - interval '1 day'
group by action_id
having not bool_or(status_id <> 3)

Upvotes: 1

Related Questions