pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Problem with LEFT JOIN 2+ messages / 2 participants [nested] [flat]
* Problem with LEFT JOIN @ 2004-01-16 09:39 Thomas Wegner <tomaten@t-online.de> 2004-01-19 04:10 ` Re: Problem with LEFT JOIN Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 2+ messages in thread From: Thomas Wegner @ 2004-01-16 09:39 UTC (permalink / raw) To: pgsql-sql 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 This version work, but i need a LEFT JOIN (L."firma" can have NULL): SELECT L.*, A."lastname" AS firma_value, T."string_val1" AS type_value FROM "lists" L, "typecode" T, "adressen" A WHERE T."id_typecode"=L."lists_type" AND A."id_adressen"=L."firma" ORDER BY L."id_lists" ------------------------------------------ Thomas Wegner ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Problem with LEFT JOIN 2004-01-16 09:39 Problem with LEFT JOIN Thomas Wegner <tomaten@t-online.de> @ 2004-01-19 04:10 ` Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2004-01-19 04:10 UTC (permalink / raw) To: Thomas Wegner <tomaten@t-online.de>; +Cc: pgsql-sql "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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-01-19 04:10 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2004-01-16 09:39 Problem with LEFT JOIN Thomas Wegner <tomaten@t-online.de> 2004-01-19 04:10 ` Tom Lane <tgl@sss.pgh.pa.us>
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