Re: SQL help

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Mon, 10 Oct 2005 18:08:29 +0200
Message-Id: <1128960509.4906.12.camel@frlinux2.roughsea.com>

Sandeep,

    in () is a kind of short-hand for "or". You must add something to your requirements, namely that the number of rows returned is 3. IMHO the most efficient way to do it is to use the analytical form of count() so as to return on each line the number of rows in the result set, and to add a condition on it, e.g.

select id
from (select id, count(id) over () rows_in_set

      from t
      where id in (1, 2, 3))

where rows_in_set = 3

SF

On Mon, 2005-10-10 at 11:53 -0400, Sandeep Dubey 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

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

Received on Mon Oct 10 2005 - 11:09:48 CDT