Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to a better way to find a record in the table?
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
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