Re: IN clause but needs to be 'AND'

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

>But this is clumsy, slow and my list of segments can have 20 or more
>segment_id's in it. The query gets pretty sluggish!
>
>Any help would be greatly appreciated....
>
>Jeremy

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp