Sorry I didn't mention this before,
but I'm on CF 4.5.2...no Q of Q's available... :o(
Rick
> -----Original Message-----
> From: Ryan Duckworth [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, November 17, 2004 9:47 AM
> To: CF-Talk
> Subject: RE: How do I rewrite this as a join query?
>
>
> 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=708.628.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.290 / Virus Database: 265.3.1 - Release Date: 11/15/2004
--
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:184583
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
