SQL using subquery

Please try the following...

SELECT volunteerName AS 'Volunteer Role'
FROM ( SELECT volunteerName AS volunteerName,
              quantityNeeded AS quantityNeeded,
              COUNT( volunteerOption ) AS volunteersCount
       FROM Delegate
       JOIN Orders ON Delegate.OrderId = Orders.Id
       RIGHT JOIN VolunteerRole ON Orders.eventId = VolunteerRole.eventId
                               AND Delegate.volunteerOption = VolunteerRole.volunteerName
       WHERE Orders.eventId = 1
       GROUP BY volunteerName,
                quantityNeeded
     ) AS volunteersCountFinder
WHERE quantityNeeded > volunteersCount
GROUP BY volunteerName;

This statement starts by performing an INNER JOIN between Delegate and Orders, giving us a list of delegates assigned to each order and thus to each event.

This list is then right-joined to VolunteerRole, giving us a list of delegates assigned to each event and each role within that event. A RIGHT JOIN is performed rather than an INNER JOIN so that roles at an event are still listed even when there are no delegates assigned.

Please note that a RIGHT JOIN is much the same as a LEFT JOIN. Which you use is determined by which side of the JOIN has the table from where nonmatching records should be retained.

The dataset resulting from the two joins is then refined to just those records with an eventId of 1 via the WHERE clause.

The refined dataset is then grouped by volunteerName. Subgrouping by quantityNeeded does not effectively refine or broaden the grouping by volunteerName since each value of volunteerName will have only one corresponding value of quantityNeeded, but GROUP BY requires you to use all fields not generated by an aggregate function for grouping.

The count of each non-NULL value of volunteerOption is then calculated. COUNT() will return 0 where a role has no delegates assigned, i.e. where it encounters a NULL value rather a non-NULL value for volunteerOption (not to be confused with volunteerName here, which will always have a value in each record).

The subquery then returns a list consisting of each volunteerName and its corresponding quantityNeeded and volunteersCount (the alias given to our count).

The main query then refines the subquery's dataset to just those where the quantity needed is greater than the count of assigned volunteers and group's the results by volunteerName. Each value of volunteerName from this group is then returned by the statement.

If you have any questions or comments, then please feel free to post a Comment accordingly.

Appendix

If you wish to extend this statement to list all events that have inadequately filled roles along with each of those inadequately filled roles, you can use...

SELECT eventId,
       volunteerName AS 'Volunteer Role'
FROM ( SELECT Orders.eventId AS eventId,
              volunteerName AS volunteerName,
              quantityNeeded AS quantityNeeded,
              COUNT( volunteerOption ) AS volunteersCount
       FROM Delegate
       JOIN Orders ON Delegate.OrderId = Orders.Id
       RIGHT JOIN VolunteerRole ON Orders.eventId = VolunteerRole.eventId
                               AND Delegate.volunteerOption = VolunteerRole.volunteerName
       GROUP BY Orders.eventId,
                volunteerName,
                quantityNeeded
     ) AS volunteersCountFinder
WHERE quantityNeeded > volunteersCount
GROUP BY eventId,
         volunteerName;

This gives you event_id,order_id,employee_type,employee_needed wherever you need some:

SELECT a.id as event_id, b.id as order_id, d.volunteerOption, c.quantityNeeded-d.employeed as still_to_find
FROM events a
LEFT JOIN orders b ON a.id=b.eventId
LEFT JOIN VolunteerRole c ON a.id=c.eventId
LEFT JOIN (SELECT order_id, volunteerOption, COUNT(*) as employeed FROM Delegate WHERE volunteerOption IS NOT NULL GROUP BY order_id, volunteerOption) d ON b.id=d.order_id AND c.volunteerName=d.volunteerOption
WHERE c.quantityNeeded<d.employeed 

I think you will have to do that in two steps, because you can't check an aggregated value against a non-aggregated one

select  distinct volunteerName
from    (
            SELECT  t1.volunteerName, t1.quantityNeeded, count(t2.Name) as cnt
            FROM    VolunteerRole t1
            JOIN    Orders t2
            ON      t1.eventId = t2.eventId
            LEFT JOIN
                    Delegate t3
            ON      t1.VolunteerName = t3.volunteerOption and
                    t2.Id = t3.OrderId
            WHERE   t1.eventId = 1
        ) t1
where   quantityNeeded > cnt

Try this query. this should work fine

select  distinct volunteerName from (SELECT  t1.volunteerName, t1.quantityNeeded, count(t2.Name) as cnt FROM    VolunteerRole t1 JOIN    Orders t2 ON      t1.eventId = t2.eventId
LEFT JOIN Delegate t3 ON t1.VolunteerName = t3.volunteerOption and t2.Id = t3.OrderId WHERE   t1.eventId = 1
 ) t1 where   quantityNeeded > cnt;