Trying to join two tables together, I want to filter the select results based on a parameter in the first table and the second table. Having an issue in the not exists where I only want either the value when no value exists, or the custdef_id is one of two values, 13 or 15. So Having the exist does not select the data where the value does not exist, so trying a not exist condition to filter all stated custdef_id values of 20 and 21 but then it also cuts off the 15 value?
First query:
SELECT
BILL_NUMBER,
deliver_by_end,
actual_delivery,
c.custdef_id,
(case when c.custdef_id = '13' then c.data end) "Late Flag",
(case when c.custdef_id = '15' then c.data end) "Planned Late Flag"
FROM TLORDER_all T LEFT join custom_data c on t.detail_line_id = c.src_table_key
WHERE
bill_number in ('T119633','T119634','T121374')
AND DATE(T.DELIVER_BY) between :STARTDATE and :ENDDATE
and companY_id = '1'
AND T.ACTUAL_DELIVERY IS NOT NULL
AND ((T.ACTUAL_DELIVERY > (T.DELIVER_BY_END + :MINUTESLATE MINUTES) AND TIME(T.DELIVER_BY_END) <> '00:00:00') OR
((TIME(T.DELIVER_BY_END)) = '00:00:00' AND T.ACTUAL_DELIVERY >= T.DELIVER_BY_END + 1 DAY))
order by
customer, bill_number
query results:
BILL_NUMBER DELIVER_BY_END ACTUAL_DELIVERY CUSTDEF_ID Late Flag Planned Late Flag
T119633 1/31/2023 23:59:00 2/3/2023 9:22:03 <null> <null> <null>
T119634 1/31/2023 23:59:00 2/6/2023 11:57:17 <null> <null> <null>
T121374 1/27/2023 1:30:00 1/27/2023 2:02:36 20 <null> <null>
T121374 1/27/2023 1:30:00 1/27/2023 2:02:36 21 <null> <null>
T121374 1/27/2023 1:30:00 1/27/2023 2:02:36 15 <null> Yes
I want the first two rows and last row. The repeating rows of 3 an 4 are pulling values I do not want. Tried a second query with a not exist of those CUSTDEF_ID of 20 and 21, and then I lose all but the first two rows?
SELECT
BILL_NUMBER,
deliver_by_end,
actual_delivery,
c.custdef_id,
(case when c.custdef_id = '13' then c.data end) "Late Flag",
(case when c.custdef_id = '15' then c.data end) "Planned Late Flag"
FROM TLORDER_all T LEFT join custom_data c on t.detail_line_id = c.src_table_key
WHERE
not EXISTS(SELECT * FROM CUSTOM_DATA C WHERE T.DETAIL_LINE_ID = C.SRC_TABLE_KEY and C.custdef_id NOT in ('20','21') )
and bill_number in ('T119633','T119634','T121374')
AND DATE(T.DELIVER_BY) between :STARTDATE and :ENDDATE
and companY_id = '1'
AND T.ACTUAL_DELIVERY IS NOT NULL
AND ((T.ACTUAL_DELIVERY > (T.DELIVER_BY_END + :MINUTESLATE MINUTES) AND TIME(T.DELIVER_BY_END) <> '00:00:00') OR
((TIME(T.DELIVER_BY_END)) = '00:00:00' AND T.ACTUAL_DELIVERY >= T.DELIVER_BY_END + 1 DAY))
order by
customer, bill_number
Results:
BILL_NUMBER DELIVER_BY_END ACTUAL_DELIVERY CUSTDEF_ID Late Flag Planned Late Flag
T119633 1/31/2023 23:59:00 2/3/2023 9:22:03 <null> <null> <null>
T119634 1/31/2023 23:59:00 2/6/2023 11:57:17 <null> <null> <null>
Using NOT IN inside a NOT EXISTS might be tripping you up, but it's hard to say exactly as we cannot see the DETAIL_LINE_ID value.
Why not simply use:
WHERE (CUSTDEF_ID IS NULL OR CUSTDEF_ID IN ('13','15'))