SQL challenge: can you avoid this self-join?!?!

Here's something of a challenging SQL problem. I'm trying to only scan a table once, yet the SQL that I know won't allow this. Does Oracle (or for that matter the SQL standard) have some way to help me?

Consider a table ATTR with columns PARENT_ID, ATTR_ID, ATTR_VAL. Unique key is PARENT_ID, ATTR_ID. Assume any other indexes that you want, but be aware that ATTR_VAL is modestly dynamic.

I can easily look for a PARENT_ID for any one ATTR_ID, ATTR_VAL pair:

SELECT parent_id FROM attr
 WHERE attr_id = 1 AND attr_val = :b1

I can also easily do this looking at multiple attributes when I only need one condition to be met with an OR, as:

SELECT DISTINCT parent_id FROM attr
 WHERE (attr_id = 1 AND attr_val = :b1)

    OR (attr_id = 31 AND attr_val = :b2)

But how to handle the condition where I want to have the two ATTR_ID, ATTR_VAL pairs "and-ed" together? I know that I can do this:

SELECT DISTINCT parent_id FROM
(SELECT parent_id FROM attr WHERE attr_id = 1 AND attr_val = :b1)
UNION ALL
(SELECT parent_id FROM attr WHERE attr_id = 31 AND attr_val = :b2)

But this will necessitate looking at ATTR twice. This is maybe okay if there are only two conditions, but what about when there might be 10 or even 50? At some point this technique becomes unacceptable.

Clearly:

SELECT DISTINCT parent_id FROM attr
 WHERE (attr_id = 1 AND attr_val = :b1)
   AND (attr_id = 31 AND attr_val = :b2)

won't work (each row has but one ATTR_ID). This will end up doing the same basic thing as the UNION-ALL (only without the sort caused by the DISTINCT): SELECT parent_id FROM attr a1, attr a2

 WHERE a1.parent_id = a2.parent_id
   AND a1.attr_id = 1 AND a1.attr_val = :b1
   AND a2.attr_id = 31 AND a2.attr_val = :b2

but the fundamental problem of scanning ATTR twice remains.

What cleverness can I apply here to only scan ATTR once?

Thanks,
:-Phil Received on Thu Aug 07 2003 - 20:00:25 CDT