Обсуждение: LEFT JOIN problem

2013/8/10 Michael Rowan <michael.rowan3@gmail.com>:
> Hi
> Extreme novice here. Pls be gentle. I have a database with four tables
>
> orders (have a foreign key to depots.de_id)
> depots (have a foreign key to customers.co_id)
> companies
> addresses
>
> depots have an optional cross reference from depots.de_ad_id to
> addresses.ad_id
>
> so:
>
> SELECT co_id, de_id, or_id
> FROM orders, depots, companies
> LEFT JOIN addresses ON (de_ad_id=ad_id)
> WHERE or_id=de_or_id AND co_id=de_co_id;
>
> returns error
> ERROR:  invalid reference to FROM-clause entry for table "depots"
> LINE 1: ...pots, orders, companies LEFT JOIN addresses ON (depots.de_...
>                                                              ^
> HINT:  There is an entry for table "depots", but it
>
> The error message is incomplete.
>
> Any idea whats wrong with my code?  Thanks
>
> Mike
>
>

Hi Mike,

The "invalid reference" error means that, from the "LEFT JOIN" part of
the query, you cannot refer to columns belonging to the "depots"
table.
This may be due to the fact you're mixing implicit and explicit joins
in your query.
Try doing the same query with only explicit joins.

For example (I'm going to assume the relationship between columns and tables) :

SELECT c.co_id, d.de_id, o.or_id
FROM depots d
JOIN orders o ON o.or_id=d.de_or_id
JOIN companies c ON c.co_id=d.de_co_id
LEFT JOIN addresses a ON (d.de_ad_id=a.ad_id)
;

I'm not sure the relationships I set up are correct, because I can't
see the meaning of the LEFT JOIN with the table addresses if you're
not going to select a column from this table ... it won't restrict the
resultset, nor add any information to it.

By the way, I always used explicit joins, I find the queries easier to
read that way.
For the same reason, you should use aliases for the table names.

--
Mael

Mael Rimbault <mael.rimbault@gmail.com> writes:
> 2013/8/10 Michael Rowan <michael.rowan3@gmail.com>:
>> SELECT co_id, de_id, or_id
>> FROM orders, depots, companies
>> LEFT JOIN addresses ON (de_ad_id=ad_id)
>> WHERE or_id=de_or_id AND co_id=de_co_id;
>>
>> returns error
>> ERROR:  invalid reference to FROM-clause entry for table "depots"
>> LINE 1: ...pots, orders, companies LEFT JOIN addresses ON (depots.de_...
>>                                                            ^
>> HINT:  There is an entry for table "depots", but it

> The "invalid reference" error means that, from the "LEFT JOIN" part of
> the query, you cannot refer to columns belonging to the "depots"
> table.
> This may be due to the fact you're mixing implicit and explicit joins
> in your query.

For some context here: the SQL standard specifies that JOIN binds tighter
than comma in a FROM list.  So the implied parenthesization of what you
wrote is (abbreviating the table names for convenience)

      FROM o, d, (c LEFT JOIN a ON (...))

and the ON-condition is only allowed to reference tables c and a.

You might have gotten this code, or at least this habit, from old versions
of MySQL, which got the SQL standard wrong and considered that comma and
JOIN bind equally strongly left-to-right, thus allowing an ON condition
placed like this to refer to o and/or d as well.  I'm fairly sure they
fixed that awhile ago, though.

If you really want to write in this style I'd suggest replacing the commas
by CROSS JOINs:

      FROM o CROSS JOIN d CROSS JOIN c LEFT JOIN a ON (...)

Now the JOINs all bind equally strongly left to right, so the effect is

      FROM ((o CROSS JOIN d) CROSS JOIN c) LEFT JOIN a ON (...)

and the ON condition can refer to any of the tables.

But, as Mael suggested, it might be better to think a bit harder about
exactly what you're expecting the LEFT JOIN to do, and swap the placement
around accordingly.

            regards, tom lane