Re: SQL question
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: dubey.sandeep@xxxxxxxxx
- Date: Fri, 13 Jan 2006 21:44:43 +0100
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
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
