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) > >

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? Select S.EmailAddress from staff S Select CAL.EmailAddress from classroom_access_list CAL where CAL.EmailAddress not in ('#ValueList(emailList.EmailAddress, ',')#') Phillip b.

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 Rick Faircloth
8 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? > > >

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 fr

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?

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 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. SELECT S.EmailAddress FROM staff S SELECT CAL.EmailAddress FROM classroom_a

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