I have 2 Tables “Transport_Status” and “Receipts” where the id number is the key and can occur in Transport_Status more than once.
I want to protocol the transmission of the Receipts
Only if a 200 code does not appear, show the resulting set (Show only results with Error code 900)
What I have is:
Select * from Transport_Protocoll
inner join Receipts on Transport_Protocoll.id=Receipts.id
where not exists(select * where TRANSPORT_STATUS=200)
But I still receive the results from the 900 Error even when TRANSPORT_STATUS=200 exists
Transport_Protocolls:
| ID | Transport_Status | Transport_Detail | Date |
|---|---|---|---|
| 1111126 | 900 | Network is unreachable (connect failed) | 10.07.2024 |
| 1111126 | 200 | receipt send successfully | 14.07.2024 |
| 1111127 | 900 | Network is unreachable (connect failed) | 10.07.2024 |
Reiceipts:
| ID | order_date | article | Date |
|---|---|---|---|
| 1111126 | 1.07.2024 | Ice Cream | 10.07.2024 |
| 1111127 | 1.01.2024 | Yogurt | 03.07.2024 |
| 1111128 | 1.05.2024 | Bogey Man | 03.07.2024 |
Expected result:
| ID | Transport_Status | Transport_Detail | Date |
|---|---|---|---|
| 1111127 | 900 | Network is unreachable (connect failed) | 10.07.2024 |
| 1111128 | null | null) | null |
Because receipt with Id 1111126 failed at some point but was afterwards send successfully.
And 1111127 was not successfully in any other point
8
You want Receipts transmission protocol status.
So, first take table Receipts and LEFT JOIN Transport_Protocols. And result contain rows from table Receipts, where there is no entry in the Transport Protocol table.
See example
Select r.ID, t.Transport_Status,t.Transport_Detail,t.Date
from Receipts r
left join Transport_Protocols t on t.id=r.id
where not exists
(select 1 from Transport_Protocols t2
where t2.id=t.id and TRANSPORT_STATUS=200
)
Output
| ID | Transport_Status | Transport_Detail | Date |
|---|---|---|---|
| 1111127 | 900 | Network is unreachable (connect failed) | 2024-07-10 |
| 1111128 | null | null | null |
demo