Posted by frank m via SQLMonster.com on 07/13/05 11:05

Thanks for your reply. I tried this one:

select id from tbl
where
id in
(select id from tbl where cat='A')
and
id in
(select id from tbl where cat='B')

Which one do you think is more efficient? In reality I have 1 million records
and there are probably up to 10 CAT values.

Thanks


Erland Sommarskog wrote:
>> I can't make my mind up what the most efficient query is for the following
>> problem. My table contains records similar to this example:
>[quoted text clipped - 6 lines]
>> I now want to extract all ids that have the CAT value 'A' and 'B' .
>> In this example the result would be 1,3.
>
>SELECT DISTINCT id
>FROM tbl a
>WHERE EXISTS (SELECT *
> FROM tbl b
> WHERE a.id = b.id
> AND b.CAT = 'A')
> AND EXISTS (SELECT *
> FROM tbl b
> WHERE a.id = b.id
> AND b.CAT = 'B')
>


--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-general/200507/1

[Back to original message]