FROM + JOIN when more than one table in FROM

[Ivan Sergio Borgonovo <mail@xxxxxxxxxxxxxxx>]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general