Re: SQL help

If the values are contiguous then you can adopt this approach. Otherwise the exists option as suggested by another member on this list would be the way to go.

SQL9206>select * from t;

        ID

         1
         2
         3

3 rows selected.

SQL9206>select a.kount , b.range_diff
  2 from
  3 (select count(*) kount from t) a
  4 ,(select ( &end_value - &begin_value ) + 1 range_diff   5 from t) b
  6 where a.kount = b.range_diff;
Enter value for end_value: 3
Enter value for begin_value: 1
old 4: ,(select ( &end_value - &begin_value ) + 1 range_diff new 4: ,(select ( 3 - 1 ) + 1 range_diff

     KOUNT RANGE_DIFF
---------- ----------

         3          3
         3          3
         3          3

3 rows selected.

SQL9206>delete from t where id = 3;

1 row deleted.

SQL9206>select a.kount , b.range_diff
  2 from
  3 (select count(*) kount from t) a
  4 ,(select ( &end_value - &begin_value ) + 1 range_diff   5 from t) b
  6 where a.kount = b.range_diff;
Enter value for end_value: 3
Enter value for begin_value: 1
old 4: ,(select ( &end_value - &begin_value ) + 1 range_diff new 4: ,(select ( 3 - 1 ) + 1 range_diff

no rows selected

SQL9206> HTH
GovindanK

>
> On 10/10/05, Sandeep Dubey <dubey.sandeep_at_gmail.com> wrote:
> >
> > Hi,
> >
> > I am having weekend hangover with seemingly simple sql requirement.
> >
> > create table t(id number);
> > insert into t values(1);
> > insert into t values(2);
> > commit;
> >
> > I want to query this with an Id set. All values in the set should be
> > there to return me any row.
> > e.g.
> > select * from t where id in (1,2); return 1 and 2
> >
> > If am serching for 1,2,3 if any one value is missing I should not get any
> > data.
> > e.g.
> > select * from t where id in (1,2,3) should not return any row.
> > How to rewrite the above query with (1,2,3) that should not return me any
> > row.
> > Thanks
> >
> > Sandeep
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
>
>
>
> --
> ------------------------------
> select standard_disclaimer from company_requirements where category =
> 'MANDATORY';

--
http://www.freelists.org/webpage/oracle-l