X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 821E3D1E823 for ; Mon, 19 Jan 2004 04:11:02 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 09289-07 for ; Mon, 19 Jan 2004 00:10:31 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 6B258D1E821 for ; Mon, 19 Jan 2004 00:10:30 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id i0J4ATpH020205; Sun, 18 Jan 2004 23:10:29 -0500 (EST) To: "Thomas Wegner" Cc: pgsql-sql@postgresql.org Subject: Re: Problem with LEFT JOIN In-reply-to: References: Comments: In-reply-to "Thomas Wegner" message dated "Fri, 16 Jan 2004 10:39:17 +0100" Date: Sun, 18 Jan 2004 23:10:29 -0500 Message-ID: <20203.1074485429@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/158 X-Sequence-Number: 16551 "Thomas Wegner" 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