Re: SQL problem finding rows in same table

DazedNConfused <nospamthanks_at_coldmail.com> wrote in message news:AJCM5.13707$rl.1150876_at_newsread2.prod.itd.earthlink.net...
> 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
>
>

Since a select deals with rows, and there are multiple rows involved in each occurance, these multiple rows are (sometimes) appearing in your query. Use the 'distinct' keyword to get rid of these duplicates. Your query becomes:

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

Noel Received on Mon Nov 06 2000 - 00:21:42 CST