Re: Help constructing a query (UNION or subselect)

hegyvari_at_ardents.hu wrote:
>
> 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

In addition to the INTERSECT method above, I came up with two others which worked (that I think are distinct from the suggestions given).

The subselect method is:

SELECT t0.NAME from t0 OBJECT, t1 ATTRIBUTE where (t0.ID = t1.OID) and (t1.TYPE = 2) and (t1.VALUE = 1) and

(t1.OID IN (SELECT OID from ATTRIBUTE where (TYPE = 3) and 
(VALUE = 6) and (OID IN
(SELECT OID from ATTRIBUTE where (TYPE = 4) and (VALUE = 3)));

And the self join method is:

SELECT t0.NAME from t0 OBJECT, t1 ATTRIBUTE, t2 ATTRIBUTE, t3 ATTRIBUTE where (t0.ID = t1.OID) and

(t1.TYPE = 2) and (t1.VALUE = 1) and
(t2.TYPE = 3) and (t2.VALUE = 6) and
(t3.TYPE = 4) and (t3.VALUE = 3) and
(t1.OID = t2.OID) and (t2.OID = t3.OID);

Any others?

Thanks.

--Tim Received on Thu May 31 2001 - 19:35:57 CDT