postgreSQL and joining on alias-CodePudding

I have a query that, in very simplified form, looks like this:

SELECT aa, bb, xx
FROM (VALUES ('a1', 'b1'), ('a2', 'b2')) as T1(aa, bb)
LEFT JOIN (
    SELECT xx FROM (VALUES
    ('a1', 'y1'), ('x2', 'y2')) as T2(xx, yy)
) as T3 ON T1.aa = T3.xx ;

which executes to produce this:

 aa | bb |   xx   
---- ---- --------
 a1 | b1 | a1
 a2 | b2 | (NULL)
(2 rows)

In real life the column xx is defined using a case statement and alias:

SELECT
    ...,
    (CASE WHEN rgr.age_years < 18 THEN '< 18'
          WHEN rgr.age_years < 36 THEN '26-35'
          WHEN rgr.age_years < 46 THEN '36-45'
          WHEN rgr.age_years < 56 THEN '46-55'
          WHEN rgr.age_years < 130 THEN '> 55' END)
        as row_name,
    ...
FROM
    (VALUES ('< 18'), ('26-35'), ('36-45'),
            ('46-55'), ('> 55'))
    as prn(possible_row_name)
LEFT JOIN other_table rgr 
  ON prn.possible_row_name = ??row_name??

Obviously ??row_name?? is an alias and so is not available at the time I specify the join. But I've not found the right formulation.

The point is simply that I'm doing a select on table rgr and I want all the age tranches present, even if no entity satisfies that number.

CodePudding user response：

You can create the column on the fly inside the table expression rgr, as in:

SELECT
    ...,
    rgr.row_name,
    ...
FROM
    (VALUES ('< 18'), ('26-35'), ('36-45'),
            ('46-55'), ('> 55'))
    as prn(possible_row_name)
LEFT JOIN ( -- table expression rgr defined here
  select t.*,
     CASE WHEN rgr.age_years < 18 THEN '< 18'
          WHEN rgr.age_years < 36 THEN '26-35'
          WHEN rgr.age_years < 46 THEN '36-45'
          WHEN rgr.age_years < 56 THEN '46-55'
          WHEN rgr.age_years < 130 THEN '> 55' END
        as row_name
  from other_table t
) rgr ON prn.possible_row_name = rgr.row_name