>>I have a table that looks something like this:
>>
>>
>>groupnum itemnum quantity
>>1 10 1
>>2 10 1
>>1 17 5
>>2 17 5
>>1 23 2
>>1 26 1
>>2 26 1
>>1 27 3
>>2 27 4
>>1 30 0
>>2 30 0
>>2 33 1
>>1 36 3
>>2 36 5
>>
>>
>>The table isn't really sorted, but we order it this way in queries. As you can see, most itemnums are found in groups 1 and 2, and usually have the same quantity. The user wants a query that returns those itemnums found in both groups, but with different quantities. In the above example, such a query would return:
>>
>>
>>1 27 3
>>2 27 4
>>1 36 3
>>2 36 5
>>
>>There are probably a few ways to do this, involving sequences of queries, but I wonder what would be the most efficient.
>
>Bret,
>
>Try...
>
>select * ;
>from thetable A, thetable B;
>where a.groupnum <> b.groupnum and;
>a.itemnum = b.itemnum and;
>a.quantity <> b.quantity
That worked. Actually, I replaced the * with A.groupnum, A.itemnum, A.quantity so I wouldn't also get the superfluous B.groupnum, B.itemnum, B.quantity and also so the fieldnames in the output would be the same as in the table without my having to name them.
Also, my problem wasn't quite as simple as I said. There are really more than two groupnums, so I made a query that selects WHERE groupnum = 1 OR groupnum = 2 and then ran your query off that cursor. Perhaps I could have merged them into one cursor, but this way seemed easiest.
Thanks.