FROM + JOIN when more than one table in FROM

Поиск
Список
Период
Сортировка
От Ivan Sergio Borgonovo
Тема FROM + JOIN when more than one table in FROM
Дата
Msg-id 20080312114018.16195c80@webthatworks.it
обсуждение исходный текст
Ответы Re: FROM + JOIN when more than one table in FROM  (Martijn van Oosterhout <kleptog@svana.org>)
Список 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


В списке pgsql-general по дате отправления:

Предыдущее
От: Richard Huxton
Дата:
Сообщение: Re: migration of 7.4 to 8.1
Следующее
От: Martijn van Oosterhout
Дата:
Сообщение: Re: FROM + JOIN when more than one table in FROM