Обсуждение: 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