Re: How can I select an element that doesn't exists in a table?

From: jose_luis_fdez_diaz <gfiuni2_at_gmail.com>
Date: 13 Nov 2006 07:55:18 -0800
Message-ID: <1163433318.345503.265980@k70g2000cwa.googlegroups.com>

This is what I was looking for.

Thanks for your help.

Regards,
Jose Luis.

Charles Hooper wrote:
> jose_luis_fdez_diaz wrote:
> > Laurenz Albe wrote:
> > > jose_luis_fdez_diaz <gfiuni2_at_gmail.com> wrote:
> > > > Idem.
> > >
> > > 'Idem' is Latin for 'the same'.
> > >
> > > What do you want to tell us?
> > > Or, more to the point, what is your question?
> > >
> > > If it is the question in the subject, please clarify what you want.
> > >
> > > Are you looking for 'SELECT 42 FROM dual' ?
> > >
> > > Yours,
> > > Laurenz Albe
> > In the table below:
> >
> > table1
> > ---------
> > 1
> > 2
> > 4
> > 5
> >
> >
> > select 3 from table1 where not exists (select * from table1 where
> > row1!=3)
> >
> > returns:
> >
> > 3
> > 3
> > 3
> > 3
> >
> > I only want "one" 3.
> >
> > How can I get it?
> >
> > PD: Sorry for my little explanation in the last post.
> >
> >
> > Regards,
> > Jose Luis.
> >

>

> I suggest a different approach:
> Setting up the test case:
> CREATE TABLE TABLE1 (ROW1 NUMBER(22));
> INSERT INTO TABLE1 VALUES (1);
> INSERT INTO TABLE1 VALUES (2);
> INSERT INTO TABLE1 VALUES (4);
> INSERT INTO TABLE1 VALUES (5);

>

> SELECT
> COUNT(*) MATCH_ROWS
> FROM
> TABLE1
> WHERE
> ROW1=3;

>

> MATCH_ROWS
> ==========
> 0

>

> The above will give you a count of the rows where ROW1 is 3. A count
> of 0 implies that 3 is not included in the ROW1 column.

>

> The above does not return the result that you are looking for, which
> appears to be 3, if 3 does not appear in column ROW1. To obtain that
> result, the above query is modified:
> SELECT
> DECODE(COUNT(*),0,3,0) NOT_IN
> FROM
> TABLE1
> WHERE
> ROW1=3;

>


>     NOT_IN

> ==========

> 	 3

>

> If the COUNT is equal to 0, the value 3 is returned, otherwise the
> value 0 is returned.

>

> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
Received on Mon Nov 13 2006 - 09:55:18 CST