microsoft.public.sqlserver.server - Is there a way to get the intersection of data from a single table access?

Author

Is there a way to get the intersection of data from a single table access?

John Peterson

2002-08-02, 10:25 am

(SQL Server 2000, SP2)

Hello, all!

I have a lengthy and complex query (actually in Oracle, but I find this
group to be much more responsive) that I was looking for help with.

The query is structured like

select *
from MyTable mt
where mt.Id = <value>
and exists (select * from OtherTable ot where ot.Id = mt.Id and (<other
criteria 1> ))
and exists (select * from OtherTable ot where ot.Id = mt.Id and (<other
criteria 2> ))
and exists (select * from OtherTable ot where ot.Id = mt.Id and (<other
criteria 3> ))

I had thought to restructure the query to something like:

select *
from MyTable mt
where mt.Id = <value>
and exists (
select *
from OtherTable ot
where ot.Id = mt.Id
and (
(<other criteria 1> )
or (<other criteria 2> )
or (<other criteria 3> )
)
)

However, this will give me the union of the Ids, and I want the intersection
of the Ids. I was wondering if there was some way to restructure the table
to provide the intersection of Ids and only access the OtherTable one time?
I don't think so, but I've been surprised at the various creative answers in
here in the past! :-)

Regards,

John Peterson

Anith Sen

2002-08-02, 10:25 am

Use NOT EXISTS

SELECT *
FROM MyTable mt
WHERE mt.Id = <value>
AND NOT EXISTS (
SELECT *
FROM OtherTable ot
WHERE ot.Id = mt.Id
AND (<other criteria 1> )
AND (<other criteria 2> )
AND (<other criteria 3> )
)

If this does not give you what you want, try posting your
table DDLs (CREATE TABLE statemenets) with some sample data
(preferably as INSERT statements) and someone here can help you..

--
- Anith

Cristian

2002-08-02, 10:25 am

select mt.*
from MyTable mt
join OtherTable ot on mt.Id = ot.Id
where <other criteria 1>
and <other criteria 2>
and <other criteria 3>

- Cristian

"John Peterson" <johnp@azstarnet.com> wrote in message
news:ugS5xQjOCHA.2372@tkmsftngp10...
> (SQL Server 2000, SP2)
>
> Hello, all!
>
> I have a lengthy and complex query (actually in Oracle, but I find this
> group to be much more responsive) that I was looking for help with.
>
> The query is structured like
>
> select *
> from MyTable mt
> where mt.Id = <value>
> and exists (select * from OtherTable ot where ot.Id = mt.Id and (<other
> criteria 1> ))
> and exists (select * from OtherTable ot where ot.Id = mt.Id and (<other
> criteria 2> ))
> and exists (select * from OtherTable ot where ot.Id = mt.Id and (<other
> criteria 3> ))
>
> I had thought to restructure the query to something like:
>
> select *
> from MyTable mt
> where mt.Id = <value>
> and exists (
> select *
> from OtherTable ot
> where ot.Id = mt.Id
> and (
> (<other criteria 1> )
> or (<other criteria 2> )
> or (<other criteria 3> )
> )
> )
>
> However, this will give me the union of the Ids, and I want the
intersection
> of the Ids. I was wondering if there was some way to restructure the
table
> to provide the intersection of Ids and only access the OtherTable one
time?
> I don't think so, but I've been surprised at the various creative answers
in
> here in the past! :-)
>
> Regards,
>
> John Peterson
>
>
>

John Peterson

2002-08-02, 10:25 am

I should have been more clear in that my <other criteria N> references the
same fields (and thus mutually exclusive?). For example:

select *
from MyTable mt
where mt.Id = <value>
and exists (select * from OtherTable ot where ot.Id = mt.Id
and (ot.Type = 10 and ot.Data = 50)
and exists (select * from OtherTable ot where ot.Id = mt.Id
and (ot.Type = 20 and ot.Data between 10 and 100)
and exists (select * from OtherTable ot where ot.Id = mt.Id
and (ot.Type < 50 and ot.Data in (10, 15, 30))

Anith, Cristian (and others), does this change your suggested solutions?

Thanks for your help! :-)

"John Peterson" <johnp@azstarnet.com> wrote in message
news:ugS5xQjOCHA.2372@tkmsftngp10...
> (SQL Server 2000, SP2)
>
> Hello, all!
>
> I have a lengthy and complex query (actually in Oracle, but I find this
> group to be much more responsive) that I was looking for help with.
>
> The query is structured like
>
> select *
> from MyTable mt
> where mt.Id = <value>
> and exists (select * from OtherTable ot where ot.Id = mt.Id and (<other
> criteria 1> ))
> and exists (select * from OtherTable ot where ot.Id = mt.Id and (<other
> criteria 2> ))
> and exists (select * from OtherTable ot where ot.Id = mt.Id and (<other
> criteria 3> ))
>
> I had thought to restructure the query to something like:
>
> select *
> from MyTable mt
> where mt.Id = <value>
> and exists (
> select *
> from OtherTable ot
> where ot.Id = mt.Id
> and (
> (<other criteria 1> )
> or (<other criteria 2> )
> or (<other criteria 3> )
> )
> )
>
> However, this will give me the union of the Ids, and I want the
intersection
> of the Ids. I was wondering if there was some way to restructure the
table
> to provide the intersection of Ids and only access the OtherTable one
time?
> I don't think so, but I've been surprised at the various creative answers
in
> here in the past! :-)
>
> Regards,
>
> John Peterson
>
>
>

Cristian

2002-08-02, 10:25 am

select mt.*
from MyTable mt
join OtherTable ot on mt.Id = ot.Id
where
(ot.Type = 10 and ot.Data = 50) or
(ot.Type = 20 and ot.Data between 10 and 100) or
(ot.Type < 50 and ot.Data in (10, 15, 30))

- Cristian

"John Peterson" <johnp@azstarnet.com> wrote in message
news:ugS5xQjOCHA.2372@tkmsftngp10...
> (SQL Server 2000, SP2)
>
> Hello, all!
>
> I have a lengthy and complex query (actually in Oracle, but I find this
> group to be much more responsive) that I was looking for help with.
>
> The query is structured like
>
> select *
> from MyTable mt
> where mt.Id = <value>
> and exists (select * from OtherTable ot where ot.Id = mt.Id and (<other
> criteria 1> ))
> and exists (select * from OtherTable ot where ot.Id = mt.Id and (<other
> criteria 2> ))
> and exists (select * from OtherTable ot where ot.Id = mt.Id and (<other
> criteria 3> ))
>
> I had thought to restructure the query to something like:
>
> select *
> from MyTable mt
> where mt.Id = <value>
> and exists (
> select *
> from OtherTable ot
> where ot.Id = mt.Id
> and (
> (<other criteria 1> )
> or (<other criteria 2> )
> or (<other criteria 3> )
> )
> )
>
> However, this will give me the union of the Ids, and I want the
intersection
> of the Ids. I was wondering if there was some way to restructure the
table
> to provide the intersection of Ids and only access the OtherTable one
time?
> I don't think so, but I've been surprised at the various creative answers
in
> here in the past! :-)
>
> Regards,
>
> John Peterson
>
>
>

John Peterson

2002-08-02, 12:25 pm

Hello, Cristian!

I *think* that this will result in the unions of Ids, and I really want the
*intersection* of Ids. I'm working on some DDL to help whip up a sample
data set. :-)

"Cristian" <Cristian.Lefter@scala.ro> wrote in message
news:#hxFVkjOCHA.1032@tkmsftngp12...
> select mt.*
> from MyTable mt
> join OtherTable ot on mt.Id = ot.Id
> where
> (ot.Type = 10 and ot.Data = 50) or
> (ot.Type = 20 and ot.Data between 10 and 100) or
> (ot.Type < 50 and ot.Data in (10, 15, 30))
>
> - Cristian
>
>
> "John Peterson" <johnp@azstarnet.com> wrote in message
> news:ugS5xQjOCHA.2372@tkmsftngp10...
> > (SQL Server 2000, SP2)
> >
> > Hello, all!
> >
> > I have a lengthy and complex query (actually in Oracle, but I find this
> > group to be much more responsive) that I was looking for help with.
> >
> > The query is structured like
> >
> > select *
> > from MyTable mt
> > where mt.Id = <value>
> > and exists (select * from OtherTable ot where ot.Id = mt.Id and
(< other

> > criteria 1> ))
> > and exists (select * from OtherTable ot where ot.Id = mt.Id and
(< other

> > criteria 2> ))
> > and exists (select * from OtherTable ot where ot.Id = mt.Id and
(< other

> > criteria 3> ))
> >
> > I had thought to restructure the query to something like:
> >
> > select *
> > from MyTable mt
> > where mt.Id = <value>
> > and exists (
> > select *
> > from OtherTable ot
> > where ot.Id = mt.Id
> > and (
> > (<other criteria 1> )
> > or (<other criteria 2> )
> > or (<other criteria 3> )
> > )
> > )
> >
> > However, this will give me the union of the Ids, and I want the
> intersection
> > of the Ids. I was wondering if there was some way to restructure the
> table
> > to provide the intersection of Ids and only access the OtherTable one
> time?
> > I don't think so, but I've been surprised at the various creative
answers
> in
> > here in the past! :-)
> >
> > Regards,
> >
> > John Peterson
> >
> >
> >
>
>

John Peterson

2002-08-02, 2:25 pm

Hello, Cristian (and others)!

Okay, here's some DDL to illustrate the data set that I'm working with:

use tempdb
go

create table MyTable (Id int)
go

insert into MyTable values (1)
insert into MyTable values (2)
insert into MyTable values (3)
insert into MyTable values (4)
go

create table OtherTable (Id int, Type int, Data int)
go

insert into OtherTable values (1, 10, 50)
insert into OtherTable values (1, 20, 99)
insert into OtherTable values (1, 30, 15)
insert into OtherTable values (2, 60, 100)
insert into OtherTable values (3, 10, 50)
insert into OtherTable values (3, 20, 50)
insert into OtherTable values (4, 40, 10)
go

My original statement might look like:

select Id
from MyTable mt
where exists (select * from OtherTable ot where ot.Id = mt.Id
and (ot.Type = 10 and ot.Data = 50))
and exists (select * from OtherTable ot where ot.Id = mt.Id
and (ot.Type = 20 and ot.Data between 10 and 100))
and exists (select * from OtherTable ot where ot.Id = mt.Id
and (ot.Type < 50 and ot.Data in (10, 15, 30)))

But, I was hoping to be able to construct this such that OtherTable is only
used once (if possible). That is, I'm interested in the *intersection* of
Ids as defined by the various "filters".

Any help would be much appreciated!

John Peterson

"John Peterson" <johnp@azstarnet.com> wrote in message
news:ODOmt3kOCHA.1776@tkmsftngp10...
> Hello, Cristian!
>
> I *think* that this will result in the unions of Ids, and I really want
the
> *intersection* of Ids. I'm working on some DDL to help whip up a sample
> data set. :-)
>
>
> "Cristian" <Cristian.Lefter@scala.ro> wrote in message
> news:#hxFVkjOCHA.1032@tkmsftngp12...
> > select mt.*
> > from MyTable mt
> > join OtherTable ot on mt.Id = ot.Id
> > where
> > (ot.Type = 10 and ot.Data = 50) or
> > (ot.Type = 20 and ot.Data between 10 and 100) or
> > (ot.Type < 50 and ot.Data in (10, 15, 30))
> >
> > - Cristian
> >
> >
> > "John Peterson" <johnp@azstarnet.com> wrote in message
> > news:ugS5xQjOCHA.2372@tkmsftngp10...
> > > (SQL Server 2000, SP2)
> > >
> > > Hello, all!
> > >
> > > I have a lengthy and complex query (actually in Oracle, but I find
this[
color=darkred]
> > > group to be much more responsive) that I was looking for help with.
> > >
> > > The query is structured like
> > >
> > > select *
> > > from MyTable mt
> > > where mt.Id = <value>
> > > and exists (select * from OtherTable ot where ot.Id = mt.Id and
> (< other
> > > criteria 1> ))
> > > and exists (select * from OtherTable ot where ot.Id = mt.Id and
> (< other
> > > criteria 2> ))
> > > and exists (select * from OtherTable ot where ot.Id = mt.Id and
> (< other
> > > criteria 3> ))
> > >
> > > I had thought to restructure the query to something like:
> > >
> > > select *
> > > from MyTable mt
> > > where mt.Id = <value>
> > > and exists (
> > > select *
> > > from OtherTable ot
> > > where ot.Id = mt.Id
> > > and (
> > > (<other criteria 1> )
> > > or (<other criteria 2> )
> > > or (<other criteria 3> )
> > > )
> > > )
> > >
> > > However, this will give me the union of the Ids, and I want the
> > intersection
> > > of the Ids. I was wondering if there was some way to restructure the
> > table
> > > to provide the intersection of Ids and only access the OtherTable one
> > time?
> > > I don't think so, but I've been surprised at the various creative
> answers
> > in
> > > here in the past! :-)
> > >
> > > Regards,
> > >
> > > John Peterson
> > >
> > >
> > >
> >
> >
>
>[/color]

BP Margolin

2002-08-02, 7:25 pm

John,

select ot.Id
from OtherTable as ot
where (ot.Type = 10 and ot.Data = 50)
or (ot.Type = 20 and ot.Data between 10 and 100)
or (ot.Type < 50 and ot.Data in (10, 15, 30))
group by ot.Id
having count(*) = 3

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"John Peterson" <johnp@azstarnet.com> wrote in message
news:#VZEv6lOCHA.2472@tkmsftngp11...
> Hello, Cristian (and others)!
>
> Okay, here's some DDL to illustrate the data set that I'm working with:
>
>
> use tempdb
> go
>
> create table MyTable (Id int)
> go
>
> insert into MyTable values (1)
> insert into MyTable values (2)
> insert into MyTable values (3)
> insert into MyTable values (4)
> go
>
> create table OtherTable (Id int, Type int, Data int)
> go
>
> insert into OtherTable values (1, 10, 50)
> insert into OtherTable values (1, 20, 99)
> insert into OtherTable values (1, 30, 15)
> insert into OtherTable values (2, 60, 100)
> insert into OtherTable values (3, 10, 50)
> insert into OtherTable values (3, 20, 50)
> insert into OtherTable values (4, 40, 10)
> go
>
>
> My original statement might look like:
>
>
> select Id
> from MyTable mt
> where exists (select * from OtherTable ot where ot.Id = mt.Id
> and (ot.Type = 10 and ot.Data = 50))
> and exists (select * from OtherTable ot where ot.Id = mt.Id
> and (ot.Type = 20 and ot.Data between 10 and 100))
> and exists (select * from OtherTable ot where ot.Id = mt.Id
> and (ot.Type < 50 and ot.Data in (10, 15, 30)))
>
>
> But, I was hoping to be able to construct this such that OtherTable is
only
> used once (if possible). That is, I'm interested in the *intersection* of
> Ids as defined by the various "filters".
>
> Any help would be much appreciated!
>
> John Peterson
>
>
>
>
> "John Peterson" <johnp@azstarnet.com> wrote in message
> news:ODOmt3kOCHA.1776@tkmsftngp10...
> > Hello, Cristian!
> >
> > I *think* that this will result in the unions of Ids, and I really want
> the
> > *intersection* of Ids. I'm working on some DDL to help whip up a sample
> > data set. :-)
> >
> >
> > "Cristian" <Cristian.Lefter@scala.ro> wrote in message
> > news:#hxFVkjOCHA.1032@tkmsftngp12...
> > > select mt.*
> > > from MyTable mt
> > > join OtherTable ot on mt.Id = ot.Id
> > > where
> > > (ot.Type = 10 and ot.Data = 50) or
> > > (ot.Type = 20 and ot.Data between 10 and 100) or
> > > (ot.Type < 50 and ot.Data in (10, 15, 30))
> > >
> > > - Cristian
> > >
> > >
> > > "John Peterson" <johnp@azstarnet.com> wrote in message
> > > news:ugS5xQjOCHA.2372@tkmsftngp10...
> > > > (SQL Server 2000, SP2)
> > > >
> > > > Hello, all!
> > > >
> > > > I have a lengthy and complex query (actually in Oracle, but I find
> this
> > > > group to be much more responsive) that I was looking for help with.
> > > >
> > > > The query is structured like
> > > >
> > > > select *
> > > > from MyTable mt
> > > > where mt.Id = <value>
> > > > and exists (select * from OtherTable ot where ot.Id = mt.Id and
> > (<other
> > > > criteria 1> ))
> > > > and exists (select * from OtherTable ot where ot.Id = mt.Id and
> > (<other
> > > > criteria 2> ))
> > > > and exists (select * from OtherTable ot where ot.Id = mt.Id and
> > (<other
> > > > criteria 3> ))
> > > >
> > > > I had thought to restructure the query to something like:
> > > >
> > > > select *
> > > > from MyTable mt
> > > > where mt.Id = <value>
> > > > and exists (
> > > > select *
> > > > from OtherTable ot
> > > > where ot.Id = mt.Id
> > > > and (
> > > > (<other criteria 1> )
> > > > or (<other criteria 2> )
> > > > or (<other criteria 3> )
> > > > )
> > > > )
> > > >
> > > > However, this will give me the union of the Ids, and I want the
> > > intersection
> > > > of the Ids. I was wondering if there was some way to restructure
the[c
olor=darkred]
> > > table
> > > > to provide the intersection of Ids and only access the OtherTable[/color]
one[c
olor=darkred]
> > > time?
> > > > I don't think so, but I've been surprised at the various creative
> > answers
> > > in
> > > > here in the past! :-)
> > > >
> > > > Regards,
> > > >
> > > > John Peterson
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>[/color]

John Peterson

2002-08-02, 8:25 pm

Hello, BP!

Thanks for the clever approach! This reminds me of something you helped me
with a *long* time ago.

I tried this technique, and it seems to net me about a 10-15% gain. (I
wonder if there's some threshold where the more OR clauses I add, the worse
it gets in relation to the original.)

I've got a query that runs in about 3.25 minutes and this shaved it down to
about 2.75. I was hoping that by virtue of using the OtherTable *once* (it
has about 5 million records), it might speed it up dramatically. No such
luck. :-(

Thanks again! :-)

"BP Margolin" <bpmargo@attglobal.net> wrote in message
news:OYcQgkoOCHA.2048@tkmsftngp08...
> John,
>
> select ot.Id
> from OtherTable as ot
> where (ot.Type = 10 and ot.Data = 50)
> or (ot.Type = 20 and ot.Data between 10 and 100)
> or (ot.Type < 50 and ot.Data in (10, 15, 30))
> group by ot.Id
> having count(*) = 3
>
> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.
>
> "John Peterson" <johnp@azstarnet.com> wrote in message
> news:#VZEv6lOCHA.2472@tkmsftngp11...
> > Hello, Cristian (and others)!
> >
> > Okay, here's some DDL to illustrate the data set that I'm working with:
> >
> >
> > use tempdb
> > go
> >
> > create table MyTable (Id int)
> > go
> >
> > insert into MyTable values (1)
> > insert into MyTable values (2)
> > insert into MyTable values (3)
> > insert into MyTable values (4)
> > go
> >
> > create table OtherTable (Id int, Type int, Data int)
> > go
> >
> > insert into OtherTable values (1, 10, 50)
> > insert into OtherTable values (1, 20, 99)
> > insert into OtherTable values (1, 30, 15)
> > insert into OtherTable values (2, 60, 100)
> > insert into OtherTable values (3, 10, 50)
> > insert into OtherTable values (3, 20, 50)
> > insert into OtherTable values (4, 40, 10)
> > go
> >
> >
> > My original statement might look like:
> >
> >
> > select Id
> > from MyTable mt
> > where exists (select * from OtherTable ot where ot.Id = mt.Id
> > and (ot.Type = 10 and ot.Data = 50))
> > and exists (select * from OtherTable ot where ot.Id = mt.Id
> > and (ot.Type = 20 and ot.Data between 10 and 100))
> > and exists (select * from OtherTable ot where ot.Id = mt.Id
> > and (ot.Type < 50 and ot.Data in (10, 15, 30)))
> >
> >
> > But, I was hoping to be able to construct this such that OtherTable is
> only
> > used once (if possible). That is, I'm interested in the *intersection*
of
> > Ids as defined by the various "filters".
> >
> > Any help would be much appreciated!
> >
> > John Peterson
> >
> >
> >
> >
> > "John Peterson" <johnp@azstarnet.com> wrote in message
> > news:ODOmt3kOCHA.1776@tkmsftngp10...
> > > Hello, Cristian!
> > >
> > > I *think* that this will result in the unions of Ids, and I really
want
> > the
> > > *intersection* of Ids. I'm working on some DDL to help whip up a
sample
> > > data set. :-)
> > >
> > >
> > > "Cristian" <Cristian.Lefter@scala.ro> wrote in message
> > > news:#hxFVkjOCHA.1032@tkmsftngp12...
> > > > select mt.*
> > > > from MyTable mt
> > > > join OtherTable ot on mt.Id = ot.Id
> > > > where
> > > > (ot.Type = 10 and ot.Data = 50) or
> > > > (ot.Type = 20 and ot.Data between 10 and 100) or
> > > > (ot.Type < 50 and ot.Data in (10, 15, 30))
> > > >
> > > > - Cristian
> > > >
> > > >
> > > > "John Peterson" <johnp@azstarnet.com> wrote in message
> > > > news:ugS5xQjOCHA.2372@tkmsftngp10...
> > > > > (SQL Server 2000, SP2)
> > > > >
> > > > > Hello, all!
> > > > >
> > > > > I have a lengthy and complex query (actually in Oracle, but I find
> > this
> > > > > group to be much more responsive) that I was looking for help
with. [colo
r=darkred]
> > > > >
> > > > > The query is structured like
> > > > >
> > > > > select *
> > > > > from MyTable mt
> > > > > where mt.Id = <value>
> > > > > and exists (select * from OtherTable ot where ot.Id = mt.Id and
> > > (<other
> > > > > criteria 1> ))
> > > > > and exists (select * from OtherTable ot where ot.Id = mt.Id and
> > > (<other
> > > > > criteria 2> ))
> > > > > and exists (select * from OtherTable ot where ot.Id = mt.Id and
> > > (<other
> > > > > criteria 3> ))
> > > > >
> > > > > I had thought to restructure the query to something like:
> > > > >
> > > > > select *
> > > > > from MyTable mt
> > > > > where mt.Id = <value>
> > > > > and exists (
> > > > > select *
> > > > > from OtherTable ot
> > > > > where ot.Id = mt.Id
> > > > > and (
> > > > > (<other criteria 1> )
> > > > > or (<other criteria 2> )
> > > > > or (<other criteria 3> )
> > > > > )
> > > > > )
> > > > >
> > > > > However, this will give me the union of the Ids, and I want the
> > > > intersection
> > > > > of the Ids. I was wondering if there was some way to restructure
> the
> > > > table
> > > > > to provide the intersection of Ids and only access the OtherTable
> one
> > > > time?
> > > > > I don't think so, but I've been surprised at the various creative
> > > answers
> > > > in
> > > > > here in the past! :-)
> > > > >
> > > > > Regards,
> > > > >
> > > > > John Peterson
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>[/color]

BP Margolin

2002-08-03, 12:25 pm

John,

Depending upon the indexes you have on the table, perhaps something like:

select ot.Id
from OtherTable as ot
where (ot.Type < 50) and
((ot.Type = 10 and ot.Data = 50) or
(ot.Type = 20 and ot.Data between 10 and 100) or
(ot.Type < 50 and ot.Data in (10, 15, 30)))
group by ot.Id
having count(*) = 3

or

select ot.Id
from OtherTable as ot
where (ot.Data between 10 and 100) and
((ot.Type = 10 and ot.Data = 50) or
(ot.Type = 20 and ot.Data between 10 and 100) or
(ot.Type < 50 and ot.Data in (10, 15, 30)))
group by ot.Id
having count(*) = 3

Perhaps you can play with the concept of coding "redundant" information into
the WHERE clause so that SQL Server can utilize an index.

BPM

"John Peterson" <johnp@azstarnet.com> wrote in message
news:ey5N7LpOCHA.436@tkmsftngp11...
> Hello, BP!
>
> Thanks for the clever approach! This reminds me of something you helped
me
> with a *long* time ago.
>
> I tried this technique, and it seems to net me about a 10-15% gain. (I
> wonder if there's some threshold where the more OR clauses I add, the
worse
> it gets in relation to the original.)
>
> I've got a query that runs in about 3.25 minutes and this shaved it down
to
> about 2.75. I was hoping that by virtue of using the OtherTable *once*
(it
> has about 5 million records), it might speed it up dramatically. No such
> luck. :-(
>
> Thanks again! :-)
>
>
> "BP Margolin" <bpmargo@attglobal.net> wrote in message
> news:OYcQgkoOCHA.2048@tkmsftngp08...
> > John,
> >
> > select ot.Id
> > from OtherTable as ot
> > where (ot.Type = 10 and ot.Data = 50)
> > or (ot.Type = 20 and ot.Data between 10 and 100)
> > or (ot.Type < 50 and ot.Data in (10, 15, 30))
> > group by ot.Id
> > having count(*) = 3
> >
> > -------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which

> > can be cut and pasted into Query Analyzer is appreciated.
> >
> > "John Peterson" <johnp@azstarnet.com> wrote in message
> > news:#VZEv6lOCHA.2472@tkmsftngp11...
> > > Hello, Cristian (and others)!
> > >
> > > Okay, here's some DDL to illustrate the data set that I'm working
with:

> > >
> > >
> > > use tempdb
> > > go
> > >
> > > create table MyTable (Id int)
> > > go
> > >
> > > insert into MyTable values (1)
> > > insert into MyTable values (2)
> > > insert into MyTable values (3)
> > > insert into MyTable values (4)
> > > go
> > >
> > > create table OtherTable (Id int, Type int, Data int)
> > > go
> > >
> > > insert into OtherTable values (1, 10, 50)
> > > insert into OtherTable values (1, 20, 99)
> > > insert into OtherTable values (1, 30, 15)
> > > insert into OtherTable values (2, 60, 100)
> > > insert into OtherTable values (3, 10, 50)
> > > insert into OtherTable values (3, 20, 50)
> > > insert into OtherTable values (4, 40, 10)
> > > go
> > >
> > >
> > > My original statement might look like:
> > >
> > >
> > > select Id
> > > from MyTable mt
> > > where exists (select * from OtherTable ot where ot.Id = mt.Id
> > > and (ot.Type = 10 and ot.Data = 50))
> > > and exists (select * from OtherTable ot where ot.Id = mt.Id
> > > and (ot.Type = 20 and ot.Data between 10 and 100))
> > > and exists (select * from OtherTable ot where ot.Id = mt.Id
> > > and (ot.Type < 50 and ot.Data in (10, 15, 30)))
> > >
> > >
> > > But, I was hoping to be able to construct this such that OtherTable is
> > only
> > > used once (if possible). That is, I'm interested in the
*intersection*
> of

> > > Ids as defined by the various "filters".
> > >
> > > Any help would be much appreciated!
> > >
> > > John Peterson
> > >
> > >
> > >
> > >
> > > "John Peterson" <johnp@azstarnet.com> wrote in message
> > > news:ODOmt3kOCHA.1776@tkmsftngp10...
> > > > Hello, Cristian!
> > > >
> > > > I *think* that this will result in the unions of Ids, and I really
> want
> > > the
> > > > *intersection* of Ids. I'm working on some DDL to help whip up a
> sample
> > > > data set. :-)
> > > >
> > > >
> > > > "Cristian" <Cristian.Lefter@scala.ro> wrote in message
> > > > news:#hxFVkjOCHA.1032@tkmsftngp12...
> > > > > select mt.*
> > > > > from MyTable mt
> > > > > join OtherTable ot on mt.Id = ot.Id
> > > > > where
> > > > > (ot.Type = 10 and ot.Data = 50) or
> > > > > (ot.Type = 20 and ot.Data between 10 and 100) or
> > > > > (ot.Type < 50 and ot.Data in (10, 15, 30))
> > > > >
> > > > > - Cristian
> > > > >
> > > > >
> > > > > "John Peterson" <johnp@azstarnet.com> wrote in message
> > > > > news:ugS5xQjOCHA.2372@tkmsftngp10...
> > > > > > (SQL Server 2000, SP2)
> > > > > >
> > > > > > Hello, all!
> > > > > >
> > > > > > I have a lengthy and complex query (actually in Oracle, but I
find[
color=darkred]
> > > this
> > > > > > group to be much more responsive) that I was looking for help
> with.
> > > > > >
> > > > > > The query is structured like
> > > > > >
> > > > > > select *
> > > > > > from MyTable mt
> > > > > > where mt.Id = <value>
> > > > > > and exists (select * from OtherTable ot where ot.Id = mt.Id[/color]
and[c
olor=darkred]
> > > > (<other
> > > > > > criteria 1> ))
> > > > > > and exists (select * from OtherTable ot where ot.Id = mt.Id[/color]
and[c
olor=darkred]
> > > > (<other
> > > > > > criteria 2> ))
> > > > > > and exists (select * from OtherTable ot where ot.Id = mt.Id[/color]
and[c
olor=darkred]
> > > > (<other
> > > > > > criteria 3> ))
> > > > > >
> > > > > > I had thought to restructure the query to something like:
> > > > > >
> > > > > > select *
> > > > > > from MyTable mt
> > > > > > where mt.Id = <value>
> > > > > > and exists (
> > > > > > select *
> > > > > > from OtherTable ot
> > > > > > where ot.Id = mt.Id
> > > > > > and (
> > > > > > (<other criteria 1> )
> > > > > > or (<other criteria 2> )
> > > > > > or (<other criteria 3> )
> > > > > > )
> > > > > > )
> > > > > >
> > > > > > However, this will give me the union of the Ids, and I want the
> > > > > intersection
> > > > > > of the Ids. I was wondering if there was some way to[/color]
restructure
> > the
> > > > > table
> > > > > > to provide the intersection of Ids and only access the
OtherTable
> > one
> > > > > time?
> > > > > > I don't think so, but I've been surprised at the various
creative
> > > > answers
> > > > > in
> > > > > > here in the past! :-)
> > > > > >
> > > > > > Regards,
> > > > > >
> > > > > > John Peterson
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Sponsored Links