Re: Tricky query?

On 26 Aug 2004 13:32:09 -0700, 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.
>
>Any ideas?
>
>Thanks a lot!

The 2 unique keywords are redundant. The result of intersect is a set and set consists by definition of unique elements. The select can be written as
select distinct assetgroupid from assetgroupmapping where assetid in (3,5)

and it is a bit strange you think you need intersect. The second remark is even more strange. Why would it be difficult to implement?

--
Sybrand Bakker, Senior Oracle DBA