Nested query problem

I'm trying to write a query that will sum the quantities in the funds
table for all dates that are less than the navDate in the nav table.
I'm trying to use a nested query but it doesn't work. It says 'Unknown
column 'NAV.navDate' in 'where clause'"
I'm assuming I'm using this wrong. Is it possible to do in this way?


SELECT navDate, nav, funds.theQuantity FROM navs AS NAV, (SELECT
SUM(quantity) as theQuantity FROM funds WHERE transactionDate <
NAV.navDate AND username='$user') AS funds WHERE navDate > (SELECT
MIN(transactionDate) FROM funds WHERE username='$user')

arod wrote:
> I'm trying to write a query that will sum the quantities in the funds
> table for all dates that are less than the navDate in the nav table.
> I'm trying to use a nested query but it doesn't work. It says 'Unknown
> column 'NAV.navDate' in 'where clause'"
> I'm assuming I'm using this wrong. Is it possible to do in this way?
>
>
> SELECT navDate, nav, funds.theQuantity FROM navs AS NAV, (SELECT
> SUM(quantity) as theQuantity FROM funds WHERE transactionDate <
> NAV.navDate AND username='$user') AS funds WHERE navDate > (SELECT
> MIN(transactionDate) FROM funds WHERE username='$user')
>

Well, for one thing you can't use NAV like this - you'll have to use the
full table name (navs).

But I'm not real sure what you're trying to do here. And not having the
table layout, it's impossible to know how to code this.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================