Getting Parent and Child from the same table & same column [message #653852] |
Wed, 20 July 2016 08:00 |
|
naveenjv
Messages: 3 Registered: April 2016 Location: Bangalore
|
Junior Member |
|
|
Hello Experts,
I have a table with the following information:
code ano vno
-------------------------------
40805681 56 00
40805682 56 01
vno='00' is a parent & non-zero is a child.
If I input parent (40805681), I am expecting the following output:
code ss
---------------
40805681 0
If I input child(40805682), I am expecting the following output:
code ss
---------------
40805681 0
40805682 1
Please do let me know how to achive this using SQL query?
Thanks,
Naveen
|
|
|
|
|
|
|
Re: Getting Parent and Child from the same table & same column [message #653862 is a reply to message #653852] |
Wed, 20 July 2016 14:19 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
This is just taking a guess at what you want. It looks like, if you provide a code, you want the row that corresponds to that code, and you want every row that has the same ano and has any vno that is less than the ano for that code. Please see the demonstration below, to which I have added some more data. If this is not what you want, then please provide more data and more explanation.
-- test data:
SCOTT@orcl_12.1.0.2.0> select * from test_tab order by code
2 /
CODE ANO VNO
---------- ---------- ----------
40805681 56 0
40805682 56 1
40805683 56 2
99999991 57 0
99999992 57 1
5 rows selected.
-- variable and value:
SCOTT@orcl_12.1.0.2.0> variable g_code number
SCOTT@orcl_12.1.0.2.0> exec :g_code := 40805681
PL/SQL procedure successfully completed.
-- query using above variable and value:
SCOTT@orcl_12.1.0.2.0> select code, vno
2 from test_tab
3 where code = :g_code
4 union all
5 select t2.code, t2.vno
6 from test_tab t1, test_tab t2
7 where t1.code = :g_code
8 and t1.ano = t2.ano
9 and t1.vno > t2.vno
10 order by code
11 /
CODE VNO
---------- ----------
40805681 0
1 row selected.
-- assignment of different value to variable and re-run of query:
SCOTT@orcl_12.1.0.2.0> exec :g_code := 40805682
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> /
CODE VNO
---------- ----------
40805681 0
40805682 1
2 rows selected.
|
|
|
|