SQL problem finding rows in same table

I have a table "fruit" like this:
 ORDERNUM BOXNUM ITEM
--------- --------- ------

        1         1 apple
        1         1 orange
        1         2 apple
        1         2 grape
        2         1 apple
        2         1 orange
        2         1 grape
        3         1 apple

Each ordernum and boxnum can only have one of each item type.

I want to find boxes with both apples and oranges, so I do:

select apple.* from fruit apple, fruit orange where apple.item='apple' and orange.item='orange' and apple.ordernum=orange.ordernum
and apple.boxnum=orange.boxnum;

And I get what I expect:

  ORDERNUM BOXNUM ITEM
--------- --------- -------

        1         1 apple
        2         1 apple

As I add data, I some times get results like:

  ORDERNUM BOXNUM ITEM
--------- --------- -------

        1         1 apple
        1         1 apple
        1         1 apple
        2         1 apple
        2         1 apple
        2         1 apple

But there is only one "apple" and one "orange" in ordernum=1, boxnum=1

Why do I get duplicate results from my query some times?

Thank you Received on Fri Nov 03 2000 - 11:37:36 CST