Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to a better way to find a record in the table?

Re: How to a better way to find a record in the table?

From: Steven Franklin <steven.franklin_at_wcom.com>
Date: Fri, 06 Aug 1999 17:09:30 GMT
Message-ID: <37AB16C9.FE70FF3B@wcom.com>


Peter Wu wrote:

> Hi, I do not know if there is a better way to find record in a table.
> suppose I have a table:
>
> T: id number,
> code char(2)
>
> and the data is:
> id code
> 1 aa
> 1 bb
> 1 cc
> 1 dd
> 2 ff
> 2 qq
> 2 aa
> 2 cc
> 2 ee
> 3 mm
> 3 bb
> 3 nn
> 4 aa
> 4 hh
> 5 uu
>
> I am going to find which one has both 'aa' and 'cc'. The result should
> be 1 and 3.
>

I assume you mean 1 and 2 here.

> but I can not use the SQL code like:
>
> select id
> from T
> where code = 'aa'
> or code = 'cc'
>

SELECT id
FROM (SELECT id

               FROM T
               WHERE (code = 'aa')) aa,
              (SELECT id
               FROM T
               WHERE (code='cc')) cc

WHERE (aa.id = cc.id)

or

SELECT id
FROM T aa,

            T cc

WHERE (aa.id = cc.id) AND
              (aa.code = 'aa') AND
              (cc.code = 'cc')

p.s. The second one is probably faster but both should work.

> The result would be
> id
> 1
> 1
> 2
> 2
> 4
>
> can anyone come up with a better idea?
>
> Thanks
>
> Peter Wu
Received on Fri Aug 06 1999 - 12:09:30 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US