Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();

version

PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

SELECT 1

-- INIT database
CREATE TABLE Slot (
  id SERIAL PRIMARY KEY,
  time_off_id INTEGER
);

CREATE TABLE Alterr (
  id SERIAL PRIMARY KEY,
  slot_id INTEGER,
  action INTEGER
);

INSERT INTO Slot(time_off_id) VALUES (null);
INSERT INTO Slot(time_off_id) VALUES (null);
INSERT INTO Slot(time_off_id) VALUES (null);
INSERT INTO Slot(time_off_id) VALUES (null);
INSERT INTO Slot(time_off_id) VALUES (null);
INSERT INTO Slot(time_off_id) VALUES (1);
INSERT INTO Slot(time_off_id) VALUES (null);
INSERT INTO Slot(time_off_id) VALUES (null);
INSERT INTO Slot(time_off_id) VALUES (2);
INSERT INTO Slot(time_off_id) VALUES (null);
INSERT INTO Slot(time_off_id) VALUES (2);
INSERT INTO Slot(time_off_id) VALUES (null);

INSERT INTO Alterr(slot_id, action) VALUES (1, 0); -- 1
INSERT INTO Alterr(slot_id, action) VALUES (1, 1); -- 2
INSERT INTO Alterr(slot_id, action) VALUES (2, 0);
INSERT INTO Alterr(slot_id, action) VALUES (5, 0);
INSERT INTO Alterr(slot_id, action) VALUES (7, 1);
INSERT INTO Alterr(slot_id, action) VALUES (9, 0);
INSERT INTO Alterr(slot_id, action) VALUES (10, 0);
INSERT INTO Alterr(slot_id, action) VALUES (10, 1);

CREATE TABLE

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

select * from  Slot;
select * from  Alterr

id
time_off_id

1
null

2
null

3
null

4
null

5
null

6
1

7
null

8
null

9
2

10
null

11
2

12
null

SELECT 12

id
slot_id
action

1
1
0

2
1
1

3
2
0

4
5
0

5
7
1

6
9
0

7
10
0

8
10
1

SELECT 8

select s.id, s.time_off_id
from Slot s
where time_off_id is null
and not exists (select 1 from Alterr a where s.id = a.slot_id and a.action = 1)
order by s.id;

id
time_off_id

2
null

3
null

4
null

5
null

8
null

12
null

SELECT 6

select s.id, s.time_off_id, a.id, a.action
from Slot s
left outer join Alterr a
on s.id = a.slot_id
where time_off_id is null
and (action is null or action = 0)
order by s.id;

id
time_off_id
id
action

1
null
1
0

2
null
3
0

3
null
null
null

4
null
null
null

5
null
4
0

8
null
null
null

10
null
7
0

12
null
null
null

SELECT 8

id	time_off_id
1	null
2	null
3	null
4	null
5	null
6	1
7	null
8	null
9	2
10	null
11	2
12	null

id	time_off_id
2	null
3	null
4	null
5	null
8	null
12	null

id	time_off_id	id	action
1	null	1	0
2	null	3	0
3	null	null	null
4	null	null	null
5	null	4	0
8	null	null	null
10	null	7	0
12	null	null	null