:) glad you got it working, kay. ~ dina
----- Original Message ----- From: "Kay Smoljak" <[EMAIL PROTECTED]> To: "SQL" <[EMAIL PROTECTED]> Sent: Tuesday, April 30, 2002 9:55 PM Subject: Re: join problem > 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 ______________________________________________________________________ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
