0

I have a table where I store logs in Postgres. These logs contain data for cases and the case number. Each row store the case number and more records.

Example:

+-----------------+-----------+---------+------+------+
| case number     | action_id | task_id | user | date |
+-----------------+-----------+---------+------+------+
| 14221/2018/FILE | 1         | 7       |      |      |
+-----------------+-----------+---------+------+------+
| 23456/2018/FILE | 1         | 7       |      |      |
+-----------------+-----------+---------+------+------+
| 14221/2018/FILE | 2         | 7       |      |      |
+-----------------+-----------+---------+------+------+
| 14221/2018/FILE | 1         | 6       |      |      |
+-----------------+-----------+---------+------+------+
| 24556/2018/FILE | 1         | 7       |      |      |
+-----------------+-----------+---------+------+------+
| 34567/2018/FILE | 1         | 7       |      |      |
+-----------------+-----------+---------+------+------+
| 24556/2018/FILE | 1         | 6       |      |      |
+-----------------+-----------+---------+------+------+

I'd like to create a query where the result is all the row where task_id = 7, but only contain those where case number has no action_id=2 pair.

Example result:

+-----------------+-----------+---------+------+------+
| case number     | action_id | task_id | user | date |
+-----------------+-----------+---------+------+------+
| 23456/2018/FILE | 1         | 7       |      |      |
+-----------------+-----------+---------+------+------+
| 24556/2018/FILE | 1         | 7       |      |      |
+-----------------+-----------+---------+------+------+
| 34567/2018/FILE | 1         | 7       |      |      |
+-----------------+-----------+---------+------+------+

I have to make report from the log but I don't know how to perform this query.

2

How about this (untested)?

select *
from log_table x
where task_id = 7
and not exists (
  select 1
  from log_table
  where case_number = x.case_number
  and task_id = x.task_id
  and action_id = 2
);
3
  • Hi Coli, the query is almost god but has no results. I used what you give us
    – Kgerg0
    Jun 28 '18 at 13:45
  • select * from log_table x where task_id = 7 and not exists ( select 1 from log_table where case_number = x.case_number and task_id = x.task_id and action_id = 1 ); Thank you here is the correct code :)
    – Kgerg0
    Jun 28 '18 at 13:50
  • That's rather different then your question: "only contain those where case number has no action_id=2 pair." Jun 28 '18 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.