ERROR: missing FROM-clause entry for table

I am receiving this error for the query pasted below.
Is the LEFT JOIN on the table not enough?
What needs to happen here?
I am guess something to do with derived tables

http://pastie.org/10715876



--
View this message in context: http://postgresql.nabble.com/ERROR-missing-FROM-clause-entry-for-table-tp5886750.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.