>>>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 :-)
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.