Sebastian Hennebrueder <usenet@laliluna.de> writes:
> -- the following query has a inner join and an implicit join and does
> not work.
> select a.name as act_name,
> u.name as trainer
> from
> activity a,
> localgroup lg,
> sponsor spon
> inner join tuser u on a.fk_trainer=u.id
> where spon.name='Jimmy Rich'
> and
> spon.fk_localgroup=lg.id
> and lg.fk_activity=a.id
I think you were brought up on MySQL, which has only a vague
relationship to actual SQL :-(. Per the SQL standard, what you
wrote is equivalent to
... from
((activity a cross join localgroup lg)
cross join
(sponsor spon inner join tuser u on a.fk_trainer=u.id))
where ...
Notice the parenthesization. Table a is not part of the spon/u join
and so the reference to it from the spon/u ON condition is invalid.
MySQL, unfortunately, seems to render the above syntax as
... from
(((activity a cross join localgroup lg)
cross join sponsor spon)
inner join tuser u on a.fk_trainer=u.id)
where ...
in which case the reference from the ON condition is valid. Basically
they think that comma and JOIN in the FROM list have the same precedence
and associate left-to-right ... but any casual glance at the spec's
syntax rules shows this is wrong. JOIN is supposed to bind more tightly
than comma.
regards, tom lane