oracle - help on left outer join with parent-child relationship -

i have following table in database :

id   grp#  code  parent# --   ----  ----  -------  0     10        null       0     30  sf         10   1     10  s        null   1     30  sf         10 

from this, given id , grp# need return list of children , code grp#. if children doesn't exist should return null.

so example : id = 0, grp#= 10 should return us, 30 , id = 0, grp#=30 should return sf,null

note : there should not duplicates in output.

so here test data:

sql> select * t42   2  /          id       grp# code                    parent# ---------- ---------- -------------------- ----------          0         10          0         30 sf                           10          1         10 s          1         30 sf                           10  sql> 

and here query returns results want:

sql> select p.code   2         , c.grp# child_grp#   3  t42 p   4       left outer join t42 c   5        on ( c.parent# = p.grp# )   6  p.id = &id   7  , p.grp# = &grp   8  / enter value id: 0 old   6: p.id = &id new   6: p.id = 0 enter value grp: 10 old   7: , p.grp# = &grp new   7: , p.grp# = 10  code                 child_grp# -------------------- ----------                           30                           30  sql> r   1  select p.code   2         , c.grp# child_grp#   3  t42 p   4       left outer join t42 c   5        on ( c.parent# = p.grp# )   6  p.id = &id   7* , p.grp# = &grp enter value id: 0 old   6: p.id = &id new   6: p.id = 0 enter value grp: 30 old   7: , p.grp# = &grp new   7: , p.grp# = 30  code                 child_grp# -------------------- ---------- sf  sql> 

---

"can rewrite query return single row instead of duplicates ?"

certainly - provided can specify additional business rule.

the easy way out deploy distinct keyword, last resort of broken query.