NOT EXISTS with two subquery fields that match 2 fields in main query

Question

Background: Two different document types in a document management system. Both Doc Type A and Doc Type B have a Ticket #, and a Ticket Date. What we're looking for: Doc Type A docs that don't have a matching Doc Type B doc (NOT EXISTS) with the same Ticket # and Ticket Date. There like are Doc Type B docs that have the same Ticket # but NOT the same Ticket Date. We want to ignore those. Seems simple.... but I am stuck. So far what I have is something like this:

    SELECT 
       DISTINCT ki110.keyvaluechar AS "Ticket #",
       ki101.keyvaluedate AS "Ticket Date"
    FROM 
       itemdata 
    LEFT OUTER JOIN
       hsi.keyitem110 ki110 on ( itemdata.itemnum = ki110.itemnum )
    LEFT OUTER JOIN 
       hsi.keyitem101 ki101 on ( itemdata.itemnum = ki101.itemnum )
    WHERE  
       ki101.keyvaluedate BETWEEN '01-01-2021' AND '01-31-2021'
    AND 
      ( 
        itemdata.itemtypenum  = 178  
      ) -- this is Doc Type A
    AND NOT EXISTS
       (
        SELECT
           ki110.keyvaluechar, 
           ki101.keyvaluedate
        FROM
           itemdata, 
           keyitem110 ki110 , 
           keyitem101 ki101
        WHERE  --(itemdata.itemnum = ki110.itemnum) --Ticket #
   
                -- ** the problem is here  for Date: I need to say Date in Doc Type B doc is not the same as Date in Doc Type A doc using ki101.keyvaluedate)

        AND itemdata.itemtypenum = 183) -- this  is DOC Type B

Answer 1

You are not using EXISTS correctly. In an EXISTS, the selected column makes no difference, it is entirely ignored and does not even need a name. This is why I favour the syntax EXISTS (SELECT 1 all on one line, because effectively it is just extra syntax of the EXISTS not of the subquery.

In your example, you also need to correlate the subquery to the outer.

EDIT I think I know what you want now.

If you follow the logic, you need to exclude rows in your outer where there are matching rows in the inner with the same ticket and date.

Notes:

1. The LEFT JOIN doesn't make sense here as you are filtering on it's columns, it may as well be an INNER JOIN.
2. The best format for dates is yyyy-mm-dd as that will not change depending on language and region.
3. You should use half open intervals for datetime with a time component or you will get wrong results, so dateCol >= start and dateCol < dayAfterEnd. I haven't changed this as you may have a date-only column.
4. Use more sensible names for the alias
5. We can extract out the join logic into a CTE
6. Don't use column names that need quoting

;WITH cte AS (
    SELECT
       ticketNumber.keyvaluechar AS TicketNum,
       ticketDate.keyvaluedate AS TicketDate,
       itemdata.itemtypenum,
       itemdata.itemnum
    FROM 
       itemdata 
    INNER JOIN
       hsi.keyitem110 ticketNumber on ( itemdata.itemnum = ticketNumber.itemnum )
    INNER JOIN 
       hsi.keyitem101 ticketDate on ( itemdata.itemnum = ki101.itemnum )
)
SELECT DISTINCT
   TicketNum, TicketDate
FROM 
   cte
WHERE  
   ticketDate BETWEEN '01-01-2021' AND '01-31-2021'
AND 
  ( 
    cte.itemtypenum  = 178  
  ) -- this is Doc Type A
AND NOT EXISTS (SELECT 1
    FROM
       cte cte2
    WHERE cte2.itemnum = cte.itemnum --Same Ticket #
        AND cte2.TicketDate = cte.TicketDate  -- Same dates
        AND cte2.itemtypenum = 183  -- this  is DOC Type B
)

Note also that according to this, your LEFT JOINs should probably be INNER JOIN

---

As I see that someone has mentioned NOT IN, I will just say: NOT IN is usually a very bad idea, because on nullable columns it gives incorrect data.

See here, for example, for an explanation why.

Answer 2

How about a left join? Pseudo-code

SELECT ...
   FROM itemdata i
 LEFT JOIN itemdata i2
   ON i.ticketNo = i2.ticketNo
  AND i.ticketDate = i2.ticketDate
  AND i2.itemtypenum = 183
WHERE i.itemtypenum = 178
   AND i2.ticketNo is null

Answer 3

--Doc Type A docs that don't have a matching Doc Type B doc (NOT EXISTS) with the same Ticket # and Ticket Date

 SELECT
      *
    FROM
    keyitem101 ki1011 where
       not exists
       (select * from 
       keyitem101 ki101 
        join keyitem110 ki110
    on  ki101.itemnum = ki110.itemnum and ki101.keyvaluedate = ki110.keyvaluedate
    where ki1011.itemnum =ki101.itemnum and ki1011.keyvaluedate=ki101.keyvaluedate) 
    and convert(VARCHAR(10),ki1011.keyvaluedate,101) BETWEEN '01/01/2021' AND '01/31/2021';

--There like are Doc Type B docs that have the same Ticket # but NOT the same Ticket Date We want to ignore those.

   SELECT
      *
    FROM
    keyitem110 ki1101 where
       not exists
       (select * from 
       keyitem110 ki110
        join keyitem101 ki101
    on  ki110.itemnum = ki101.itemnum and ki110.keyvaluedate  ki101.keyvaluedate
    where ki1101.itemnum =ki110.itemnum and ki1101.keyvaluedate=ki110.keyvaluedate)

based on a data model like this

   

create table itemdata (itemnum numeric,itemtypenum numeric);
create table keyitem110 (itemnum numeric,keyvaluechar varchar(10),keyvaluedate date,itemtypenum numeric);
create table keyitem101 (itemnum numeric,keyvaluechar varchar(10),keyvaluedate date,itemtypenum numeric);

insert into itemdata values(1,183),(2,178),(2,183),(3,183),(4,178),(4,183),(5,183),(6,178),(6,183),(7,183),(8,178);
insert into keyitem101 values (6,'docA','01-17-2021',178),(4,'docA','01-19-2021',178),(4,'docA', '09-12-2020',178),(2,'docA','01-10-2021',178),
                (8,'docA','12-01-2021',178),(4,'docA', '01-12-2020',178)

insert into keyitem110 values (1,'docB','11-15-2020',183),(1,'docB','01-16-2021',183),(2,'docB', '01-11-2021',183),(3,'docB','10-10-2020',183),(3,'docB','01-10-2021',183),
                                (4,'docB', '01-19-2021',183),(5,'docB', '11-15-2021',183),(5,'docB','01-19-2021',183),(6,'docB','12-17-2020',183),(6,'docB', '01-17-2021',183),
                               (7,'docB', '01-15-2020',183),(7,'docB','01-10-2021',183);