Re: IN clause but needs to be 'AND'

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)
)  

--
Billy