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

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 8 Aug 1999 15:34:59 GMT
Message-ID: <7ok833$8ot$1@news.seed.net.tw>

Peter Wu <wupz_at_hotmail.com> wrote in message news:37AB03BA.A90FA2E1_at_hotmail.com...
> 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.

select id
  from T
  where code in ('aa', 'cc')
  group by id
  having count(distinct code)=2; Received on Sun Aug 08 1999 - 10:34:59 CDT