RE: Inner Join?

Tom... you are totally right. We had version 7.03 installed - which
didn't support OUTER hence we couldn't figure this thing out!

Many thanks to all!

Jeff

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 11 July 2001 00:32
To: Jeff Rhys-Jones
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Inner Join?


jeff@emojo.com (Jeff Rhys-Jones) writes:
> Hi there - we've hit a bit of a brick wall with this and I was
> wondering if someone could help us out. Our MS-SQL code is this :

> SELECT ...
>     FROM "Site","AppUser"
>     WHERE "Site"."SiteCode"=1
>         AND "Site"."SecurityContactCode" *= "AppUser"."UserCode"

> And it's the "* =" at the very end of the table which is causing the
> problem.

> Our dev. guy has told me that "*=" in MS speak is INNER JOIN.

I believe it's actually an outer join; inner join is the standard and
wouldn't need any special syntax.  You probably want

SELECT ...
    FROM "Site" LEFT OUTER JOIN "AppUser" ON
        ("Site"."SecurityContactCode" = "AppUser"."UserCode")
    WHERE "Site"."SiteCode"=1

assuming that I've guessed right...

            regards, tom lane