Re: 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