How to perfom recursive query without cursors

Question

I have 2 tables

Person

id
----
1
2

Order

Id   Qty
---------
1    3
1    0
2    2

How can I get all persons with orders greater than 0 that have never placed a 0 order? So the result of this would only Person.Id 2

I think I know how to do this with cursors but want to find a different way.

EDIT:

I think in an attempt to make the example short and easy to read I forgot one detailed.. I am not really filtering by Id's. I am filtering by text.

The correct example would be a Customer table, with an order table, in which i want all who have ordered chicken, but have not ordered chicken and fish.

Customer

id
----
1
2
3

Order

Id   Dish
----------------
1    chicken
2    chicken
2    fish
3    fish 

So the result should only be Customer Id 1

Answer 1

It's not recursive, but it is a "different way":

SELECT DISTINCT id 
FROM Order 
WHERE Qty > 0 
AND id NOT IN (SELECT DISTINCT id FROM Order WHERE Qty = 0)

The inner query gets a list of ids that have placed zero orders, and the outer query gets a list of ids that have (a) placed non-zero orders, and (b) are not in the first list.

Answer 2

There's nothing recursive about solving the question.

Using NOT EXISTS

SELECT p.id
  FROM PERSON p
 WHERE NOT EXISTS(SELECT NULL
                    FROM ORDER o
                   WHERE o.id = p.id
                     AND o.qty = 0)

Using NOT IN

SELECT p.id
  FROM PERSON p
 WHERE p.id NOT IN (SELECT o.id
                      FROM ORDER o
                     WHERE o.qty = 0)

Using LEFT JOIN/IS NULL

   SELECT p.id
     FROM PERSON p
LEFT JOIN ORDER o ON o.id = p.id
                 AND o.qty = 0
    WHERE o.id IS NULL

Answer 3

select p.id
from Person p
left join Order o on p.id = o.id
group by p.id
having count(o.id) > 0 and min(o.qty) > 0

Answer 4

For a single pass answer to the revised question, try:

SELECT Id
FROM Order
GROUP BY Id
HAVING SUM(case when Dish = 'chicken' then 1 else 0 end) > 0
   AND SUM(case when Dish = 'fish' then 1 else 0 end) = 0