From: | Jan Danielsson <jan(dot)m(dot)danielsson(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | LEFT JOIN, entry can not be referenced |
Date: | 2017-06-27 00:31:06 |
Message-ID: | 284316f1-8dd4-3b99-742e-a89885cc1ef5@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2017-06-27 01:11:14 | Re: LEFT JOIN, entry can not be referenced |
Previous Message | Jim Fulton | 2017-06-26 23:13:39 | Re: When inserting from a SELECT with an ORDER BY, are the inserts (and associated triggers) applied in order? |