Re: IN clause but needs to be 'AND'

Billy Verreynne <vslabs_at_onwe.co.za> wrote in message news:<b1o7h8$kgn$1_at_ctb-nnrp2.saix.net>...
> Jeremy Taylor wrote:
>
> > 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)
>
>
> Unless I'm missing something (like my brain and/or more caffeine), you can
> do this as follows:
>
> /* get all segments for the list of specified routes */
> SELECT
> DISTINCT segment_id
> FROM route_segment
> WHERE route_id IN
> (
> /* get a distinct list of routes using the specified segments */
> SELECT
> DISTINCT route_id
> FROM route_segments
> WHERE segment_id IN (100,200,300,400)
> )

er.... not quite (I think)

We need to get all the routes that use a particular stretch of segments. For instance, if I have the routes:

route 1:
100, 200, 300, 400, 500, 600

route 2:
200, 300, 400, 700, 800

route 3:
200, 400, 800, 900

then I would like to be able to specify a list of segments - e.g. 200, 300, 400 and get back a list of routes that use these. In this case routes 1 and 2.

Your solution seems to get me all the segments in all the routes that use any of the the list of segments given. Not quite what I want.

Thanks anyway

any more ideas?? anybody??

Jeremy Received on Tue Feb 04 2003 - 11:41:09 CST