SUB SUB SUB SELECTS

[deizu net]

I have a table with stock closing prices. I'm trying to get the Average of the previous 50 closing prices for each date using mysql subselects.

Using Mysql Version 4.1.10-max-log

This is the create statement for the prices table

CREATE TABLE `prices` (
`date` date NOT NULL default '0000-00-00',
`comp_id` int(4) NOT NULL default '0',
`close` decimal(17,4) NOT NULL default '0.0000',
`volume` bigint(10) unsigned NOT NULL default '0',
`value` decimal(17,4) NOT NULL default '0.0000',
`close_bid` decimal(17,4) NOT NULL default '0.0000',
`close_ask` decimal(17,4) NOT NULL default '0.0000',
`cap_issue` bigint(20) unsigned NOT NULL default '0',
`curr_val` decimal(17,4) default NULL,
`high` decimal(17,4) NOT NULL default '0.0000',
`low` decimal(17,4) NOT NULL default '0.0000',
`max` decimal(17,4) NOT NULL default '0.0000',
`min` decimal(17,4) NOT NULL default '0.0000',
PRIMARY KEY (`comp_id`,`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

An example of what is in there
2005-01-02 01 22.00 etc etc
2004-12-22 01 21.00 etc etc

This is the query I'm trying to use. I get the following error

SELECT date as pdate, close,
(SELECT AVG(close) FROM prices WHERE date < pdate AND date >
(SELECT MIN(date)
FROM
(SELECT date FROM prices
WHERE comp_id = 13 AND date < pdate
ORDER BY date DESC LIMIT 50
) as p
)
)
as avg FROM prices WHERE comp_id = 13 AND date > '2005-01-02';

I get the following error
(ERROR 1054 (42S22): Unknown column 'pdate' in 'where clause')

The first SubSelect seems to see pdate just fine. I've tried it with only the first subselect and it works. However I need to have only the last 50 closing prices for each date Averaged. Note that trading does not occur on all dates.

If anyone can help me do this It would be greatly appreciated. Thanks in advance.