mysql- subtract values between rows where column values correspond

Question

I have a table like:

Number  |  Event  |  Weight
1            4          150
1            4          160
2            5          200
2            4          200
3            6          190
3            6          195

For each row, I would like to subtract from its Weight, the Weight of another row where Number and Event matches (if exists). The desired output is:

Number  |  Event  |  Weight  | DIFF
1            4          150    -10
1            4          160     10
2            5          200    NULL
2            4          200    NULL
3            6          190     -5
3            6          195      5

Is such an operation possible? Not sure if relevant, eventually I would need to turn this query into a view. Thanks in advance.

Answer 1

You need a left join:

select 
  t.*,
  t.weight - tt.weight diff
from tablename t left join tablename tt
on tt.number = t.number and tt.event = t.event and tt.weight <> t.weight