sql-serverrowfilter

Filter multiple rows in SQL Server

This is my table:

ori table

The filteredID is the criteria that the rows will be filtered against. However, if one row meets the criteria, I want to delete all rows with the same staffID. For example, if the filteredID is set to 50 or 88.I want to filter all the "james" rows.

So my output will be:

output table

Could not think of a elegant way to tackle this.

Solution

  • the simplest way to achieve this result use 'not in' operator in where clause

    select 
    staffID, Name 
from 
    Staff 
where 
    staffID not in (select staffID from Staff where filteredID = 50) 
order by 
    staffID;