simple join that drives me nuts

[Peter Primrose]

can anyone assist me with this one:
I am trying to convert this table:

thanks

  +------+-----------+-------------+
| ID | FILE_TYPE | amount |
+------+-----------+-------------+
| 1 | U | 200 |
| 2 | U | 249 |
| 3 | U | 114 |
| 4 | U | 249 |
| 5 | U | 176 |
| 1 | F | 1722 |
| 2 | F | 1890 |
| 3 | F | 2088 |
| 4 | F | 1169 |
| 5 | F | 1768 |
+------+-----------+-------------+
  

to this:

  +------+----------+---------+
| ID | AMOUNT_U | AMOUNT_F|
+------+----------+---------+
| 1 | 200 | 1722 |
| 2 | 249 | 1890 |
| 3 | 114 | 2088 |
| 4 | 249 | 1169 |
| 5 | 176 | 1768 |
+------+----------+---------+  

[stu derby]

INSERT INTO new_table
(id, amount_u, amount_f)
SELECT id, u.amount, f.amount
FROM
(SELECT id, amount FROM old_table WHERE file_type = 'U') u,
(SELECT id, amount FROM old_table WHERE file_type = 'F') f
WHERE u.id = f.id

This will work well only for pairs where both 'U' and 'F' exist; if one or the other is missing, the data from the part that's present will be silently dropped.