Do you need to declare whether it's a left outer join or a right outer join?
At 01:46 PM 4/29/02 +0800, you wrote: >Thanks for your help Jeffry, > >I tried both, but I'm getting errors. With the second version I'm >getting "Incorrect syntax near the keyword 'outer'". Any ideas? It >*seems* like it should work... It is SQL Server 7 if that makes any >difference. > >Thanks again, >Kay. > >-----Original Message----- >From: Jeffry Houser [mailto:[EMAIL PROTECTED]] >Sent: Monday, 29 April 2002 1:21 PM >To: SQL >Subject: Re: join problem > > > I think you are really close. > It is late, and I'm not necessarily thinking right. > The webuserarchive may not exist. If it doesn't, nothing will be >returned when you perform an inner join (which is what you do) > > I'm a little rusty on the syntax, but.. > >SELECT DISTINCT > WebUser.pkWebUser, > WebUser.username, > Email.email, > [Level].USD, > WebUserArchive.dtUpdated >FROM > ( WebUser, > Email, > [Level], >WHERE > WebUser.PartNum IS NOT NULL >AND > WebUser.fkEmail = Email.pkEmail >AND > [Level].pkLevel = WebUser.fkLevel ) outer join webUserArchive >on > WebUser.pkWebUser =* WebUserArchive.fkWebUser > > > Which should return all the information you are asking for, and if it >exists the dtupdated field from the web user archive. It might be a >cleaner syntax if you were to set up the from clause like this: > >from (((WebUser Join Email on WebUser.fkEmail = Email.pkEmail) > Join [Level] on [Level].pkLevel = WebUser.fkLevel) > outer join webUserArchive on > WebUser.pkWebUser =* WebUserArchive.fkWebUser) > > > But, anyway.. that should be the general gist of it. > > > >At 12:45 PM 4/29/2002 +0800, you wrote: > >Hi all, > > > >I have the following SQL Server query (I've simplified it a little to > >save space): > > > >SELECT DISTINCT > > WebUser.pkWebUser, > > WebUser.username, > > Email.email, > > [Level].USD, > > WebUserArchive.dtUpdated > >FROM > > WebUser, > > Email, > > [Level], > > WebUserArchive > >WHERE > > WebUser.PartNum IS NOT NULL > >AND > > WebUser.fkEmail = Email.pkEmail > >AND > > [Level].pkLevel = WebUser.fkLevel > >AND > > WebUser.pkWebUser =* WebUserArchive.fkWebUser > > > >This gives me the following error message: > > > >The table 'WebUser' is an inner member of an outer-join clause. This is > > >not allowed if the table also participates in a regular join clause. > > > >What I am trying to do is pull out ALL the matching records in WebUser, > > >including the field from WebUserArchive if it exists (most WebUser > >records do not have a corresponding WebUserArchive record). Am I going > >about this the wrong way? > > > >Thanks, > >Kay. > >______________________________________________________ > >Kay Smoljak Web Developer PerthWeb Pty Ltd > > > >Level 9/105 St George's Tc - Perth - Western Australia > >Ph: (08) 9226 1366 Fax: (08) 9226 1375 > > > >www.perthweb.com.au developer.perthweb.com.au > > > > > > ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
