Home > Software design >  SQL: Count records not in subquery
SQL: Count records not in subquery

Time:09-27

I have the following data for two recipients.

Subscriptions for user 13250626 (recipient A)

enter image description here

Subscriptions for user 13251076 (recipient B)

enter image description here

I want to count the number of records, recipient A is registered to that recipient B isnt, my query is not working, returns 0 and I am expecting 1.

SELECT 
  COUNT(*) 
FROM 
  NmsSubscription 
WHERE 
  iRecipientId = 13250626 
  AND NOT EXISTS(
    SELECT 
      1
    FROM 
      NmsSubscription 
    WHERE 
      iRecipientId = 13251076
  )

CodePudding user response:

SELECT 
  COUNT(*) 
FROM 
  NmsSubscription O
WHERE 
  iRecipientId = 13250626 
  AND NOT EXISTS(
    SELECT 
      1
    FROM 
      NmsSubscription I
    WHERE 
      I.iRecipientId = 13251076 AND I.IServiceID = O.IServiceID
  )
  • Related