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

On Wed, 12 Mar 2008 11:48:24 +0100
Martijn van Oosterhout <kleptog@svana.org> wrote:

> 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

one of the inner join doesn't have an on relationship.

As you could see in the other (longer) query I'm just trying to put in
the same row what would be

select 1,
    t.Name, m.Name, m.MethodID, m.Description
    from shop_commerce_shipmethods m
    inner join shop_commerce_shiptypes t on m.TypeID=t.TypeID
    where m.MethodID=1

+

select 1,
    t.Name, m.Name, m.MethodID, m.Description
    from shop_commerce_paymethods m
    inner join shop_commerce_paytypes t on m.TypeID=t.TypeID
    where m.MethodID=1

I don't want it to get it with a union since pay and ship are
associated.

The "natural" way to get them in one row would be to get the table
that link them shop_commerce_baskets as in the longer query.

But that query contains a lot of unions and loop etc... and I'd be
curious to see if fetching ShipMethodOnStockID, PayMethodOnStock, ...
in advance and using the above queries would make the query plan
simpler...

I'm attaching the query plan of the longer query since it is too
nested to just get pasted.

The scope of the "exercise" would be to avoid 2 scans of the
shop_commerce_baskets table.


--
Ivan Sergio Borgonovo
http://www.webthatworks.it