Without subqueries, I think it is easiest just to use CF's Query of
Queries.
This is close to what you need:
The trick is the last query.... It uses both queries to filter the
results.
<cfquery name="excludeStaffEmail" datasource="myDataSource">
SELECT S.EmailAddress
FROM staff S
</cfquery>
<cfquery name="allEmail" datasource="myDataSource">
SELECT CAL.EmailAddress
FROM classroom_access_list CAL
</cfquery>
<cfquery dbtype="query">
SELECT *
FROM allEmail OUTER JOIN excludeStaffEmail
WHERE allEmail.EmailAddress != excludeStaffEmail.EmailAddress
</cfquery>
Ryan Duckworth
Macromedia ColdFusion Certified Professional
Uhlig Communications
10983 Granada Lane
Overland Park, KS 66211
(913) 754-4272
-----Original Message-----
From: Rick Faircloth [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 17, 2004 8:25 AM
To: CF-Talk
Subject: How do I rewrite this as a join query?
Good morning, all...
I'm using MySQL (4.0) version, which doesn't support
subqueries, so I need to rewrite this as a join query...how would I do
that?
Select CAL.EmailAddress
from classroom_access_list CAL
where CAL.EmailAddress
not in (Select S.EmailAddress from staff S)
What I'm after is a query that gives me all the email addresses in the
classroom_access_list table and the staff table without duplicates...
I've been working on the join approach, but those things just
elude my logic for some reason...can't wait until I'm ready to move to
MySQL 4.1, which supports subqueries...
Thanks,
Rick
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.290 / Virus Database: 265.3.1 - Release Date: 11/15/2004
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184582
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
