Re: SQL question

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: 2006-01-13 21:44:43
Message-id: 1137185083.8093.1.camel@frlinux2.roughsea.com

I am not too proud of it, but it works:

SQL> set verify off
SQL> select * from foo;

      ITEM PA
---------- --

         1 A
         2 A
         2 B
         3 A
         3 B
         3 C

6 rows selected.

SQL> @fooquery
Enter value for combination: A

      ITEM

---

         1

SQL> @fooquery
Enter value for combination: A,B

      ITEM

---

         2

SQL> @fooquery
Enter value for combination: A,B,C

      ITEM

---

         3

SQL> !cat fooquery.sql
select a.item
from (select item,

             ltrim(sys_connect_by_path(part, ','), ',') combination,
             level parts
      from foo
      connect by  nocycle item = prior item
            and part != prior part) a,
     (select item, count(*) cnt
      from foo
      group by item) b
where a.parts = b.cnt

  and a.item = b.item
  and a.combination = '&combination'
/

Note that it generates all combination. Entertaining, but I wouldn't try it on large numbers of rows.

HTH S Faroult

On Fri, 2006-01-13 at 14:30 -0500, Sandeep Dubey 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
>

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

Received on Fri Jan 13 2006 - 21:44:43 CST