Inner Join?
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
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
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
Import Notes
Resolved by subject fallback