Thank you Boris and Naomi!
>>>>Hi All:
>>>>
>>>>Consider the following table:
>>>>
>>>>
>>>>Artnum Catnum
>>>>111 INT
>>>>111 ISR
>>>>111 GRP
>>>>222 INT
>>>>222 RSA
>>>>222 GRP
>>>>
>>>>
>>>>I want a select statement that says 'give me all the records for a particular artnum WHERE the artnum belongs to a catnum of BOTH 'INT' and 'ISR'.
>>>>
>>>>This would produce the following list:
>>>>
>>>>
>>>>111 INT
>>>>111 ISR
>>>>111 GRP
>>>>
>>>>
>>>>Thanks,
>>>>
>>>>Yossi
>>>
>>>This is the problem that is called Relational Divison Problem. Peter Larsson has a very interesting blog about it.
>>>
>>>
http://sqlblog.com/blogs/peter_larsson/archive/2010/06/30/relational-algebra.aspx>>>
>>>one of the possible solutions:
>>>
>>>select ArtNum from YourTable
>>>where CartNum IN ('INT','ISR')
>>>group by ArtNum
>>>having MIN(CatNum) = 'INT' and MAX(CatNum) = 'ISR'
>>
>>This produces only one record. What do you think of Sergey's 2 solutions?
>
>
>:-)
>
>SELECT YourTable.*
>FROM YourTable
>INNER JOIN (select ArtNum
> from YourTable
> where CartNum IN ('INT','ISR')
> group by ArtNum
> having MIN(CatNum) = 'INT' and MAX(CatNum) = 'ISR') Tbl1
> ON YourTable.ArtNum = Tbl1.ArtNum
>
>
>> What do you think of Sergey's 2 solutions?
>
>They are great (as always)
>But Naomi here gives you just another way. Sometimes you must know all possible ways so you can choose the best.
>Turn Actual Execution Plan ON and run all queries, then analyse the result :-)