SQL. How to combine subquery and join correctly?

Question

I need to list the customer name(s) and their project title(s) that has employee GLC working on a task but not employee CAC working on a task.**

I tried the following, but got an empty set:

select 
   c.custname, p.pjtitle
from 
   (select taskno pjno
    from task 
    where taskno not in
                 (select taskno
                  from task
                  where empid = 'CAC')
    and empid = 'GLC') t
join project p on p.pjno = t.pjno
join customer c on p.custno = c.custno;

How should I use join and subquery correctly?

Answer 1

SELECT c.custname, p.pjtitle
  FROM customer c
  JOIN project p ON p.custno = c.custno
  JOIN task t ON p.pjno=t.pjno
 WHERE t.empid='GLC'
   AND NOT EXISTS (SELECT NULL FROM task t1 WHERE t.pjno=t1.pjno AND t1.empid='CAC')
;

Answer 2

If you want the customer name and the project name, then presumably you do not want duplicates. This is an example of a set-within-sets subquery. I think the best solution is to use aggregation, because that offers the most flexibility in describing the conditions.

SELECT c.custname, p.pjtitle 
FROM customer c join
     project P
     ON P.CUSTNO = P.CUSTNO join
     task t ON t.pjno = p.pjno
group by c.custname, p.pjtitle 
having sum(case when empid = 'GLC' > 0 then 1 else 0 end) > 0 and
       sum(case when empid = 'CAC' > 0 then 1 else 0 end) = 0

The two parts to the having clause are counting the number of times that each employee is working on the particular project. The first says "make sure GLC is working on at least once task" and the second says "make sure CAC is working on no tasks".