help on left outer join with parent-child relationship

i have following table in my database :

id   grp#  code  parent#
--   ----  ----  -------
 0     10  US       NULL     
 0     30  SF         10 
 1     10  S        NULL 
 1     30  SF         10

From this, given an id and grp# i need to return开发者_Python百科 the list of children and the code for the grp#. If the children doesn't exist it should return NULL.

So for example : for id = 0, grp#= 10 it should return US, 30 and for id = 0, grp#=30 it should return SF,NULL

Note : there should not be duplicates in the output.

So here is your test data:

SQL> select * from t42
  2  /

        ID       GRP# CODE                    PARENT#
---------- ---------- -------------------- ----------
         0         10 US
         0         30 SF                           10
         1         10 S
         1         30 SF                           10

SQL>

And here is a query which returns the results you want:

SQL> select p.code
  2         , c.grp# as child_grp#
  3  from t42 p
  4       left outer join t42 c
  5        on ( c.parent# = p.grp# )
  6  where p.id = &id
  7  and p.grp# = &grp
  8  /
Enter value for id: 0
old   6: where p.id = &id
new   6: where p.id = 0
Enter value for grp: 10
old   7: and p.grp# = &grp
new   7: and p.grp# = 10

CODE                 CHILD_GRP#
-------------------- ----------
US                           30
US                           30

SQL> r
  1  select p.code
  2         , c.grp# as child_grp#
  3  from t42 p
  4       left outer join t42 c
  5        on ( c.parent# = p.grp# )
  6  where p.id = &id
  7* and p.grp# = &grp
Enter value for id: 0
old   6: where p.id = &id
new   6: where p.id = 0
Enter value for grp: 30
old   7: and p.grp# = &grp
new   7: and p.grp# = 30

CODE                 CHILD_GRP#
-------------------- ----------
SF

SQL>

"Can we rewrite the query to return a single row instead of duplicates ?"

Certainly - provided you can specify the additional business rule.

The easy way out is to deploy the DISTINCT keyword, the last resort of the broken query.