OraFAQ Forum: SQL & PL/SQL » Getting Parent and Child from the same table & same column

Home » SQL & PL/SQL » SQL & PL/SQL » Getting Parent and Child from the same table & same column (Oracle 11g/Windows 8.1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: Getting Parent and Child from the same table & same column [message #653853 is a reply to message #653852]

Wed, 20 July 2016 08:05

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: Getting Parent and Child from the same table & same column [message #653854 is a reply to message #653853]

Wed, 20 July 2016 08:14

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Left join the table to itself to optionally get the parent. Discard the parent if it has the same vno as the record with the code specified.

Report message to a moderator

Re: Getting Parent and Child from the same table & same column [message #653855 is a reply to message #653852]

Wed, 20 July 2016 09:45

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Also, if you want we continue to help, you'd think to feedback, say how you solve your issue and thank people who gave you answers in your previous topic.

Report message to a moderator

Re: Getting Parent and Child from the same table & same column [message #653859 is a reply to message #653855]

Wed, 20 July 2016 13:38

Bill B
Messages: 1971
Registered: December 2004

Senior Member

This would have been cleaner and easier with a daughter table but the query should be simple. Give us some sample data and the structure of the tables and what is the common link, I assume that it is ANO.

Report message to a moderator

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: 9090
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.

Report message to a moderator

Re: Getting Parent and Child from the same table & same column [message #653889 is a reply to message #653862]

Thu, 21 July 2016 06:23

naveenjv
Messages: 3
Registered: April 2016
Location: Bangalore

Junior Member

Thank you Barbara Boehmer. Your answer is perfect.

Report message to a moderator

Previous Topic:	ORA-01006
Next Topic:	Deadlock experienced but Blocking lock expected

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 16:11:58 CDT 2024