By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE orders (
id int
);
INSERT INTO orders VALUES (1), (2), (3), (4);
CREATE TABLE bills (
id int,
order_id int,
status text
);
INSERT INTO bills VALUES (1,1,'failed'), (2,2,'failed'), (3,3,'failed'), (4,4,'successful');
CREATE TABLE rebills (
id int,
bill_id int,
status text
);
INSERT INTO rebills VALUES (1, 1, 'failed'), (1,1,'successful'), (1,2,'failed');
4 rows affected
4 rows affected
3 rows affected
SELECT * FROM orders
INNER JOIN bills ON bills.order_id = orders.id
WHERE status = 'failed'
AND NOT EXISTS(SELECT 1 FROM rebills WHERE rebills.bill_id = bills.id AND rebills.status = 'successful');
| id | id | order_id | status |
|---|---|---|---|
| 2 | 2 | 2 | failed |
| 3 | 3 | 3 | failed |