MySQL how to reuse select column aliase, when I aliase subquery?

Question

I have something like:

Select1 ... 
        ,(fieldA - fieldB) AS Y, 
        ,(Select2 
                 (Select3 percent 
                    From ... 
                    Join.... 
                    Where ... 
                    Order by some_date 
                    Limit 1) AS X 
            From ... 
            Join... 
            Where ... 
            Order by some_date2 
            Limit 1) AS X
        ,(X * Y) AS output
  From ...
  Join ...
  Join ...

• Yes I have Select nested inside Select inside Select - each using values from one level up (they wont work without outer levels!)
• Selects are used because I need filter after "date nearest to given but not newer" in each select
• As You can see I reuse results returned in that nested Select twice
• Right now I copy that nested Select to where I wrote X in above pseudo-code

Everything is ok till I need to reuse value returned by that nested select.

How do I reuse X alias in definition of "output" column?

PS Wont past code as its more then A4 right now! It works as expected but is hard on eyes.

Answer 1

You can do this by using, once again, another nested SELECT. This new outer-most SELECT can then access X and Y.

Select0
        ...
        ,(X * Y) AS output
    From (
        Select1 ... 
            ,(fieldA - fieldB) AS Y, 
            ,(Select2 
                     (Select3 percent 
                        From ... 
                        Join.... 
                        Where ... 
                        Order by some_date 
                        Limit 1) AS X 
                From ... 
                Join... 
                Where ... 
                Order by some_date2 
                Limit 1) AS X
      From ...
      Join ...
      Join ...) AS values