From oracle-l-bounce@freelists.org Mon Oct 10 11:09:48 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j9AG9lro027341 for ; Mon, 10 Oct 2005 11:09:47 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j9AG9evX027323 for ; Mon, 10 Oct 2005 11:09:42 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2DED21FA4BD; Mon, 10 Oct 2005 11:09:34 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 03311-02; Mon, 10 Oct 2005 11:09:34 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A1ECB1FA42D; Mon, 10 Oct 2005 11:09:33 -0500 (EST) X-ME-UUID: 20051010160734445.0AD2C1C00192@mwinf0206.wanadoo.fr Subject: Re: SQL help From: Stephane Faroult To: dubey.sandeep@gmail.com Cc: oracle-l@freelists.org In-Reply-To: References: Content-Type: text/plain Organization: RoughSea Ltd Date: Mon, 10 Oct 2005 18:08:29 +0200 Message-Id: <1128960509.4906.12.camel@frlinux2.roughsea.com> Mime-Version: 1.0 X-archive-position: 26641 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: sfaroult@roughsea.com Precedence: normal Reply-To: sfaroult@roughsea.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Level: X-Spam-Status: No, hits=-3.6 required=5.0 tests=AWL,BAYES_00 autolearn=ham version=2.63 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