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