SQL Subquery Using Parent Value In "Start With"

Question

Trying to combine several queries but running into an issue no matter which way I try to hook them together. Here is a mockup of the two:

Query 1 (Get all Gs and related Rs):

select distinct 
    a.g_id, 
    a.g_name,
    b.r_name, 
    b.r_id
from 
    g_tab a, 
    r_tab b,
    f_tab c
where
    a.g_id = b.g_id
    and b.r_id = c.r_id
    and c.f_id = 7
order by a.g_id

Query 2 (Traverse hierarchy and filter to specific group):

with parentGs as 
(
    select 
        f.g_id,
        f.g_name
    from 
        g_tab f
    where  
        f.delete_indicator is null
    connect by
       prior f.parent_g_id = f.g_id
    start with
        f.g_id = 22 --DO NOT WANT THIS HARDCODED
)

select 
    d.g_name
from
    parentGs d,
    g_group_members_tab e
where 
    d.g_id = e.member_g_id
    and e.g_group_description = 'test'

I do not want to alter the return count for query 1, but would like to join in the g_name from query 2 and add it as a newly aliased column.

Here is one example of what I am trying to get at, but does not work because of g_id being called "invalid identifier" in the "start with". I know parent values are only valid one subquery deep, but still get this same error if restructured with only one subquery (in the "from" in this case):

select distinct 
    a.g_id, 
    a.g_name,
    b.r_name, 
    b.r_id,
    (select 
        d.g_name
    from
        (select 
            f.g_id,
            f.g_name
        from 
            g_tab f
        where  
            f.delete_indicator is null
        connect by
           prior f.parent_g_id = f.g_id
        start with
            f.g_id = a.g_id) d, --THIS GIVES INVALID IDENTIFIER
        g_group_members_tab e
    where 
        d.g_id = e.member_g_id
        and e.g_group_description = 'test') as parent_g_name
from 
    g_tab a, 
    r_tab b,
    f_tab c
where
    a.g_id = b.g_id
    and b.r_id = c.r_id
    and c.f_id = 7
order by a.g_id

Any help in joining these two queries together would be greatly appreciated. My target platform is Oracle 11g. Thanks a lot!

* Update *

Here is some example SQL Fiddle data:

create table g_tab
( 
  g_id number(9),
  g_name varchar2(50),
  parent_g_id number(9),
  delete_indicator char(1)
)
/
create table g_group_members_tab
( 
  member_g_id number(9),
  g_group_description varchar2(50)
)
/
create table r_tab
( 
  r_id number(9),
  r_name varchar2(50),
  g_id number(9)
)
/
create table f_tab
( 
  f_id number(9),
  f_name varchar2(50),
  r_id number(9)
)
/
insert into g_tab (g_id, g_name, parent_g_id) values (0, 'a', null)
/
insert into g_tab (g_id, g_name, parent_g_id) values (1, 'b', 0)
/
insert into g_tab (g_id, g_name, parent_g_id) values (2, 'c', 1)
/
insert into g_tab (g_id, g_name, parent_g_id) values (3, 'd', null)
/
insert into g_tab (g_id, g_name, parent_g_id) values (4, 'e', 3)
/
insert into g_tab (g_id, g_name, parent_g_id) values (5, 'f', 4)
/
insert into g_tab (g_id, g_name, parent_g_id) values (6, 'g', null)
/
insert into g_group_members_tab (member_g_id, g_group_description) values (1, 'test')
/
insert into g_group_members_tab (member_g_id, g_group_description) values (3, 'test')
/
insert into r_tab (r_id, r_name, g_id) values (0, 'r', 0)
/
insert into r_tab (r_id, r_name, g_id) values (0, 'r', 1)
/
insert into r_tab (r_id, r_name, g_id) values (0, 'r', 2)
/
insert into r_tab (r_id, r_name, g_id) values (0, 'r', 3)
/
insert into r_tab (r_id, r_name, g_id) values (0, 'r', 4)
/
insert into r_tab (r_id, r_name, g_id) values (1, 'rr', 5)
/
insert into r_tab (r_id, r_name, g_id) values (0, 'r', 6)
/
insert into f_tab (f_id, f_name, r_id) values (7, 'f', 0)
/
insert into f_tab (f_id, f_name, r_id) values (7, 'f', 1)

Desired result:

g_id    g_name    r_name    r_id    parent_g_name
0       a         r         0       null
1       b         r         0       b   
2       c         r         0       b
3       d         r         0       d
4       e         r         0       d
5       f         rr        1       d
6       g         r         0       null

True, was just pointing out what I was working with... thinking the solution very likely could use Oracle specific functionality. — Cood, Jul 20 '14 at 15:54
Oracle-specific SQL extensions don't make it PL/SQL. But terminology aside, can you set this up in an SQL Fiddle with some data and show the results you're expecting? If `g_tab.g_id` is a primary key I'm not sure why you're joining back to that. — Alex Poole, Jul 20 '14 at 20:59
Added some SQL Fiddle data and the desired results above. So in my non working example, the inner most query is getting the hierarchy, the middle query is getting the desired parent record based off the group_description, and the outer query is getting all the Gs. Thanks a lot! — Cood, Jul 21 '14 at 05:09

score 0 · Accepted Answer · answered Jul 21 '14 at 16:54

You can get most of the way there just by turning your subquery into an in-line view:

select distinct 
    a.g_id, 
    a.g_name,
    b.r_name, 
    b.r_id,
    d.g_name as parent_g_name
from 
    g_tab a, 
    r_tab b,
    f_tab c,
    (
        select 
            f.g_id,
            f.g_name,
            connect_by_root f.g_id as root_g_id
        from 
            g_tab f
        where  
            f.delete_indicator is null
        connect by
            prior f.parent_g_id = f.g_id
    ) d,
    g_group_members_tab e
where
    a.g_id = b.g_id
    and b.r_id = c.r_id
    and c.f_id = 7
    and a.g_id = d.root_g_id
    and d.g_id = e.member_g_id
    and e.g_group_description = 'test'
order by a.g_id

Which gives:

      G_ID G_NAME R_NAME       R_ID PARENT_G_NAME
---------- ------ ------ ---------- -------------
         1 b      r               0 b             
         2 c      r               0 b             
         3 d      r               0 d             
         4 e      r               0 d             
         5 f      rr              1 d

You're missing the nulls, so you need to turn this into an outer join; but you need to join d to e within that. All of which is much more pleasant with ANSI joins:

select distinct 
    a.g_id, 
    a.g_name,
    b.r_name, 
    b.r_id,
    d.g_name as parent_g_name
from g_tab a
join r_tab b
on   b.g_id = a.g_id
join f_tab c
on   c.r_id = b.r_id
left join (
    select
        e.g_id,
        e.g_name,
        e.root_g_id
    from (
        select 
            f.g_id,
            f.g_name,
            connect_by_root f.g_id as root_g_id
        from 
            g_tab f
        where  
            f.delete_indicator is null
        connect by
            prior f.parent_g_id = f.g_id) e
    join g_group_members_tab f
    on   f.member_g_id = e.g_id
    where f.g_group_description = 'test'
    ) d
on  d.root_g_id = a.g_id
where c.f_id = 7
order by a.g_id

Which gives:

      G_ID G_NAME R_NAME       R_ID PARENT_G_NAME
---------- ------ ------ ---------- -------------
         0 a      r               0               
         1 b      r               0 b             
         2 c      r               0 b             
         3 d      r               0 d             
         4 e      r               0 d             
         5 f      rr              1 d             
         6 g      r               0

SQL Fiddle demo.

SQL Subquery Using Parent Value In "Start With"

1 Answers1