Thread: [GENERAL] LEFT JOIN, entry can not be referenced

Hello,

   I'm trying to use LEFT JOIN's in a manner which I imagine is pretty
archetypal.  In short; I have a table called worklogs which has a few
columns that can't be NULL, and a few columns which may reference other
tables or will be NULL.  If the optional columns are not NULL I want to
use their id's to fetch names/titles from other columns.

   I use the following query to gather a list of the work log rows (this
query is much simpler than it looks at first glance; it just has a lot
of columns):

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.
--------------------

   This is at the outer bounds of my SQL knowledge; I understand what
the error and hint are saying (wl isn't valid in the context of the
JOIN), but I'm not sure how to remedy that / how to rephrase the query.


   ("Dear Diary"-moment: I've had queries in the past which work in
postgresql which I couldn't run in sqlite, but this is the first time I
can recall where a query works in sqlite but not in postgresql).

--
Kind regards,
Jan Danielsson

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Jun 26, 2017 at 5:31 PM, Jan Danielsson <jan.m.danielsson@gmail.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.
> ...
> Mixing "FROM tbl1, tbl2 WHERE" and "FROM tbl1 JOIN tbl2 ON" syntax just
> causes grief.

More specifically, the commas can be read as CROSS JOINs of the lowest
syntactic priority, so that what you wrote is equivalent to

SELECT ... FROM
  worklogs AS wl
  CROSS JOIN workactions AS wa
  CROSS JOIN (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 ...

You could further parenthesize that, understanding that JOIN operators
bind left-to-right when not parenthesized, but I think it would just
add clutter not clarity.  Anyway, the point is that that first ON
clause can only refer to "u" and "d", because only those two tables
are in-scope for it.

There are other RDBMSes (mumble ... ancient mysql versions ... mumble)
that give the commas a different syntactic priority and would allow
that ON clause to reference "wl".  But they're wrong per SQL spec.

            regards, tom lane