这是名为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 |
名为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 |
名为event的第三个子表，每个记录route_detail有1event.这是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 |
我想要做的是获得visit_status = 5的所有路由，而不是event_type = 3(end visit)的活动
但我没办法得到那个结果
在进行了一些研究之后，我try 了这样的方法，但查询结果仍然是routes,route_details,event_type = 3
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;
这就是我的结果应该是什么样子，因为只有4路和5路有visit_status = '5'，而他们的route_details没有event_type =3
Note:桌子不是我做的
| id | start_day | end_day |
|---|---|---|
| 4 | 2023/05/01 | 2023/05/07 |
| 5 | 2023/05/01 | 2023/05/07 |