How to select rows from a hierarchical query filtering by descendant value in Oracle?

Question

Given the table

ID       PARENT_ID      STRVAL      SUBTYPE     SUBVAL
0        null           Chicago     location    city
1        0              Best Buy    building    bestbuy
2        0              Walmart     building    walmart
3        0              Amazon      building    amazon
4        1              Macbook     object      macbook
5        2              Sausages    object      sausages
6        3              Macbook     object      macbook
7        3              Tupperware  object      tupperware

What I'm attempting to do is query this table and get all items from level 1 (the buildings), but what I need to do is filter this return set by returning those that have children containing a certain value. The following query is what I have so far which returns Best Buy, Walmart, and Amazon

SELECT * FROM (
SELECT strval, parent_id, id
FROM stores
where LEVEL = 1
CONNECT BY PRIOR id = parent_id
START WITH parent_id = 0
) 

What I would like to do is get a return where one of the descendants has a subtype of object and a subval of macbook, thus returning only Best Buy and Amazon from my query. I'm not really sure where to go from here.

SQLFiddle

Answer 1

Try reversing your CONNECT BY condition and starting with (i.e., START WITH) what you know:

SELECT DISTINCT strval, parent_id, id
FROM stores
where subtype = 'building'
CONNECT BY id = prior parent_id
START WITH subtype = 'object' and subval = 'macbook';

Update for more general question

In the comments, you asked what if the starting values aren't at the same level?

In that case, I'm afraid you'll have to look at the whole tree for each building and then filter.

I added this row to your test data:

insert into stores values (8, 4, 'Year','edition','2015');

Then, this query gives the answer:

WITH whole_tree AS
       (SELECT strval,
               parent_id,
               id,
               CONNECT_BY_ROOT(strval) building,
               SYS_CONNECT_BY_PATH (subtype || ':' || subval, ',') PATH
        FROM   stores
        CONNECT BY PRIOR id = parent_id
        START WITH subtype = 'building')
SELECT distinct building
FROM   whole_tree
WHERE  PATH LIKE '%object:macbook%edition:2015%';

Answer 2

This join should give you macbook objects whose parents are buildings. Feel free to select columns you need only:

select *
from
(
select *
from stores
where subtype = 'object' 
and strval = 'Macbook'
) macs
join
(
select *
from stores 
where subtype = 'building'
) bld
on bld.id = macs.parent_id