sqlsql-server

Find records with only one id

I have a table called Tags:

TagId Name
1 abc
2 abc
1 def
2 def
1 ret

How can I get distinct records that are only associated with Tagid = 1 but also should not have tagid = 2. So if a record is associated with 2 tagIds, it should not be returned.

I hope I am able to explain my ask. Thanks in advance.

Expected results:

TagId Name
1 ret

What I tried:

SELECT 
    * 
FROM
    (SELECT
         ROW_NUMBER() OVER (PARTITION BY TagId ORDER BY Name) RN
     FROM
         Tags) t 
WHERE
    t.tagId = 1
Solution

  • The simple solution would seem to be using NOT EXISTS e.g.

    select *
from Tags t
where not exists (
  select 1
  from Tags t1
  where t1.TagId <> t.TagId
  and t1.Name = t.Name
)
and t.TagId = 1;