0

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
6
  • can you please tag your rdms and provide a proper minimal reproducible example and is there a good cause why you swithced from joins to comma separated tables – nbk Jan 18 at 21:23
  • Sorry. I don't understand what you mean by 'tag you rdms'. – Joe Pineda Jan 18 at 22:03
  • a rdms is the server that you use so tagb your question with mysql mariadb psotgres and so on and version. a minimal reproducible example helps to understand your qiestion and wanted result. if you make a dbfiddle you have to select a rdms and so help is eaiser becasue doffrent rdms have different solution for your problem – nbk Jan 18 at 22:34
  • Left join doesn't make sense if you have WHERE ki101.keyvaluedate BETWEEN ... may as well use inner join – Charlieface Jan 19 at 1:49
  • The db is on a Microsoft sql server. – Joe Pineda Jan 19 at 1:56
3

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.

7
  • You are right. I am trying to post the reworked query but can't yet figure how to post the code correctly. In any case, what I need is to compare the ki101.keyvaluedate between Doc Type A & B, and if they are the same (along with ki110.Keyvaluechar- Ticket#), ignore them. But I don't know how to refer back to the main query (Doc Type A) from the subquery (Doc Type B) – Joe Pineda Jan 19 at 17:20
  • Can you describe to me the logic you are trying to achieve in plain english, because I haven't got that quite right in this answer? Are you just trying to exclude rows in the outer query where a match is found in the inner, or are you saying that the inner query still needs further filtering before applying? You have outer references in an EXISTS or APPLY no problem, no need to select the tables again, just make sure you alias the tables correctly. – Charlieface Jan 19 at 19:50
  • Sure Charlie: I want to get a report of Type A Tickets showing their Ticket # and Ticket Date when: a) A Type B Ticket with the same Ticket # DOES NOT exist or b) A Type B ticket exists with the same Ticket # but a different Ticket Date than the one found on the Type A ticket. The Ticket # and Ticket Date values are from the ki110 and ki101 tables respectively. The itemdata table does not contain those specific values, but I need to query it because there are other columns in the report that i need to get that are found on the itemdata table. That's why I can't use itemdata table for Date. – Joe Pineda Jan 19 at 20:55
  • OK got you have updated – Charlieface Jan 19 at 21:36
  • Thanks Charlieface. I'll test it out tomorrow. Appreciate your help! – Joe Pineda Jan 19 at 22:37
0

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
0

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



2
  • So, I did try the NOT IN. But I can only use one expression in the SELECT subquery. I need to use both the Ticket# and Date on the Type B documents so I can ignore them if the Ticket Date is different from the date on the Type A documents. Both types would have the same ticket #. – Joe Pineda Jan 19 at 1:58
  • NOT IN is inadvisable in a lot of cases stackoverflow.com/questions/129077/… – Charlieface Jan 19 at 2:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.