William Xu William Xu - 6 months ago 13
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?
Thanks!

Answer

So many ways:

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

or

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

or

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

or

select distinct Item from T t
where 'Complete' = all (select Progress from T t2 where t2.Item = t.Item)