William Xu William Xu - 2 years ago 65
SQL Question

An interesting tricky filter (don't know how to describe it in title)

My table is like this:

Item Subitem Progress
1 101 Complete
1 102 Pending
1 103 Pending
2 201 Complete
2 202 Complete

I want to SELECT the Items whose Subitems are all Complete --- in this case, Item #2. Any idea how to do that?

Answer Source

So many ways:

select Item from T group by Item
having min(Progress) = max(Progress) and max(Progress) = 'Complete'


having count(case when Progress = 'Complete' then 1 end) = count(*)


having count(case when Progress = 'Complete' then 0 else 1 end) = 0 /* handles nulls */


select distinct Item from T t
where 'Complete' = all (select Progress from T t2 where t2.Item = t.Item)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download