Philip: Your valid help has been well appreciated. I solved my problem with your suggestion Thanks a thousand friend Regards Mario
----- Original Message ----- From: "Philip Arnold - ASP" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, May 22, 2002 1:40 PM Subject: RE: SQL puzzle > > Down here > > tHE First query result is 267 > > tHE Second query result is 211 > > > > The Thirth and four query result is empty. > > > > My question is whyyyyyyyyyyyyyyyyyyyyyy??? > > > > Thanks friends in advance and forgive my desperate state. > > regards Mario > > Breaking this down > > The first returns more than the second because it's an OUTER JOIN - > therefore all records in EbethCustRpt3 that don't have entries in > tblUsers will be included, while the INNER JOIN will only return records > that are in both > > As for three and four, that's because you're using EXISTS wrongly, it > returns TRUE or FALSE, not a query... > If > SELECT tblUsers.nameFirst, > EbethCustRpt3.nameLast > FROM tblUsers > > Returns any results, then EXISTS returns true, you're using NOT EXISTS, > so you might as well put > SELECT nameFirst, nameLast > FROM EbethCustRpt3 > WHERE FALSE > > Not to mention that the third query, the SELECT is referring to a table > that isn't in the FROM - if you're only using one table, don't put the > table name in front of the field names > > What you might want to try is; > SELECT nameFirst, nameLast > FROM EbethCustRpt3 > WHERE nameFirst + ' ' + nameLast in > (SELECT nameFirst + ' ' + nameLast > FROM tblUsers) > > Although that might return some entries which are slightly wrong, so > changing the ' ' to something like '-+-+-' or similar to stop similar > names coming through > > Philip Arnold > Technical Director > Certified ColdFusion Developer > ASP Multimedia Limited > Switchboard: +44 (0)20 8680 8099 > Fax: +44 (0)20 8686 7911 > > www.aspmedia.co.uk > www.aspevents.net > > An ISO9001 registered company. > > ********************************************************************** > This email and any files transmitted with it are confidential and > intended solely for the use of the individual or entity to whom they > are addressed. If you have received this email in error please notify > the system manager. > ********************************************************************** > > > ______________________________________________________________________ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
