> 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.
**********************************************************************
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
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
