From: Tom Lane <tgl@sss.pgh.pa.us>
To: Thomas Wegner <tomaten@t-online.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: Problem with LEFT JOIN
Date: Sun, 18 Jan 2004 23:10:29 -0500
Message-ID: <20203.1074485429@sss.pgh.pa.us> (raw)
In-Reply-To: <bu8bg5$15oo$1@news.hub.org>
References: <bu8bg5$15oo$1@news.hub.org>
"Thomas Wegner" <tomaten@t-online.de> writes:
> Hello, whats wrong with this SQL?:
> SELECT L.*, A."lastname" AS firma_value, T."string_val1" AS type_value
> FROM "lists" L, "typecode" T
> LEFT JOIN "adressen" A ON A."id_adressen"=L."firma"
> WHERE T."id_typecode"=L."lists_type"
> ORDER BY L."id_lists"
> I get this:
> ERROR: relation "l" does not exist
The problem is in your ON clause: the above is equivalent to
SELECT ...
FROM "lists" L CROSS JOIN
("typecode" T LEFT JOIN "adressen" A ON A."id_adressen"=L."firma")
WHERE ...
so the ON clause is illegal because it controls the join of T and A,
in which L does not appear.
I have a sneaking suspicion that you are trying to port some MySQL code.
Last I heard, MySQL interprets the above FROM syntax as
FROM ("lists" L CROSS JOIN "typecode" T) LEFT JOIN "adressen" A ON A."id_adressen"=L."firma"
which makes the ON condition legal. Unfortunately for MySQL, their
parser is directly in violation of the SQL standard on this point.
JOIN is supposed to bind more tightly than comma.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, tomaten@t-online.de
Subject: Re: Problem with LEFT JOIN
In-Reply-To: <20203.1074485429@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox