Postgres Pro
Downloads
Home   >   mailing lists

Thread: Inner Join?

Inner Join?

From
jeff@emojo.com (Jeff Rhys-Jones)
Date:
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 "Site"."Name" as "SiteName", "Site"."Description" as
"SiteDescription",
 "Site"."DefaultStyle", "Site"."PageWidth",
 "Site"."Tel", "Site"."Fax", "Site"."Email", "Site"."Web",
 "Site"."UserRegistration", "Site"."UserApprovalRequired",
"Site"."DefaultSecurity", "Site"."GrantedSecurity",
"Site"."KeyWordMeta",
"Site"."DescriptionMeta", "Site"."WhatsNewTF", "Site"."RegIntro",
"Site"."RegConfirm", "AppUser"."Email" as "SecurityContactEmail",
"Site"."TimeZone", "Site"."Logo", "Site"."LogoWidth",
"Site"."LogoHeight",
"Site"."LogoBGColor", "Site"."Favicon", "Site"."PublishingTF",
"Site"."LastPublished", "Site"."MyMessageCodeField",
"Site"."ContactDisplay",
"Site"."SiteUIDCode"
    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.

Can anyone out there help us. I have Momjians book here but I'm stuck.

Many thanks,

Jeff

Re: Inner Join?

From
Tom Lane
Date:
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

RE: Inner Join?

From
"Jeff Rhys-Jones"
Date:
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