"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