Re: IN clause but needs to be 'AND'

Jeremy Taylor wrote:

> er.... not quite (I think)

<snipped>

So I need more coffee.. :-)

> We need to get all the routes that use a particular stretch of
> segments.

Ah.. <little light flickers and goes on>

> any more ideas?? anybody??

Yeah, here goes. A bit inflexible as you need to manually enter the number of segments in the HAVING clause. But it seems to work.

SQL> select
  2 *
  3 from route;

  ROUTE_ID SEGMENT_ID
---------- ----------

         1        100
         1        200
         1        300
         1        400
         1        500
         1        600
         2        200
         2        300
         2        400
         2        700
         2        800
         3        200
         3        400
         3        800
         3        900

15 rows selected.

SQL> SELECT
  2 route_id
  3 FROM route
  4 WHERE segment_id IN (200,300,400) /* 3 segments */   5 GROUP BY route_ID
  6* HAVING count(*) = 3 /* containing all 3 segments*/ SQL> /   ROUTE_ID

         1
         2

--
Billy