SQL query

[frank m via SQLMonster.com]

Hi,

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:
ID CAT
1 A
1 B
2 A
3 A
3 B
I now want to extract all ids that have the CAT value 'A' and 'B' .
In this example the result would be 1,3.

What's the best way to do it? Any help is much appreciated!

Cheers, Frank

[Pawe³ Kiraga]

Re: SQL query

Osoba podpisujaca sie jako frank m via SQLMonster.com popelnila na
comp.databases. ms-sqlserver co nastepuje:
[color=blue]
> What's the best way to do it? Any help is much appreciated![/color]

maybe not fastest, but...

select t.ID from YourTableName t
where t.CAT = 'A' or t.CAT = 'B'
group by t.ID
having count(t.ID) =2

is it right way?
--
~> pozdrawiam,
~> Pawe³ Kiraga
~> left(imie,1) && [dot] && nazwisko (na gmail-u)

[AK]

Re: SQL query

create table t(n int, c char(2))
insert into t values(1, 'A')
insert into t values(1, 'B')
insert into t values(2, 'A')
insert into t values(3, 'A')
insert into t values(3, 'B')
select a.n from t a, t b
where a.n = b.n
and a.c='A' and b.c='B'

n
-----------
1
3

drop table t

[Pawe³ Kiraga]

Re: SQL query

Osoba podpisujaca sie jako AK popelnila na comp.databases. ms-sqlserver co
nastepuje:
[color=blue]
> create table t(n int, c char(2))
> insert into t values(1, 'A')
> insert into t values(1, 'B')
> insert into t values(2, 'A')
> insert into t values(3, 'A')
> insert into t values(3, 'B')
> select a.n from t a, t b
> where a.n = b.n
> and a.c='A' and b.c='B'
>
> n
> -----------
> 1
> 3
>
> drop table t[/color]
hmmm...
bidalay wrote 'example' and 'query'.
try write it fro table with 1 million records ;>
--
~> pozdrawiam,
~> Pawe³ Kiraga
~> left(imie,1) && [dot] && nazwisko (na gmail-u)

[AK]

Re: SQL query

Pawel, your query might be just plain incorrect:

create table t(n int, c char(2))
insert into t values(1, 'A')
insert into t values(1, 'B')
insert into t values(2, 'A')
insert into t values(2, 'A')
select t.n from t
where t.C = 'A' or t.C = 'B'
group by t.n
having count(t.n) =2

n
-----------
1
2
drop table t

note that 2 was retrieved - it should not be selected at all ;)

[Erland Sommarskog]

Re: SQL query

frank m via SQLMonster.com (forum@SQLMonst er.com) writes:[color=blue]
> 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:
> ID CAT
> 1 A
> 1 B
> 2 A
> 3 A
> 3 B
> I now want to extract all ids that have the CAT value 'A' and 'B' .
> In this example the result would be 1,3.[/color]

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

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[frank m via SQLMonster.com]

Re: SQL query

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:[color=blue][color=green]
>> 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:[/color]
>[quoted text clipped - 6 lines][color=green]
>> I now want to extract all ids that have the CAT value 'A' and 'B' .
>> In this example the result would be 1,3.[/color]
>
>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')
>[/color]


--
Message posted via SQLMonster.com

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

[frank m via SQLMonster.com]

Re: SQL query

Thanks for your reply. Would this not cause problems with large tables and
more than 2 c values? My table could contain a million records with up to 10
c values!

AK wrote:[color=blue]
>create table t(n int, c char(2))
>insert into t values(1, 'A')
>insert into t values(1, 'B')
>insert into t values(2, 'A')
>insert into t values(3, 'A')
>insert into t values(3, 'B')
>select a.n from t a, t b
>where a.n = b.n
>and a.c='A' and b.c='B'
>
>n
>-----------
>1
>3
>
>drop table t[/color]


--
Message posted via SQLMonster.com

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

[Erland Sommarskog]

Re: SQL query

frank m via SQLMonster.com (forum@SQLMonst er.com) writes:[color=blue]
> 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.[/color]

I believe they are equivalent. The optimizer rewrites the IN thing to
EXISTS internally.

The advantage with EXISTS is that it works syntactically even if you would
have a two-column id.

Assuming that there several other columns in the table, the most important
for good performance here is to have a non-clustered index on (id, cat) or
(cat, id). (I was trying to get a grip of which order of the column that
would be best, but arrived at "it depends" kind of answer.) With a covering
index on the query, SQL Server can compute the query from the index alone,
which is more effecient, since the index is smaller than the table.

You can also try the query posted by AK:
[color=blue]
>select a.n from t a, t b
>where a.n = b.n
>and a.c='A' and b.c='B'[/color]

And this:

SELECT id
FROM (SELECT DISTICNT id, cat FROM tbl WHERE cat IN ('A', 'B')) AS x
GROUP BY id
HAVING COUNT(*) > 1

(Which I have not tested)

Which of these that is the most effecient, I don't dare to say. But for
all the non-clustered index I mentioned has a major impact for performance.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[--CELKO--]

Re: SQL query

Look up the term "Relational Division"; it is one of Codd's original
eight operators.