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

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 - 08:57:58 CST