Thread: Can views join tables from sub selects?

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;


Am I barking up the wrong tree here? Is this even possible?

I've tried usr.id = v_unit_stuff.unit_status, usr.id =
this.unit_status...

Any help would be appreciated.

Thank you.
--
Hadley Willan > Systems Development > Deeper Design Limited. +64(7)377-3328
hadley.willan@deeperdesign.co.nz > www.deeperdesign.com > +64(21)-28-41-463
Level 1, 4 Tamamutu St, PO Box 90, TAUPO 2730, New Zealand.

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;