>>>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 :-)