Re: [GENERAL] FROM + JOIN when more than one table in FROM

2008-03-13 Thread Sam Mason
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 <[EMAIL PROTECTED]> 
> 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

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


Re: [GENERAL] FROM + JOIN when more than one table in FROM

2008-03-12 Thread Scott Marlowe
On Wed, Mar 12, 2008 at 4:48 AM, Ivan Sergio Borgonovo
<[EMAIL PROTECTED]> 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?

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


Re: [GENERAL] FROM + JOIN when more than one table in FROM

2008-03-12 Thread Ivan Sergio Borgonovo
On Wed, 12 Mar 2008 11:48:24 +0100
Martijn van Oosterhout <[EMAIL PROTECTED]> 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

Unique  (cost=58.36..58.41 rows=2 width=884)
  ->  Sort  (cost=58.36..58.37 rows=2 width=884)
Sort Key: "?column?", name, name, methodid, description, name, name, 
methodid, description
->  Append  (cost=0.00..58.35 rows=2 width=884)
  ->  Subquery Scan "*SELECT* 1"  (cost=0.00..29.18 rows=1 
width=884)
->  Nested Loop  (cost=0.00..29.17 rows=1 width=884)
  ->  Nested Loop  (cost=0.00..23.33 rows=1 width=806)
->  Nested Loop  (cost=0.00..17.49 rows=1 
width=446)
  ->  Nested Loop  (cost=0.00..11.66 rows=1 
width=368)
->  Index Scan using 
shop_commerce_baskets_pkey on shop_commerce_baskets b  (cost=0.00..5.82 rows=1 
width=8)
  Index Cond: (basketid = 3)
->  Index Scan using 
shop_commerce_paymethods_pkey on shop_commerce_paymethods pm  (cost=0.00..5.82 
rows=1 width=364)
  Index Cond: (pm.methodid = 
"outer".paymethodonstockid)
  ->  Index Scan using 
shop_commerce_paytypes_pkey on shop_commerce_paytypes pt  (cost=0.00..5.82 
rows=1 width=86)
Index Cond: ("outer".typeid = 
pt.typeid)
->  Index Scan using 
shop_commerce_shipmethods_pkey on shop_commerce_shipmethods sm  
(cost=0.00..5.82 rows=1 width=364)
  Index Cond: (sm.methodid = 
"outer".shipmethodonstockid)
  ->  Index Scan using shop_commerce_shiptypes_pkey on 
shop_commerce_shiptypes st  (cost=0.00..5.82 rows=1 width=86)
Index Cond: ("outer".typeid = st.typeid)
  ->  Subquery Scan "*SELECT* 2"  (cost=0.00..29.18 rows=1 
width=884)
->  Nested Loop  (cost=0.00..29.17 rows=1 width=884)
  ->  Nested Loop  (cost=0.00..23.33 rows=1 width=806)
->  Nested Loop  (cost=0.00..17.49 rows=1 
width=446)
  ->  Nested Loop  (cost=0.00..11.66 rows=1 
width=368)
->  Index Scan using 
shop_commerce_baskets_pkey on shop_commerce_baskets b  (cost=0.00..5.82 rows=1 
width=8)
  Index Cond: (basketid = 3)
->  Index Scan using 
shop_commerce_paymethods_pkey on shop_commerce_paymethods pm  (cost=0.00..5.82 
rows=1 width=364)
  Index Cond: (pm.methodid = 
"outer

Re: [GENERAL] FROM + JOIN when more than one table in FROM

2008-03-12 Thread Martijn van Oosterhout
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   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while 
> boarding. Thank you for flying nlogn airlines.


signature.asc
Description: Digital signature