Filter table to leave out specific rows

Question

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 :)

What database are you using? – Zakaria Jan 01 '22 at 15:09 — Zakaria, Jan 01 '22 at 15:09

score 2 · Accepted Answer · answered Jan 01 '22 at 15:14

2

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;

answered Jan 01 '22 at 15:14

forpas

160,666
10
38
76

score 1 · Answer 2 · answered Jan 01 '22 at 15:23

(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'
  )

Filter table to leave out specific rows

2 Answers2