Search code examples
mysqlsqldatabasegroup-byhaving

Filter table to leave out specific rows

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

Solution

  • 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;