sql - Parent Child over id query -

example data:

id    parent    child     total  isparent isparentchildthing 1     j          na       5       1        1 1     b          na       -5      0        1 1     j          na       -10     1        1 1     b          na        10     0        1 2     x          na       -1      1        1 2     y          na        1      0        1 3     z          na        6      0        1 3     w          na        7      0        1 3     p          na        -6     1       1 3     r          na        -7     1       1 3     tt         yy        500    0      0 

desired output:

id    parent   child total  1       j       b     5      1       j       b     -10 2       x       y      -1 3       p       z       -6 3       r       w       -7 

in psuedo/english, each "pair" in given id, want parent , parent's total value , "child" goes parent determined isparent = 0 , total = total*-1 isparent = 1

any suggestions welcome

according requirement, need simple self-join find 1 child can paired each parent.

select p.id, p.parent, c.parent child, p.total mytable p left outer join mytable c  on c.isparent=0  , c.id=p.id  , c.total = p.total*-1 p.isparent=1 

note query "break" if there ever 2 or more parents same id , same total, according requirement, assuming cannot happen.