NOT EXISTS is not working for multiple records which has same value-CodePudding

I am try to create a SQL query for the user should have revoke entry in Database for the respective grant access for that study. For ex, if the user is having total count of 20 GRANT records then the user should have total count of REVOKE records. If not we need to enter the missing records.

I have created the following query. The query is working fine for the user who is having single Grant record for study but if the user is having Multiple grant record for particular study its not working.

For ex. User A is having two GRANT records and 1 REVOKE records for study ABC. the below query is not showing any output since the REVOKE record is already listed for that study.

But we have only one REVOKE records for that study. So I want to display that MISSING GRANT record

SELECT Getdate() [RequestedDate],
   Getdate() [ApprovedDate],
   A.requestedfor,
   'XXX'    [ApprovedBy],
   A.projectnumber,
   'Revoke'  [AccessRequestType]
 
FROM   [dbo].[Table1] A
WHERE  A.requestedfor  = 1234
   AND A.accessrequesttype = 'Grant'
   AND NOT EXISTS (SELECT *
                   FROM   [dbo].[Table1] B
                   WHERE  B.requestedfor =1234
                          AND B.accessrequesttype = 'Revoke'
                          AND A.projectnumber =  B.projectnumber
                          AND A.accessgroup = B. accessgroup
                          AND A.RequestedFor = B. RequestedFor
                          )

CodePudding user response：

I think you should use LEFT JOIN to link rows 1 to 1 and filter in WHERE clause by null fot that rows, where not exists linked pair by access type 'revoke'.

EXISTS clause checks only existed data. For example you want to insert new rows without duplicates.

SELECT  a.[RequestedDate],
   a.[ApprovedDate],
   A.requestedfor,
   a.ApprovedBy,
   A.projectnumber,
   'Revoke'  [AccessRequestType]
 
FROM   #Table1 A
LEFT JOIN #Table1 B ON        B.accessrequesttype = 'Revoke'
                          AND A.projectnumber =  B.projectnumber
                          AND A.accessgroup = B. accessgroup
                          AND A.RequestedFor = B. RequestedFor
WHERE  A.requestedfor  = 1234
   AND A.accessrequesttype = 'Grant'
   ---this shows only rows, which not have pair 'Grant'-'Revoke'
   AND B.ID IS NULL

Exists clause

Example result set to show an idea of query:

ReqDate	RequestedBy	AccessType	projNumber	accessGroup	RequestedFor	AccessType
2019-07-31	XXXXXX	Grant	1	1	1	Revoke
2019-07-30	XXXXX1	Grant	1	1	1	NULL
2019-07-29	XXXXXX	Grant	2	2	2	Revoke
2019-07-30	XXXXX1	Grant	1	1	1	NULL
2019-07-01	XXXXX1	Grant	3	2	2	NULL

CodePudding user response：

By numbering the rows we can pair up GRANTs with REVOKEs, and return unmatched/excess GRANTs as new Revoke entries:

 select
   Getdate() [RequestedDate],
   Getdate() [ApprovedDate],
   A.requestedfor,
   'XXX'    [ApprovedBy],
   A.projectnumber,
   'Revoke'  [AccessRequestType]
from (
    SELECT requestedfor, projectnumber, seq=row_number() over (order by RequestedFor)
    FROM Table1
    where RequestedFor=1234
    and AccessRequestType='Grant'
    except 
    SELECT requestedfor, projectnumber, seq=row_number() over (order by RequestedFor)
    FROM Table1
    where RequestedFor=1234
    and AccessRequestType='Revoke'
    ) a
     ```
@maxim's proposal should also work.