Conditionally data extract from SQL table

Question

Hello Experts,

Please help to solve below business case..

I have a SQL 'Contact' table having ContactId as GUID and ContactType columns.

A Contact can have one or more then 1 contact types, these types are of User/Super User/Bespoke/Remove Access etc.

some sample data is shown below

AD529B74-E1B8-ED11-A81B-000D3A660B07 718960009(Recognised Organisation User)

2676ED09-E6B8-ED11-A81B-000D3A660B07 718960009(Recognised Organisation User)

183C6766-0DF2-EC11-BB3D-000D3A666558 718960002(Bespoke)

183C6766-0DF2-EC11-BB3D-000D3A666558 718960004(Super User)

183C6766-0DF2-EC11-BB3D-000D3A666558 718960006(User)

183C6766-0DF2-EC11-BB3D-000D3A666558 718960009(Recognised Organisation User)

C4798A24-4F8E-EC11-B400-000D3ABA1F5C 718960006(User)

C4798A24-4F8E-EC11-B400-000D3ABA1F5C 718960007(Grant User)

C4798A24-4F8E-EC11-B400-000D3ABA1F5C 718960009(Recognised Organisation User)

now the ask is to filter contact based on below criteria.

• If contact is just setup as 'Recognised Organisation User'  .
  • Action A
• If contact is setup as 'Recognised Organisation User'  AND any other role .
  • Action B

How a COntactId can be filtered based on above requirements, please suggest.

Thanks in advance

Answer 1

select ContactID, sum(case when ContactType = 'Recognised Organisation User' then 1 else 0 end) as ROUOnly, count(*) as AllTypes

from Contacts

group by ContactID

Then use logic for ROUOnly = AllTypes vs ROUOnly < AllTypes

Answer 2

Hi @PANDEY Prashant

Please check this:

--If contact is just setup as 'Recognised Organisation User' 
SELECT ContactId
FROM Contact A
WHERE ContactType = 'Recognised Organisation User' AND NOT EXISTS(SELECT * FROM Contact WHERE ContactId=A.ContactId AND ContactType<>A.ContactType)

--If contact is setup as 'Recognised Organisation User'  AND any other role
SELECT ContactId
FROM Contact A
WHERE ContactType = 'Recognised Organisation User' AND EXISTS(SELECT * FROM Contact WHERE ContactId=A.ContactId AND ContactType<>A.ContactType)

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.