Re: LEFT JOIN, entry can not be referenced

On Mon, Jun 26, 2017 at 5:31 PM, Jan Danielsson <jan(dot)m(dot)danielsson(at)gmail(dot)com>
wrote:

> SELECT
> wl.ts,wa.name,wl.user_id,u.name,wl.doc_id,d.doc_id,wl.
> docrev_id,dr.docrev,wl.file_id,f.fname,wl.issue
> FROM worklogs AS wl, workactions AS wa, users AS u
> LEFT JOIN documents AS d ON wl.doc_id=d.id
> LEFT JOIN docrevs AS dr ON wl.docrev_id=dr.id
> LEFT JOIN files AS f ON wl.file_id=f.id
> WHERE wl.action_id=wa.id AND wl.user_id=u.id
> ORDER BY wl.ts DESC;
>
> When I run this I get the error:
>
> --------------------
> ERROR: invalid reference to FROM-clause entry for table "wl"
> LINE 3: LEFT JOIN documents AS d ON wl.doc_id=d.id
> ^
> HINT: There is an entry for table "wl", but it cannot be referenced
> from this part of the query.
> --------------------
>

You should write out all of your joins explicitly.

FROM worklogs
JOIN workactions ON
JOIN users ON
LEFT JOIN documents ON
LEFT JOIN docrevs ON
LEFT JOIN files ON
--there were no non-join conditions in your where clause so it is omitted
here
ORDER BY

Mixing "FROM tbl1, tbl2 WHERE" and "FROM tbl1 JOIN tbl2 ON" syntax just
causes grief.

David J.