mysql - how to make a SELECT that merges duplicated Primary Keys -

i'm performing query looks this:

select a.transactionid,a.customerid,b.value               adjustments                  inner join change b                          on  a.transactionid = b.transactionid                          , a.event_date    = b.event_date                          , a.event_id      = b.event_id          comment 'transfer'         order a.transactionid; 

this query brings following result:

    transactionid | customerid | value    ------------------------------------          transfer-001  |    custa   | -200     transfer-001  |    custb   |  200     transfer-002  |    custc   | -150     transfer-002  |    custd   |   0     transfer-003  |    custa   |   0     transfer-003  |    custc   |  150 

i need change query bring list ignore cases sum of value 0 same transactionid , also, group customerid , values following:

   transactionid | customerid_a | value_a | customerid_b | value_b  ------------------------------------------------------------------          transfer-002 |    custc     |   -150  |    custd     |    0     transfer-003 |    custa     |      0  |    custc     |  150 

can give advise how solve this?

try :

select * ( select a.transactionid,a.customerid,b.value           adjustments      inner join change b                      on  a.transactionid = b.transactionid                      , a.event_date    = b.event_date                      , a.event_id      = b.event_id      comment 'transfer' )m group transactionid,customerid,value having sum(value) <> 0 order transactionid;