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




Postgresql Need a query that gives me all the parents that don't have child with a specific status value
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.
