IN clause but needs to be 'AND'

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 - 05:03:33 CST