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.