New topic: MySql Query
<http://forums.realsoftware.com/viewtopic.php?t=40259> Page 1 of 1 [ 8 posts ] Previous topic | Next topic Author Message JohnWheeler Post subject: MySql QueryPosted: Sat Aug 13, 2011 11:29 am Joined: Mon Apr 19, 2010 12:58 pm Posts: 92 Hi, I have three tables on MySql which I am trying to query, but the query I am using is from a Sql database. Of course I am getting an error from MySql and think that the syntax probably needs some alterations for use with MySql This is the query: Code: rs = db.SQLSelect ("select p.length pipe_length, t.diameter, d.rate from tube_diameters t, discharge_rates d,pipe_lengths p where p.id = d.pipe_id and t.id = d.tube_id and d.id =(select min(id) from discharge_rates where pipe_id =( select min (id) from pipe_lengths where length >= '" + plength + "' ) and rate >= '" + prate +"')") And no I am not that knowledgeable about sql, Simon actually put this together for me.I have been through the MySql documentation and it seems to also allow alias and joins like sql. Thanks Top Jym Post subject: Re: MySql QueryPosted: Sat Aug 13, 2011 11:37 am Joined: Sat Oct 01, 2005 5:19 pm Posts: 2528 What's the error that you are getting? I don't MySQL but do you need a semicolon somewhere? Top JohnWheeler Post subject: Re: MySql QueryPosted: Sat Aug 13, 2011 12:07 pm Joined: Mon Apr 19, 2010 12:58 pm Posts: 92 Hi, This is the error message: [Err] 1054 - Unknown column 'd.pipe_id' in 'where clause' I have tried the query in Navcat and phpMyAdmin, both give the same error. I have not seen any reference to a semi colon unless you were using it in php. This is from Real Studio using the MySql plugin. Top Bob Keeney Post subject: Re: MySql QueryPosted: Sat Aug 13, 2011 12:11 pm Joined: Fri Sep 30, 2005 11:48 am Posts: 2602 Location: Lenexa, KS It could be the table alias is different in MySQL Code:tube_diameters t, discharge_rates d would be Code:tube_diameters as t, discharge_rates as d If I'm reading http://dev.mysql.com/doc/refman/5.0/en/select.html correctly, you need to use "AS" for the table alias. _________________ Bob K. 30+ hours of Real Studio Video Training (including over 6 hours of Web Edition) at http://www.bkeeney.com/ Real Studio Consulting http://www.bkeeney.com/consulting/real-studio-consulting BKeeney Briefs Blog For Real Studio Developers http://www.bkeeneybriefs.com/ Top JohnWheeler Post subject: Re: MySql QueryPosted: Sat Aug 13, 2011 12:43 pm Joined: Mon Apr 19, 2010 12:58 pm Posts: 92 Hi, I am reading the same link and it seems to be optional: Code:A table reference can be aliased using tbl_name AS alias_name or tbl_name alias_name: SELECT t1.name, t2.salary FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name; SELECT t1.name, t2.salary FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name; Thankyou for helping. Top timhare Post subject: Re: MySql QueryPosted: Sat Aug 13, 2011 1:11 pm Joined: Fri Jan 06, 2006 3:21 pm Posts: 10058 Location: Portland, OR USA You do not need a semicolon. Start troubleshooting by simplifying the query and build it back up. Code:select pipe_id from discharge_rates select d.pipe_id from discharge_rates d select * from discharge_rates, pipe_lengths where pipe_lengths.id = discharge_rates.pipe_id select * from discharge_rates d, pipe_lengths p where p.id = d.pipe_id etc. Top DaveS Post subject: Re: MySql QueryPosted: Sat Aug 13, 2011 1:16 pm Joined: Sun Aug 05, 2007 10:46 am Posts: 3166 Location: San Diego, CA Code:rs = db.SQLSelect ( _ "select p.length pipe_length, t.diameter, d.rate from tube_diameters t, discharge_rates d, pipe_lengths p where p.id = d.pipe_id and t.id = d.tube_id and d.id =(select min(id) from discharge_rates X where X.pipe_id =( select min (id) from pipe_lengths Z where Z.length >= '" + plength + "' ) and Z.rate >= '" + prate +"')") I suggest ALWAY alias your tables (a,b,c,d etc.) and as such would reccomend adding an ALIAS to your last two Subselects..... so there is no confusion. (X and Z) and the way this reads..... the field PIPE_ID is NOT in table DISCHARGE_RATES as that is the only place you refer to D.PIPE_ID a semicolon is not required in this circumstance. _________________ Dave Sisemore MacPro, OSX 10.6.4 RB2009r5.1 Note : I am not interested in any solutions that involve custom Plug-ins of any kind Top JohnWheeler Post subject: Re: MySql QueryPosted: Sat Aug 13, 2011 1:19 pm Joined: Mon Apr 19, 2010 12:58 pm Posts: 92 Hi, Found it. Code: rs = db.SQLSelect ("select p.plength pipe_length, t.diameter, d.rate from tube_diameters t, discharge_rates d,pipe_lengths p where p.id = d.pipeid and t.id = d.tubeid and d.id =(select min(id) from discharge_rates where pipeid =( select min(id) from pipe_lengths where plength >= 19) and rate >= 3)") Code: rs = db.SQLSelect ("select p.plength pipe_length, t.diameter, d.rate from tube_diameters t, discharge_rates d,pipe_lengths p where p.id = d.pipe_id and t.id = d.tube_id and d.id =(select min (id) from discharge_rates where pipe_id =( select min(id) from pipe_lengths where plength >= 19) and rate >= 3)") The original query worked as expected in sql which is strange as the columns pipeid and tubeid had been set as pipe_id and tube_id, yet sql still found them, where as MySql would not hence the error message. Plus the min(id) function on the original query had a space min (id) which my other problem. Now it works fine. Thankyou Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 8 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
