MySQL to return multiple differences between many rows -

i have following problem need return multiple differences on values in 1 table based on specifications given in other 2 tables. i'm pretty can accomplished inner joins in right place. greatest problem have fact 1 there might more 2 values need compared , results should returned in 1 row.

the differences should returned calculated values in table c , specifications of should compared given in table a.

below have made example of situation it's easier show situation , example return row. have tried many tricks without success appreciated.

the database uses mysql.

thanks in advance!

table a:

|  identifier  |  type   |   top_id ------------------------------------- |   aaaa       |   x     |    |   bbbb       |   y     |    aaaa |   cccc       |   y     |    aaaa |   dddd       |   y     |    aaaa 

table b:

|  identifier  |  is_base -------------------------- |   bbbb       |   true |   cccc       |   false |   dddd       |   false 

table c:

|  identifier  |  type   | sub_type |  value ---------------------------------------------- |   bbbb       |       |    q     |   100 |   cccc       |       |    q     |   90 |   dddd       |       |    q     |   80 

result:

|    top_id    |    base    |   diff1_id |  diff1_value  |  diff2_id  |  diff2_value    ------------------------------------------------------------------------------------- |    aaaa      |    bbbb    |    cccc    |     10        |   dddd     |     20 

try:

select at.top_id,        ct.identifier,        group_concat(concat(cf.identifier,':', ct.value-cf.value)) diffs tablea @ join tableb bt on at.identifier = bt.identifier , bt.is_base = 'true' join tablec ct on bt.identifier = ct.identifier  join tablea af on at.top_id = af.top_id join tableb bf on af.identifier = bf.identifier , bf.is_base = 'false' join tablec cf on bf.identifier = cf.identifier  group ct.identifier 

sqlfiddle here.