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
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 |