Help constructing a query (UNION or subselect)

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);

But I need to be able to do this for arbitrary criteria. I believe a UNION would work where the above first example would be:

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

But I need an alternate to a UNION. I think this can be done using a subselect, but I really don't understand subselects and was hoping someone could help me out with the subselect varient of the above. Also, any suggestions for other ways of doing the above would be greatly appreciated. Even if they aren't as effecient or elegant, I'd like to know all possible options.

Thanks in advance.

--Tim
Received on Wed May 30 2001 - 17:51:41 CDT