Re: Tricky query?

ibm_97_at_yahoo.com (JZ) wrote:
> Hi,
>
> Oracle 9205
>
> I have a table:
>
> SQL> select * from ASSETGROUPSMAPPINGS;
>
> ASSETGROUPID ASSETID
> ------------ ----------
> 1 1
> 1 3
> 1 4
> 1 6
> 2 3
> 2 5
>
> Now I like to have a query which can return a common 'assetgroupid'
> for a list of 'assetid'. I know it can be done through 'intersect':
>
> SQL> select unique ASSETGROUPID from ASSETGROUPSMAPPINGS where ASSETID
> =3 intersect select unique ASSETGROUPID from ASSETGROUPSMAPPINGS where
> ASSETID =5;
>
> ASSETGROUPID
> ------------
> 2
>
> The problem is that the number of 'assetid' is not fixed, it could be
> 2, 3, or 6. So it's difficult to implement into our JAVA code.

Why is that difficult to implement? You string together 2,3, or 6 query-parts with " intersect " between them. The only difficulty I can see would be if you want to re-use the prepared statments. Even that shouldn't be too hard, just store them in an array/vector/list indexed by size, preparing a new one if the desired size isn't already present.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB