Re: Help constructing a query (UNION or subselect)

From: Alex Filonov <afilonov_at_pro-ns.net>
Date: Thu, 31 May 2001 03:58:11 GMT
Message-ID: <3B15C137.EAA25BB4@pro-ns.net>

Tim McNerney wrote:

> Tim McNerney wrote:
>
> > 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);
>
> I meant INTERSECT above, rather than UNION.
>
> --Tim

Subquery won't work here. You have a strong case for an intersect. The only other option I see is to create a query table, which has 2 columns, type and value, and join it to your two tables. Example:

create table query_table (t_type number, t_value number);

insert into query_table values(2,1);
insert into query_table values(3,6);
insert into query_table values(4,3);

select ob.name
from object obj, attribute att, query_table qt

where obj.id = att.object_id
  and att.type = qt.t_type
  and att.value = qt.t_value

group by obj.name
having count(1) = (select count(1) from query_table)

I'm not sure it's better solution. The only good thing about it that this query is the same for any set of conditions. Received on Wed May 30 2001 - 22:58:11 CDT