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

Question

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
                          )

You might as well get rid of the `b. RequestedFor = 1234` filter. — shawnt00, Feb 08 '22 at 06:43
You're checking for existence only. If you want counts to match then you'll need to compute those. — shawnt00, Feb 08 '22 at 06:47
It's already correlated with the outer query. The inner filter is redundant. — shawnt00, Feb 08 '22 at 06:48
Fyi, I didn't say that was supposed to fix the problem. Look at my comment about counts. Unless there are columns you left out of the sample I think that must be what you intend. — shawnt00, Feb 08 '22 at 06:59

Maxim · Answer 1 · 2022-02-08T09:00:20.467

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

the primary key in B table is not ID its RequestID.. i have checked B.RequestID IS NULL.. still getting empty result — Ganesan VC, Feb 08 '22 at 07:37
Unfortunately You didn't describe your table schemas, so you should write executable query by yourself. I answered about main idea to solve your problem. Main idea is link two queries - first query select data with 'Grant', second query select data with 'Revoke'. So, when you join this sets by some key to get 1 to 1 relationship, on the right side you will get NULL value for fows, where not existed 'Revoke' row for 'Grant' row. See more https://stackoverflow.com/questions/10292355/how-to-create-a-real-one-to-one-relationship-in-sql-server — Maxim, Feb 08 '22 at 07:51

score 0 · Answer 2 · answered Feb 08 '22 at 09:26

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.

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

2 Answers2