Re: IN clause but needs to be 'AND'

Billy Verreynne <vslabs_at_onwe.co.za> wrote in message news:<b1q76k$p2l$1_at_ctb-nnrp2.saix.net>...
> 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

Bingo.

'Manually' entering the number is not a problem - these queries are generated by a bit of code. All it has to do is plug in the size of the list of segments it has been passed.

Other people have suggested 'count(distinct(segment_id))' which gets around the problem of routes that pass over the same segment twice. I think.

Thanks.

Jeremy Received on Wed Feb 05 2003 - 09:27:04 CST