Re: IN clause but needs to be 'AND'

Hi,

You can do the same:

select route_id
from route_segments
group by route_id
having count(distinct segment_id) in (select count(distinct segment_id) from route_segments);

I hope it's useful to you.

Regards,

                                                Isa

"Jeremy Taylor" <Jemtaylor_at_yahoo.com> escribió en el mensaje news:64827d68.0302040303.7de247ed_at_posting.google.com...
> 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.
>
> 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
Received on Tue Feb 04 2003 - 11:24:35 CST