Accessing a field in a sub-subquery SQL

I have a fairly complex query in which I am working with with multiple nested layers of queries. One of the requirements of the query is that I access a field from a subquery's subquery in the main query. However, due to aggregates in the middle query, I can not include the field in that query.

eg.

select a, d.x, f.v?
from b
  join (select a, max(z), min(z), Count(y) 开发者_JAVA百科as x
        from c
          join (select a, Count(t) as v
                from e
               ) f 
            on f.a = c.a
            Group By a
            Having min(z) <> max(z)
       ) d 
     on d.a = b.a

Is this possible?

No.

Try this instead. Basically, get the attributes you need as you go up hierarchy (may be rename them if needed) and then use them.

Select a, d.fa From b
join (select a,f.a fa from c
join (select a from e) f on f.a = c.a
) d on d.a = b.a

I don't have test tables yet..If you see any more issues, post them here.

* Edit Based on updated Question*

Even here, the logic remains the same, you'll have to either use...

select a, d.x, d.v?
from b
  join (select a, f.v, max(z), min(z), Count(y) as x
        from c
          join (select a, v
                from e
               ) f 
            on f.a = c.a
            Group By a, f.v
            Having min(z) <> max(z)
       ) d 
     on d.a = b.a

Or if your Database supports Scalar sub queries, you can get the value at the final outer Query using..

select a, d.x, (select v from e where e.a = b.a) f_v
from b
  join (select a, max(z), min(z), Count(y) as x
        from c
          join (select a, v
                from e
               ) f 
            on f.a = c.a
            Group By a
            Having min(z) <> max(z)
       ) d 
     on d.a = b.a

it's probably more straight forward if you just bring the table you care about (e in this case) back into the From clause

SELECT a, 
       d.x, 
       e.v 
FROM   b 
       JOIN (SELECT a, 
                          MAX(z), 
                          MIN(z), 
                          COUNT(y) AS x 
                   FROM   c 
                          JOIN (SELECT a 
                                FROM   e) f 
                            ON f.a = c.a 
                   GROUP  BY a 
                   HAVING MIN(z) <> MAX(z)) d 
         ON d.a = b.a 
       JOIN (SELECT a, Count(t) as v
            FROM e
            GROUP BY A ) e
         ON e.a = d.a