Postgresql Need a query that gives me all the parents that don't have child with a specific status value

This is the parent table named route

id	start_day	end_day
1	2023/05/01	2023/05/07
2	2023/05/01	2023/05/07
3	2023/05/01	2023/05/07
4	2023/05/01	2023/05/07
5	2023/05/01	2023/05/07

Child table named route_detail

id	route_id	visit_status	point_of_delivery_plant_name	point_of_delivery_plant_number
1	1	5	CROP SOLUTIONS S.A.	563
2	1	5	CROP SOLUTIONS S.A.	563
3	1	5	CROP SOLUTIONS S.A.	563
4	2	0	SAMA S.A.	781
5	3	0	WALTER SAMA HARMS	732
6	4	5	AGROSER S.A.	242
7	4	5	AGROSER S.A.	242
8	5	5	AGROFERTIL S.A	287
9	5	5	AGROFERTIL S.A	287
10	5	5	AGROFERTIL S.A	287

and a third child table named event, for each record route_detail there is 1 event. This is child to route_detail

id	route_detail_id	event_type	event_description
50	1	1	start visit
51	2	2	recurrent form
52	3	3	end visit
53	4	1	start visit
54	5	1	start visit
55	6	1	start visit
56	7	2	recurrent form
57	8	1	start visit
58	9	2	recurrent form
59	10	4	harvest advance

What I'm trying to do is to get all the routes with visit_status = 5 and that don't have events with event_type = 3(end visit) But I can't manage to get that result

I tried something like this after some research but the queries would still return routes with route_details with the event_type = 3 on them

SELECT r.id,
       r.start_day,
       r.end_day,
       de.point_of_delivery_plant_name,
       de.point_of_delivery_plant_number,
       de.visit_status
FROM route r
JOIN route_detail de ON de.route_id = r.id
WHERE NOT EXISTS (SELECT 1 
                  FROM route ro 
                  JOIN route_detail rd ON rd.route_id = ro.id 
                  JOIN event ev ON ev.route_detail_id = rd.id 
                  WHERE rd.route_id = r.id 
                    AND ev.event_type_id !=7 
                    AND rd.visit_status = '5' 
                    AND rd.id = de.id)
   AND de.visit_status = '5'
GROUP BY 1,2,3,4,5,6
ORDER BY r.id DESC;

This is how my results should look like, since only routes 4 and 5 have visit_status = '5' and their route_details don't have event_type =3

Note: I didn't make the tables

id	start_day	end_day
4	2023/05/01	2023/05/07
5	2023/05/01	2023/05/07

Solution

If you want to do it with the EXISTS expression, you can use:

one EXISTS to check the existence of route_detail.visit_status = 5
one EXISTS to check the non-existence of event.event_type = 3 when route_detail.visit_status = 5

SELECT r.*
FROM route r
WHERE EXISTS(SELECT 1
             FROM route_detail rd
             WHERE r.id = rd.route_id
               AND rd.visit_status = 5 )
  AND NOT EXISTS(SELECT 1 
                 FROM       route_detail rd
                 INNER JOIN "event"      e
                         ON rd.id = e.route_detail_id
                 WHERE r.id = rd.route_id
                   AND e.event_type = 3)

Output:

id	start_day	end_day
4	2023-05-01T00:00:00.000Z	2023-05-07T00:00:00.000Z
5	2023-05-01T00:00:00.000Z	2023-05-07T00:00:00.000Z

Check the demo here.