Search code examples
sqlsql-serversql-server-2012subquerygreatest-n-per-group

SQL query to exclude records that already mapped with another column id and show rest to them

I am trying to achieve the following but not able to

Id   CatID  CategoryName
-------------------------
1    1      Movies
2    1      Movies
3    2      Books
4    3      Tools

What I am trying to achieve is :

I want all the records :

  1. not having ID as 1 and
  2. Exclude (CatID) records that ID = 1 is sharing with other IDs. In this case Id =1 and Id = 2 sharing the same CATID= 1 (Movies). I want to eliminate such records and show the rest of them.

Output :

Id   CatID  CategoryName
-------------------------
3    2      Books
4    3      Tools
Solution

  • You can use not exists:

    select t.*
from mytable t
where not exists (
    select 1 from mytable t1 where t1.catID = t.catID and t1.id = 1
)

    For performance with this query, consider an index on (catID, id).