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(dot)willan(at)deeperdesign(dot)co(dot)nz > www.deeperdesign.com > +64(21)-28-41-463
Level 1, 4 Tamamutu St, PO Box 90, TAUPO 2730, New Zealand.