How to Display data with 'yes' or 'no' entries unique.

This is my data

unitCode stunum assNo   subStatus 
SIT111  1000    1   Yes
SIT111  开发者_JAVA百科1000    2   No
SIT111  3000    1   No

How do i generate only results with ONLY a 'No'

eg: Only student 3000 should come up. Since that person has not handed in ANY assignments. ?

---

You can use the following statement:

select
    *
from
(
    select
        unitCode,
        stunum,
        sum(case when subStatus = 'Yes' then 1 else 0 end) as CountYes,
        sum(case when subStatus = 'No' then 1 else 0 end) as CountNo
    from
        students
    group by
        unitCode, stunum
) as student
inner join student_details
    on student.stunum = student_details.stunum
where
    CountNo > 0 and CountYes = 0;

---

declare @T table
(
  unitCode varchar(10),
  stunum int,
  assNo int,
  subStatus varchar(3)
)  

insert into @T values
('SIT111',  1000,    1,   'Yes'),
('SIT111',  1000,    2,   'No'),
('SIT111',  3000,    1,   'No')

select *
from @T as T
where T.subStatus = 'No' and
      T.stunum not in (select stunum
                       from @T
                       where subStatus = 'Yes')