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

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.

The above should be the "optimised" version of a much longer query
that works:

select 0,
    st.Name, sm.Name, sm.MethodID, sm.Description,
    pt.Name, pm.Name, pm.MethodID, pm.Description
    from shop_commerce_baskets b
inner join shop_commerce_shipmethods sm on
sm.MethodID=b.ShipMethodOnStockID inner join shop_commerce_shiptypes
st on sm.TypeID=st.TypeID

inner join shop_commerce_paymethods pm on
pm.MethodID=b.PayMethodOnStockID inner join shop_commerce_paytypes pt
on pm.TypeID=pt.TypeID where b.BasketID=3
union
select 1,
    st.Name, sm.Name, sm.MethodID, sm.Description,
    pt.Name, pm.Name, pm.MethodID, pm.Description
    from shop_commerce_baskets b
inner join shop_commerce_shipmethods sm on
sm.MethodID=b.ShipMethodBackOrderID inner join
shop_commerce_shiptypes st on sm.TypeID=st.TypeID

inner join shop_commerce_paymethods pm on
pm.MethodID=b.PayMethodBackOrderID inner join shop_commerce_paytypes
pt on pm.TypeID=pt.TypeID where b.BasketID=3

I don't even know if it is worth to optimise the above till I'll have
a working comparison.

thx

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

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.

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

On Wed, Mar 12, 2008 at 4:48 AM, Ivan Sergio Borgonovo
<mail@webthatworks.it> wrote:

>  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

Could you get what you want by cross joining the first two tables?

On Wed, Mar 12, 2008 at 12:48:22PM +0100, Ivan Sergio Borgonovo wrote:
> On Wed, 12 Mar 2008 11:48:24 +0100 Martijn van Oosterhout <kleptog@svana.org> wrote:
> > 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.

In general that doesn't really matter!  For example these queries all
return the same results:

  SELECT * FROM a, b WHERE a.n = b.n;

  SELECT * FROM a INNER JOIN b ON a.n = b.n;

  SELECT * FROM a CROSS JOIN b WHERE a.n = b.n;

  SELECT * FROM a INNER JOIN b ON TRUE WHERE a.n = b.n;

In your case there are probably a couple of reasonable choices.  Because
they're all just inner joins, I'd use the old fashioned cross syntax:

  SELECT *
  FROM pay p, ship s, paytypes pt, shiptypes st
  WHERE p.typeid = pt.typeid
    AND s.typeid = st.typeid
    AND p.methodid = 1
    AND s.methodid = 1;

If you want to use JOIN syntax, you could do something like:

  SELECT *
  FROM
    pay  p INNER JOIN paytypes  pt ON p.typeid = pt.typeid,
    ship s INNER JOIN shiptypes st ON s.typeid = st.typeid
  WHERE p.methodid = 1
    AND s.methodid = 1;

Or you could do something completely different!


  Sam