My apologies everyone, There were actually nine WebUserArchive records for the record that was appearing 9 times! So Dina's code works perfectly.
Thanks to everyone who replied, Kay. -----Original Message----- From: Kay Smoljak [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 1 May 2002 10:28 AM To: '[EMAIL PROTECTED]' Subject: RE: join problem Thanks Dina, Your solution is the first one I have managed to get working. However, there is still one problem - the first record (the only one that also has a corresponding record in the WebUserArchive table) is appearing 9 times in the recordset. I added a DISTINCT at the beginning of the query, but that's not making any difference. I am looping over the recordset and sorting the records into structures depending on what they hold, so I can simply filter out the duplicates on the ColdFusion end, but it would be nicer to have it all done in SQL. Any ideas? Thanks, Kay. -----Original Message----- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 30 April 2002 1:20 AM To: SQL Subject: Re: join problem kay, if you have not already resolved your query join, i believe the following should get the information you need: SELECT w.pkWebUser, w.Username, e.Email, l.USD, a.dtUpdated FROM dbo.WebUser w INNER JOIN dbo.Email e ON w.fkEmail = e.pkEmail INNER JOIN dbo.[Level] l ON w.fkLevel = l.pkLevel LEFT OUTER JOIN dbo.WebUserArchive a ON w.pkWebUser = a.fkWebUser WHERE (w.PartNum IS NOT NULL) ~ dina ----- Original Message ----- From: "Kay Smoljak" <[EMAIL PROTECTED]> To: "SQL" <[EMAIL PROTECTED]> Sent: Sunday, April 28, 2002 11:45 PM Subject: join problem > 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 ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
