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(at)svana(dot)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