Re: FROM + JOIN when more than one table in FROM

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 <kleptog@svana.org> 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