asked on

Oracle query help

I have a dataset that has parents and children that I am calling branches. Sample dataset:

Branch TreeId Name ParentId
Root 1 GRP A NULL
Root 2 SET A1 1
Child 6 SET A1B 2
Child 7 SET A1C 2

I then have a TreeId parameter that I need to pull from that level down to the children. This set will not go higher than a Root record where ParentId IS NULL. So what I am trying to do is let's say I pass in TreeId 1 in this case I would want the whole set back. Essentially any ParentId = Null can have additional roots under it. If I pass treeid = 2 I would want records 2,6 and 7. If I pass treeid 6 or 7 I would just want that record.

I would want to start with the NULL ParentId if it is null then work down based on the treeid passed in.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Geerlings

That looks close. I think you need that last line to be:
start with treeid = 1;

PortletPaul

It's more than close...

The question seeks a method that will accept different parameters to produce different results.
The method provided by slightwv is correct; One can use a variety of where clauses and get the expected result of each:

select *
from table1
connect by prior treeid = parentid
start with parentid is null
;

| BRANCH | TREEID |    NAME | PARENTID |
|--------|--------|---------|----------|
|   Root |      1 |   GRP A |   (null) |
|   Root |      2 |  SET A1 |        1 |
|  Child |      6 | SET A1B |        2 |
|  Child |      7 | SET A1C |        2 |


-- pass in TreeId 1, want the whole set back.  
select *
from table1
connect by prior treeid = parentid
start with treeid = 1
;

| BRANCH | TREEID |    NAME | PARENTID |
|--------|--------|---------|----------|
|   Root |      1 |   GRP A |   (null) |
|   Root |      2 |  SET A1 |        1 |
|  Child |      6 | SET A1B |        2 |
|  Child |      7 | SET A1C |        2 |


-- pass treeid = 2, want records 2,6 and 7
select *
from table1
connect by prior treeid = parentid
start with treeid = 2
;

| BRANCH | TREEID |    NAME | PARENTID |
|--------|--------|---------|----------|
|   Root |      2 |  SET A1 |        1 |
|  Child |      6 | SET A1B |        2 |
|  Child |      7 | SET A1C |        2 |

-- pass treeid = 6, want record 6
select *
from table1
connect by prior treeid = parentid
start with treeid = 6
;

| BRANCH | TREEID |    NAME | PARENTID |
|--------|--------|---------|----------|
|  Child |      6 | SET A1B |        2 |

Open in new window

also see http://sqlfiddle.com/#!4/d2a7fa/1

Mark Geerlings

I agree that with this small data set, either of these lines will return the same results (and quickly):
start with parentid is null;
or
start with treeid = 1;

But in most production systems, the performance difference between these two can be *VERY* different! The "is null" option will force a full-table scan. If the table has a million records, that will take a while. The "treeid = 1" option will use an index (assuming the table has an index on this column). That will be *MUCH* faster if the table has more than a couple thousand rows.

slightwv (䄆 Netminder)

>>I think you need that last line to be: start with treeid = 1;

Paul is correct. 6 was just my last test case in the original requirement:

If I pass treeid = 2 I would want records 2,6 and 7. If I pass treeid 6 or 7 I would just want that record.

I didn't post all combinations. I thought it was sort of a given.

>>these lines will return the same results (and quickly): start with parentid is null; or start with treeid = 1;

I get no rows is I use 'is null'.

PortletPaul

Parentid is null
Will return rows

Treeid is null
Would not return rows from the sample

slightwv (䄆 Netminder)

Sorry. I didn't read the actual column involved. :(