Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Help constructing a query (UNION or subselect)
In article <3B157876.7153F5DD_at_truis.com>, tmcnerney_at_truis.com says...
> I'm having a problem coming up with a query for a particular select I
> need to do. There are two tables involved, that look like:
>
> OBJECT - ID NUMERIC, NAME VARCHAR
> ATTRIBUTE - OBJECT_ID NUMERIC, TYPE NUMERIC, VALUE NUMERIC
>
> The OBJECT_ID in ATTRIBUTE maps to the ID in OBJECT. There will be
> multiple ATTRIBUTE entries for each OBJECT entry. The OBJECT_ID + TYPE
> will be unique. The TYPE is used to specify what kind of attribute we
> are dealing with.
>
> I need to be able to do a query that returns the NAME of all OBJECTS in
> which each of several ATTRIBUTE entries match a specific set of
> constraints. If the tables contained:
>
> OBJECT
>
> ID | NAME
> ----------
> 1 | O1
> 2 | O2
> 3 | 03
>
> ATTRIBUTE
>
> OID | TYPE | VALUE
> -------------------
> 1 | 1 | 5
> 1 | 2 | 1
> 1 | 3 | 6
> 1 | 4 | 3
> 2 | 1 | 2
> 2 | 2 | 5
> 2 | 3 | 6
> 2 | 4 | 1
> 3 | 1 | 4
> 3 | 2 | 1
> 3 | 3 | 6
> 3 | 4 | 3
>
> and I wanted to return the NAME for all OBJECTs with ATTRIBUTE entries
> with TYPE 2, VAL 1 and TYPE 3, VAL 6 and TYPE 4, VAL 3. Only 1 & 3 meet
> all 3 criteria. 2 meets only the second, so it wouldn't be returned.
>
> Obviously, the degenerate case where we have only one criteria (say the
> first in the above example) is easy:
>
> SELECT t0.NAME from t0 OBJECT, t1 ATTRIBUTE
> where (t0.ID = t1.OID) and (t1.TYPE = 2) and (t1.VALUE = 1);
>
Maybe I'm off the track, but i think it's possible to do it with a query
like :
select o.* from
object o,
(select distinct oid from attribute where type=2 and val=1) crit1, (select distinct oid from attribute where type=3 and val=6) crit2, (select distinct oid from attribute where type=4 and val=3) crit3where o.id=crit1.oid and o.id=crit2.oid and o.id=crit3.oid
If you need, this type of query can be modified to "fit at least 2 of the 3 critetia" with outer joins (i don't think this is possible with intersect), but i'm almost sure that this solution is neither elegant, nor efficient... If the oid and type field in the attribute is unique, no distict is needed (this may speed up a bit)
Best regards
Laszlo Papp Received on Thu May 31 2001 - 02:25:34 CDT