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.

Cause nested LEFT JOINs complain about columns not found. Those columns should come from most outer JOINs, but would be needed in nested JOINs. Nested selects do not complain about them. — przemo_li, Apr 16 '15 at 06:13

score 1 · Accepted Answer · answered Apr 15 '15 at 17:24

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

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

1 Answers1