Re: Can views join tables from sub selects?

On 2 May 2003, Hadley Willan wrote:

> Hmmm,
>   Again looking at the docs, this didn't jump out at me.
>
> Say I have a unit, and it can have states. If my view want's to show the
> latest state then all I need to is.
>
> CREATE VIEW v_unit_stuff AS
>   SELECT u.id AS unit,
>      u.unit_number,
>      (SELECT current_state FROM unit_status AS us WHERE
>           us.unit_number = u.unit_number ORDER BY
>             us.date_effective DESC LIMIT 1) AS unit_status
>      u.description
>     FROM unit AS u;
>
> But, my unit_state has a description table that I call unit_state_res
> (resource) and it would be good if I could join that against the
> current_status/unit_status, thus returning it's current state and the
> description of that state for display purposes.
>
> But when I try, the view doesn't seem to be able to use the unit_status
> sub select as a joinable column.
>
> CREATE VIEW v_unit_stuff AS
>   SELECT u.id AS unit,
>      u.unit_number,
>      (SELECT current_state FROM unit_status AS us WHERE
>           us.unit_number = u.unit_number ORDER BY
>             us.date_effective DESC LIMIT 1) AS unit_status
>      usr.unit_state_long_desc,
>      u.description
>  FROM unit AS u LEFT JOIN unit_state_res AS usr ON usr.id = unit_status;

Select list entries are effectively made after the join is complete, so
you can't use aliases from the select list in the on clause.  You might be
able to do something like:

select u.unit, u.unit_number, u.unit_status, usr.unit_state_long_desc,
u.description from
 (SELECT u.id AS unit,
     u.unit_number,
      (SELECT current_state FROM unit_status AS us WHERE
           us.unit_number = u.unit_number ORDER BY
             us.date_effective DESC LIMIT 1) AS unit_status
      u.description
     FROM unit AS u) as u left join unit_state_res as usr on
 usr.id=u.unit_status;