Re: Problem with LEFT JOIN

"Thomas Wegner" <tomaten(at)t-online(dot)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