Re: SQL question

Try this:

select *
from foo
where item in (

   select item
   from foo
   group by item
   having count(*) = 2
)
and part in ('A','B')

Jared

On 1/13/06, Sandeep Dubey <dubey.sandeep_at_gmail.com> wrote:
>
> Hi,
>
> I am having tough time writing a select query. I won't blame it on
> Friday afternoon, it is really difficult to me.
>
> Here is an example:
>
> create table foo ( item number, part varchar2(2));
>
> insert into foo values(1,'A');
> insert into foo values(2,'A');
> insert into foo values(2,'B');
> insert into foo values(3,'A');
> insert into foo values(3,'B');
> insert into foo values(3,'C');
>
> I want to query for item that matches exact parts given in the query.
> i.e.if I query for part A, I should get 1 ( no 2 and 3)
>
> If I pass A and B , I should get only 2 (no 3)
> If I query for A, B and C, I should get only 3.
>
> Query can have n number of parts, It should return only that item that
> has exact match to parts, no less no more.
>
> Thanks
>
>
> Sandeep
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l