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
