Filter table to leave out specific rows-CodePudding

I have the table

| WorkerID | ProjectName |  Role  |
|----------|-------------|--------|
|     1    |    Test     | Leader |
|----------|-------------|--------|
|     4    |    Test     | Worker |
|----------|-------------|--------|
|     2    |    Stuff    | Leader |
|----------|-------------|--------|
|     3    |    Proj     | Worker |

and now I want to list every ProjectName where there is no specified Leader like this:

| ProjectName |
|-------------|
|    Proj     |

Right now I only know how to filter all ProjectNames with Leaders, but not the way to filter them the other way!

Any help is appreciated :)

CodePudding user response：

One way to do it is with aggregation and the condition in the HAVING clause:

SELECT ProjectName
FROM tablename
GROUP BY ProjectName
HAVING SUM(Role = 'Leader') = 0;

CodePudding user response：

(FYI: I'm mostly acquainted with PostgreSQL, so I'm not sure if this is completely transferrable)

I would do a subquery to identify the projects that have leaders, and in your main query, do a WHERE statement that selects all of the projects that are NOT IN the subquery.

SELECT ProjectName
FROM Table
WHERE ProjectName NOT IN (
  SELECT ProjectName
  FROM Table
  WHERE Role = 'Leader'
  )