Thanks for the tip, Judah! The rewrite looks good and cfqueryparam's would be good, too.
I do use them now, but this was written about 4 years again and I didn't typically use them in queries at that time. But I do now! Rick > -----Original Message----- > From: Judah McAuley [mailto:[EMAIL PROTECTED] > Sent: Monday, September 08, 2008 2:03 PM > To: CF-Talk > Subject: Re: Why won't this query run in MySQL 5? > > I'd also suggest rewriting the query to use ANSI SQL-92 syntax to be more > explicit and likely have it be more portable across db engines and versions. > > <cfquery name="get_client" datasource="#dsn#"> > > select c.*, fg.*, e.* > from clients c inner join family_groups fg > on c.family_id = fg.family_id > inner join employers e > on c.employer_id = e.employer_id > where c.family_id = > (select family_id > from clients > where client_id = > '#session.client_id#') > and c.employer_id = > (select employer_id > from clients > where client_id = > '#session.client_id#') > and c.client_id = '#url.client_id#' > > </cfquery> > > And you should also use a cfqueryparam on the url.client_id just for good > habit. > > Cheers, > Judah > > On Mon, Sep 8, 2008 at 6:33 AM, Rick Faircloth <[EMAIL PROTECTED]>wrote: > > > Wow, Will! > > > > You were dead on! Once I changed the uppercase <CFQUERY> tags > > to lowercase <cfquery>, everything worked perfectly! See!... > > > > <cfquery name="get_client" datasource="#dsn#"> > > > > select c.*, fg.*, e.* > > from clients c > > inner join (family_groups fg, employers e) > > on c.family_id = fg.family_id > > and c.employer_id = e.employer_id > > where c.family_id = > > (select family_id > > from clients > > where client_id = > > '#session.client_id#') > > and c.employer_id = > > (select employer_id > > from clients > > where client_id = > > '#session.client_id#') > > and c.client_id = '#url.client_id#' > > > > </cfquery> > > > > No, actually, CF Developer and Marcus were on the right track. > > It seems to be a precedence issue. However, the solution wasn't > > parentheses around the on-and clause after the inner join, but > > rather around the tables specified for the inner join. > > > > I tried that out of desperation...it doesn't make as much as parentheses > > around the on/and clause, but it works. > > > > Thanks for the help, everyone! (Yes, you too, Will... :o) > > > > Rick > > > > > > > > > -----Original Message----- > > > From: Will Tomlinson [mailto:[EMAIL PROTECTED] > > > Sent: Monday, September 08, 2008 8:26 AM > > > To: CF-Talk > > > Subject: Re: Why won't this query run in MySQL 5? > > > > > > > Hi, all... > > > > > > > > I've just moved a website from a server running MySQL 4.1 to > > > > a server running MySQL 5 and now this query is giving me problems. > > > > > > > > > > I think the problem is in your uppercase <CFQUERY> tags. Heehhehe...:) > > > > > > Seriously though, I've encountered the same kind of problems between > > MySQL versions. Sorry I can't > > help > > > with your syntax problem. Just letting you know this isn't uncommon. > > > > > > Will > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312199 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
