Re: SQL help
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: dubey.sandeep@xxxxxxxxx
- Date: Mon, 10 Oct 2005 18:08:29 +0200
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
> --
> //www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
