From: | Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | FROM + JOIN when more than one table in FROM |
Date: | 2008-03-12 10:40:18 |
Message-ID: | 20080312114018.16195c80@webthatworks.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2008-03-12 10:48:24 | Re: FROM + JOIN when more than one table in FROM |
Previous Message | Tim Child | 2008-03-12 10:39:41 | Re: ERROR: text search configuration "pg_catalog.english" does not exist |