Subtracting values in MySQL Table

Question

I have prices in two different tables and want to subtract them (current price-last day price) and ORDER them in DESC form. I was wondering if it can be done using a single MySQL command.

Table Structure

Table 1
id | Item Name | Date       | Price
 1 | alpha     | 2011-10-05 | 10
 2 | beta      | 2011-10-05 | 12
 3 | gamma     | 2011-10-05 | 14 

Table 2
id | Item Name | Date       | Price
 1 | alpha     | 2011-10-04 | 8
 2 | beta      | 2011-10-04 | 10
 3 | gamma     | 2011-10-04 | 12
 4 | alpha     | 2011-10-03 | 4
 5 | beta      | 2011-10-03 | 6
 6 | gamma     | 2011-10-03 | 8

Answer 1

SELECT 
table1.id, table1.`Item Name`,
table1.`Date` AS CurrDate, table1.Price AS CurrPrice,
table2.`Date` AS PrevDate, table2.Price AS PrevPrice,
table1.Price - table2.Price AS Difference
FROM table1
LEFT JOIN table2 ON table1.id = table2.id AND table1.`Date` - INTERVAL 1 DAY = table2.`Date`
ORDER BY Difference DESC

There is nothing special about this query except the way I've used the LEFT JOIN. I believe if yesterday's rates for a record are not available, the the last three columns would contain NULL. Output:

id | Item Name | CurrDate   | CurrPrice | PrevDate   | PrevPrice | Difference
2  | beta      | 2011-10-05 | 12        | 2011-10-04 | 10        | 2
3  | gamma     | 2011-10-05 | 14        | 2011-10-04 | 12        | 2
1  | alpha     | 2011-10-05 | 10        | 2011-10-04 | 8         | 2