Thread: Problem with LEFT JOIN

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

"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