Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help constructing a query (UNION or subselect)

Re: Help constructing a query (UNION or subselect)

From: <hegyvari_at_ardents.hu>
Date: Thu, 31 May 2001 09:25:34 +0200
Message-ID: <MPG.1580102747380076989683@192.168.0.1>

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) crit3
    where 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US