Derived Tables with aggrigate queries.

[DavidJA]

Hi all,

I have a query with a derived table in the from clause.

The derived table is made up of an agrigate query. FROM xyz LEFT JOIN (select min(dteDeadline) as MinDeadline, OrderID FROM tblOrderItems) is there any way that I can put a where clause in the aggrigate query and specify columsn from the main query, eg: FROM xyz LEFT JOIN (select min(dteDeadline) as MinDeadline, OrderID FROM tblOrderItems WHERE CustomerID = Customers.CustomerID)?

When I try this I get a column not found. I can of course specify the column to output in my derived table then join on it in the parent query, but I want to do the aggrigate on a column that can not be in the group by clause of the aggrigate query.

 

[bperry]

I don't think you can do that.
I've never been able to get the select in a derived table to refer to a column from a table 'outside the brackets'.

You want it work like a subquery in that regard, but I guess it doesn't.

bperry