Re: How do I rewrite this as a join query?

2004-11-17 Thread Anders Green
At 09:24 AM 11/17/2004, Rick Faircloth wrote: 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

RE: How do I rewrite this as a join query?

2004-11-17 Thread Ryan Duckworth
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

RE: How do I rewrite this as a join query?

2004-11-17 Thread Rick Faircloth
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

RE: How do I rewrite this as a join query?

2004-11-17 Thread Rick Faircloth
MySQL didn't like that one...got a syntax error... Rick -Original Message- From: Anders Green [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 9:41 AM To: CF-Talk Subject: Re: How do I rewrite this as a join query? At 09:24 AM 11

RE: How do I rewrite this as a join query?

2004-11-17 Thread Cornillon, Matthieu (Consultant)
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? Well, here's a clumsy idea that gets the job done. Do an outer join (don't know the exact syntax for MySQL) that gets you two columns in each row: email address

RE: How do I rewrite this as a join query?

2004-11-17 Thread Rick Faircloth
and 700 respectively... Thanks! Rick -Original Message- From: Anders Green [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 9:41 AM To: CF-Talk Subject: Re: How do I rewrite this as a join query? At 09:24 AM 11/17/2004, Rick Faircloth

RE: How do I rewrite this as a join query?

2004-11-17 Thread Anders Green
At 10:40 AM 11/17/2004, Rick Faircloth wrote: I think your Union query worked, Anders. A la Stewie from the Family Guy cartoon: HA! Victory is mine! ;) Anders +===+ |Anders Green Email: [EMAIL PROTECTED] | |

Re: How do I rewrite this as a join query?

2004-11-17 Thread Phill B
I don't know much about MySql but can you do this? cfquery name=emailList Select S.EmailAddress from staff S /cfquery cfquery name=whatever Select CAL.EmailAddress from classroom_access_list CAL where CAL.EmailAddress not in ('#ValueList(emailList.EmailAddress, ',')#') /cfquery Phillip b.

Re: How do I rewrite this as a join query?

2004-11-17 Thread Keith Gaughan
Rick Faircloth wrote: 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