Re: FROM + JOIN when more than one table in FROM

On Wed, Mar 12, 2008 at 11:40:18AM +0100, Ivan Sergio Borgonovo wrote:
> I'd like to make this query work
>
> select 1,
>     st.Name, sm.Name, sm.MethodID, sm.Description,
>     pt.Name, pm.Name, pm.MethodID, pm.Description
>     from
>     shop_commerce_paymethods pm,
>     shop_commerce_shipmethods sm
>
>     inner join shop_commerce_shiptypes st on sm.TypeID=st.TypeID
>     inner join shop_commerce_paytypes pt on pm.TypeID=pt.TypeID
> where sm.MethodID=1 and pm.MethodID=1
>
> I can make it work renouncing to one *t.Name changing the order of
> the FROM tables and skipping one join... but I can't have in one run
> all I need.

From my understanding of SQL join syntax, the above is parsed as:

FROM pm,((sm inner join st) inner join pt)

which means that pm isn't in scope when doing the inner join on pt.
Perhaps this would owrk:

FROM sm inner join st inner join pt inner join pm

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.