Re: IN clause but needs to be 'AND'

Thomas Kyte <tkyte_at_oracle.com> wrote in message news:<b1ovic02iqk_at_drn.newsguy.com>...
> In article <64827d68.0302040303.7de247ed_at_posting.google.com>,
> Jemtaylor_at_yahoo.com says...
> >
> >Hi,
> >
> >I have a seemingly simple problem wih an SQL statement I need to
> >write. But I can't figure it out...at least, not nicely.
> >
> >A 'route' is made up of a list of 'segments'. We have a table that
> >captures this called 'ROUTE_SEGMENTS'. It lists all the 'segments' in
> >a 'route' (it's a many-to many join table).
> >
> >We can easily find all the routes that use any one of a list of
> >segments by using 'IN':
> >
> >select route_id from route_segments where segment_id in
> >(100,200,300,400);
> >
> >but how do I find all the routes that use ALL of the segments in my
> >list? (in other words I want to find all the routes that pass over a
> >given list of segments)
> >
> >The only solution I've managed so far is:
> >
> >select route_id from route_segments a
> >where
> >exists (select * from route_segments b where b.segment_id=100
> > and b.route_id=a.route_id)
> >and
> >exists (select * from route_segments b where b.segment_id=200
> > and b.route_id=a.route_id)
> >and
> >exists ...... and so on with one 'exists' for each segment_id in my
> >list.
> >
>
> I would put the segments in a global temporary table, or using something like:
> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:110612348061
>
> use a collection type. Then you can:
>
> select route_id
> from route_segments
> where segment_id in ( THAT_SET )
> group by route_id
> having count(distinct segment_id) = (select count(*) from (THAT_SET) )
>
> like this:
>
> ops$tkyte_at_ORA817DEV> create global temporary table t1 ( x int ) on commit delete
> rows;
> Table created.
>
> ops$tkyte_at_ORA817DEV> create table t2 ( route_id int, segment_id int );
> Table created.
>
> ops$tkyte_at_ORA817DEV> insert into t2 select 1, rownum from all_users where rownum
> <= 10;
> 10 rows created.
>
> ops$tkyte_at_ORA817DEV> insert into t2 select 2, rownum*2 from all_users where
> rownum <= 10;
> 10 rows created.
>
> ops$tkyte_at_ORA817DEV> insert into t1 values ( 2 );
> ops$tkyte_at_ORA817DEV> insert into t1 values ( 4 );
> ops$tkyte_at_ORA817DEV> insert into t1 values ( 6 );
> ops$tkyte_at_ORA817DEV> insert into t1 values ( 7 );
>
>
> ops$tkyte_at_ORA817DEV> select route_id
> 2 from t2
> 3 where segment_id in ( select * from t1 )
> 4 group by route_id
> 5 having count(distinct segment_id) = (select count(*) from t1 )
> 6 /
>
> ROUTE_ID
> ----------
> 1
>

Now that looks like a solution - and in fact we had just come up with the following...

select route_id from route_segments
where segment_id in ( <the list of segments> ) group by route id
having count(distinct(segment_id)) = <n>

...where 'n' is the number of segments in the list.

Which is identical to yours except we don't use the temporary table. I was a little worried about routes that 'go in a loop' and use the same segment twice - but I think the 'distinct' will mean it'll be OK.

...and the best bit is that this runs very, very fast. I mean - really fast. The other query (where we just want to know which routes use any of the segments using a simple IN) is much slower. And yet this query has the 'IN' as well. How come this thing runs so quickly???

Perhaps I shouldn't ask. Just be happy that it does.

Thanks

Jeremy Received on Wed Feb 05 2003 - 03:48:26 CST