Does MySQL (4.0.20-standard) Allow Subqueries???
Im moving a development app (MySQL 5.0) to a different server which runs MySQL 4.0.20-standard. I am getting errors on queries that have subqueries such as...
SELECT id
FROM table1
WHERE id IN (
SELECT id FROM table1
)
Seems like such a simple query but it wont run? Anybody know the answer and what other limitations im going to experience going from version 5.0 down to 4.0.20-standard.
View Complete Forum Thread with Replies
Related Forum Messages:
Does MySQL (4.0.20-standard) Allow Subqueries
Im moving a development app (MySQL 5.0) to a different server which runs MySQL 4.0.20-standard. I am getting errors on queries that have subqueries such as... SELECT id FROM table1 WHERE id IN ( SELECT id FROM table1 ) Seems like such a simple query but it wont run? Anybody know the answer and what other limitations im going to experience going from version 5.0 down to 4.0.20-standard.
View Replies !
Last Id MySQL 4.1.21-standard-log
we have a very busy database server, and alot of insert is going on in it. but my program requires to get the last_inset_id. i did this using PHP mysql_insert_id(). now the problem is since there are alot of insert, i believe that mysql_insert_id() is getting the wrong id thus i am having duplicate id's in my other table. so linking my two tables through id is screwed up. id is auto_increment btw. can i specify the last_insert_id to only look at a specific database.table?
View Replies !
Any Way To Dump From Mysql 5.0 To 4.1.22-standard?
I have a MySQL 5.0 database that I have dumped to a flat file with: mysqldump -u username -p databasename > mydatabase.sql I have written a cgi perl script to import the dumpfile into a new table in an existing database. The script runs fine when I run it on the local host. But when I upload the same script and dumpfile onto a webserver that is running MySQL 4.1.22-standard, I get the following error when trying to import: DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101' at line 7 at mysqlimport.cgi line 24, <FH> chunk 1. I would have thought that a dumpfile would not be version dependent, but apparently it is. The database is very simple. - Is there any way to edit it so that it will import into the lower rev db? - Or is there a way to dump it into a lower rev format? - Or is there some other solution (other than upgrading the server)?
View Replies !
MySQL Version 4.1.21-standard Log In Help!
I am using phpmyadmin through my web host provider. I have set up my database and username with a password. I open phpmyadmin, open the SQL query window and try to log in with this mysql -h localhost -u myusername -p When I submit, I get this error: Error SQL query: mysql - h localhost - u myusername - p MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql -h localhost -u myusername -p' at line 1
View Replies !
Mysql Or Standard Sql Advanced Manual
I'd like to know from the experts on the group some good mysql or standard sql manuals (printed or online, and also some good sites) primarly for the query construction with complex joins and also for optimization and other arguments.
View Replies !
Help With MYSQL And PHPBB On Server 2003 Standard
Trying to get help with this situation. I have installed PHPBB, PHP (4X) and MySQL 4.1. This is how I started out. 1. Installed MYSQL - ran setup - did FULL install - and entered in new password when requested. MYSQL gave the green light when computer rebooted. Created new database with CREATE DATABASE XXX; Also installed ODBC from MYSQL site and created a "SITE DSN" for the ODBC 2. Installed PHP and verfied working and accessible 3. Installed PHPBB per the instructions on the site. Did the permissions as instructed on config.php and install.php 4. Came to the install screen. Entered the info and password that I did with MYSQL installation (Root was the username) NOW SAYS - CANNOT CONNECT TO THE DATABASE. I do not know what to do at this point.
View Replies !
Trying To Revoke User Privs Cpanel Phpmyadmin Mysql 4.1.21-standard
I have created a user (someone). Logged into cpanel as the main account if I try .... REVOKE ALL PRIVILEGES , GRANT OPTION from someone@localhost; I get"#1044 - Access denied for user 'myaccount'@'localhost' to database 'mysql'" Can anybody tell me if there is anything I can do / another way to accompish the creation of a new user but only allow them access to certain priviliges? e.g. I don't want them to be able to drop tables. If I do this - there is also an error...... GRANT DELETE , INSERT , SELECT, UPDATE ON myaccount_thedb.table_1 TO someone@localhost; ""SELECT,INSERT,UP command denied to user 'myaccount'@'localhost' for table 'table_1'"" Also updateing the user privs in cpanel gives no errors but does not work.
View Replies !
Subqueries In MySQL 4.0?
I've got the following query. I just found out that subqueries are not supported by MySQL 4.0... How would I replicate this to work with MySQL 4.0? SELECT DISTINCT CategoryID, tblCategory.Category FROM tblCategory INNER JOIN (SELECT Cat FROM ( SELECT tblCategory.Category AS Cat FROM tblEntries) UNION SELECT Cat FROM ( SELECT Category2 AS Cat FROM tblEntries) UNION SELECT Cat FROM ( SELECT Category3 AS Cat FROM tblEntries)) As A ON tblCategory.CategoryID = A.Cat; Table tblCategory CategoryID = index key Category = Text entry describing the Category Table tblEntries Category = key link to tblCategory Category2 = key link to tblCategory Category3 = key link to tblCategory Basically, I want a list of Categories that are used in tblEntries.
View Replies !
Subqueries To Mysql 3.x.x
I know that mysql 3.x.x does not support subqueries so i need your help with a query.. I need to make possible to search concurrently both fields. The user can insert only part of the word that is searching so i used the like '% keyword %'. in musql queries Now my tables are Table Titles: Title (mediumtext) AuID (integer) Table Authors: Author (mediumtext) Au_ID (integer) If i had support for subqueries then i would have done it easily select Title from Titles where Title LIKE '%user title entry%' and AuID= (select AuID from Authors where Author LIKE '%User author entry%') Now i don't have support for subqueries i need help for writing this query.
View Replies !
Having Trouble With Subqueries With Mysql 4.0.21
i'm trying to make this command run: select MAID from spez_MA where MAID = any(select MAID from spez_P_MA where P_ID = 1) on a mysql server 4.0.21. and i get an error #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '( select MAID from spez_P_MA where P_ID = 1 ) LIMIT 0, 30' at the command works using the same database in another mashine running mysql 4.1.22. where can i find the documentation of the correct syntax for 4.0.21? my sintax is according with http://dev.mysql.com/doc/refman/4.1/en/any-in-some-subqueries.html, but this is for 4.1, not for 4.0. this manual should be for versions 3.23 and 4.0 too.
View Replies !
Subqueries And DISTINCT From MySQL 5 To 4
Ugh... been working on a project for some time now and I'm just plain stuck and the SQL is getting over my head. I would LOVE any help you can give me. The following query works beautifully fast on my in house machine (mySQL 5) but when I brought it over to the live server (mySQL 4.1) the query takes about 30 seconds to compile... I realize I am not a SQL rock god... SELECT DISTINCT p.pailid AS PID, p . * , ( SELECT COUNT( * ) AS c FROM feed WHERE pailid = PID AND display =1 ) AS numFeeds, ( SELECT COUNT( * ) AS c FROM comments WHERE pailid = PID ) AS numComments, c.title AS cattitle, ( SELECT COUNT( * ) AS q FROM flags WHERE pailid = PID AND userid =1 ) AS isFlagged FROM pail p, feed f, category c WHERE p.pailid = f.pailid AND f.display =1 AND p.categoryid = c.categoryid ORDER BY p.date DESC LIMIT 0 , 50 Should I convert this to JOINS? If so, could someone give me a hand as I am LOST when it comes to JOINS.
View Replies !
Mysql Subqueries / Multiple Selects?
In MSSQL I can use subqueries to return a bunch of independant data with a single query. I am wondering if mysql has the same ability. I'm using > mysql 4.1, but I haven't seen any examples of this being used.
View Replies !
Emulating Subqueries With Temp Tables In MySQL 3.23.55
I just found out that the server I'm writing some code for only has MySQL 3.23.55 which unfortunately doesn't support subqueries (and dumb me I spent so much time trying to make them work thinking it was a syntax problem). So, I gotta find another way around. I think temporary tables are the way to go, but I can't find enough examples to put beside the manual to full understand them. Any pointer to some resources about this? Specifically what I have are two tables:
View Replies !
“Rewriting Subqueries As Joins For Earlier MySQL Version
In my portal I use MySQL version 4.1.7 where i can use subquerys but now i need to move my portal to server where is MySQL version 3.23.58. This SQL statement should convert to form where is NO subquerys and do it only ONE statement: select * from ajanvaraus where toimenpidetunnus in(select toimenpidetunnus from toimenpide where kategorianimi = (select distinct kategorianimi from toimenpide where toimenpidetunnus = $tp))"; I managed to convert inner subquery to form: select taulu2.toimenpidetunnus from toimenpide as taulu1, toimenpide as taulu2 where taulu1.toimenpidetunnus = $tp and taulu1.kategorianimi = taulu2.kategorianimi now i need to compine my last two querys togehter.
View Replies !
Standard Sql
i wanted to know if there is a standard way of limiting results in sql i've seen this page, and it seems there isn't http://dotnet.org.za/thea/archive/2005/02/22/14715.aspx just wanted that confirmed personally i like mysql's limit as it allows for offsets, and is nicely placed at the end of the query (but it obviously doesn't work for other db server's)
View Replies !
Learn Sql Standard
I'm just learning mysql--having no db experience. I'm wondering if my-sql-specific commands overlap with sql standard commands. I noticed when reading the mysql manual, that one of the user comments on the bottom of the page says that the "describe table" and "show tables" are mysql-specific. I also noticed that they use the non-standard "load data" command, where "insert" is the standard. I was under the impression that mysql-specific stuff was just an extension of the sql-standard, not an alternative. Are there a lot of overlaps like this? If so, do you recommend any other sites besides the mysql where I could learn the standard commands first?
View Replies !
Standard Deviation
I have Shows listed in a database (ShowID) and each show has several user ratings which are in a varchar field in the database called ‘rating’ which simply lists one of the verbal ratings: Highly Recommended Recommended Somewhat Recommended Not Recommended My problem is that I would like to convert each rating to a scale from 4 to 1 (Highly Recommended = 4, Recommended = 3, Somewhat Recommended =2, Not Recommended = 1) and then get the Standard Deviation of that list of numbers for each ShowID. Is that possible?
View Replies !
Database From 4.0.22-standard Into 3.23.58
If I have a backup of a Database created on MySQL 4.0.22-standard version (in a .SQL queries, that will re-create the tables and insert the data into them), and I changed server, and the MySQL version they use is 3.23.58, is it compatible, can I use the same backup queries ? (It uses the CREATE TABLE syntax and the INSERT INTO syntax.)
View Replies !
Downgrade V5 To 4.0.25-Standard?
I am in desperate need of being able to downgrade a MySQL v5 database to MySQL4.0.25-standard. vBulletin and my hosting said they can not do it with out loosing auto-incramnet and other variables, but my former hsoting was able to downgrade for me. Is the a program or tool I can use to downgrade while keeping everything intact?
View Replies !
Syntax Differences Between V. 4.0.23-standard And V.4.1.10
Basically i'm having trouble getting this to run on V. 4.0.23-standard, it runs fine on V.4.1.10. UPDATE leads SET keywordClicks = ( SELECT keywordClicks FROM googleinfo WHERE googleinfo.keyword = leads.refererKeyWords ) WHERE EXISTS ( SELECT keyword FROM googleinfo WHERE googleinfo.keyword = leads.refererKeyWords ) Is there any glaring syntac differences between the version, and if so is there a resource that documents them?
View Replies !
Standard DATE_FORMAT Query
I have a standard DATE_FORMAT query, for example: $query = "SELECT DATE_FORMAT(someDate, '%a, %b %e, %Y') AS someDate FROM table ORDER BY someDate"; My problem is that I need to nest another query within this one that has a WHERE clause based on the date: $anotherQuery = "SELECT * FROM someTable WHERE theDate = '$row[someDate]'"; Due to the odd formatting of the date in the previous query, this query doesn't return any valid results. I'm very new to these operations. Is there a way to format the date AND keep the proper mysql formatting? Any help is appreciated.
View Replies !
4.0.14 Standard Creating User Issues
We just installed mysql 4.0.14 on an xserve. When trying to add users via mascon I'm finding an issue I have not seen in previously releases. When adding a user it seems that the new column ssl_cipher is a required field. I know this is new for v4 but is it supposed to be a required field? What should the default be? I can't modify any user without being notified that it is required.
View Replies !
Difference Between Standard And Community Releases
I was wondering if somebody could tell me what the differences are between the Standard and Community releases, or point me to where the documentation is. I am looking for rpms for RHEL 4 x86_64. I require release 5.0.22 or greater and can only find community releases on the download page. MySQL-devel-standard MySQL-shared-standard MySQL-server-standard
View Replies !
Port 3306 Unless Standard Configuration
I've been trying figure out this problem for a while. Port 3306 won't forward unless I have standard configuration server installed when I'm trying to make a Detailed Configuration. That means port 3306 only opens up (yes I port forwarded and my ISP does not block 3306) when Standard Configuration is choosen.
View Replies !
Standard Char Length For Storing URLs?
I'm curious as to what the standard char length is when storing URLs into a database. Im sure 255 is way more than enough, but is that the safest bet? In my particuliar case the admin will be adding links possibly some generated from site such as yousendit or sendspace, but even those links seem to max at around 128 characters.
View Replies !
Subqueries On 4.0.25 Without Subqueries
i have 2 tables. knowing subqueries cant be performed on this version how would i write the following statement? i have 2 tables table.a, table.b I want to select all records from table.a that match table.a.id = table.b.id WHERE table.b.varchar = 'value' once the table.b.varchar is found, get table.b.id and match it to any records in table.a.id that match it. Can this be done in 1 statement. I'd hate to have to run a loop here. I've tried without success JOIN statements. here is one of my attempts: "SELECT table.a.* FROM table.a, table.b WHERE table.a.id = table.b.id AND table.b.varchar = '$value' GROUP BY table.a.sortkey ORDER BY table.a.is_primary DESC "; this does return 1 row, and joins the 2 tables accordingly, however i need all rows in table.a that match the table.b.id where table.b.varchar = 'value'
View Replies !
Use Of Subqueries With IN
I'm trying to do the following query: select * from table1 where col1 in (select col1 from table2); I keep getting a message to check my query after "in". But I've cut and pasted that query and it works. This is a very simple query so I don't understand why it doesn't work.
View Replies !
Subqueries With ALL
I got following statement: SELECT * FROM ip WHERE id <> ALL (SELECT ipid FROM pcip); Like I can see in the documentation, this should be correct, but SQL drops a SyntaxError near "ALL (SELECT ipid FROM pcip)" What it dows should be ovious, select all IPs that IDs are not noticed in the PCIP table under IPID The Tables are set up correct, means both tables exist and got values for the mentioned collums My MySQL version is 4.0.21 if that is part of the problem...
View Replies !
Subqueries For V3.23
An upgrade is definately coming, as some some SQL practice, but I'm trying to figure out how to do a subquery and not entirely certain where to turn to. From my limited understanding of SQL, what I want to do should look like: SELECT (SELECT count(SurveyData.Choice) WHERE SurveyData.Choice='0') as a, (SELECT count(SurveyData.Choice) WHERE SurveyData.Choice='1') as b, (SELECT count(SurveyData.Choice) WHERE SurveyData.Choice='2') as c FROM SurveyData The SurveyData table contains 3 fields: SID (Int), Name (Varchar 100), Choice (Int) I'd like to use it as the basis for a reusable/scalable web survey app (perl CGI), but all I'm getting is a syntax error.
View Replies !
Subqueries With NOT IN And <> ALL
I am having troubles with the following on 4.0.22: select * from question as q where q.id NOT IN (select distinct question from choice ); choice.question ---> question.id Apparently there's a syntax error, at (select distinct ....) <> ALL yields the same result.
View Replies !
Possible Without Subqueries
I'm trying to merge the result of 2 queries but I can't use subqueries. The first query gets all the sign-ups from a particular referer, the second query gets how many of those sign-ups are still active. So the first query does a count, group by ref and the second one does count where active group by ref - I know I could do this easily with subqueries but without them I'm lost.
View Replies !
Subqueries?
I'm using MySQL Control Center 0.9.2-beta. Will this take subqueries? I'm trying to extract data from tables in two different databases, and I'm not all familiar with JOINS. What is the syntax to do this in a subquery? In a JOIN?
View Replies !
Max() & Subqueries
Cant seem to get what i want Missing something so fundamental. Did it with subqueries on server at home just cant seem to get my head round it without using subqueries I have 3 tables ....
View Replies !
UNIONS In Subqueries
I have a problem using UNIONs inside subqueries. I have simplified my query to make it more readable. The question is about the right syntax.....
View Replies !
How To Rewrite This Without Subqueries?
i have a table that contains the fields order_id, canceled, and captured. the same order_id can appear many times in the table. i want to retrieve all the order_ids for which NO record exists that has both canceled and captured = N. i am not able to upgrade my version of mysql at the moment, so i would like to write a query that does that with no subqueries.
View Replies !
SQL, Count And Subqueries
I have one table that lists a number of courses available (including CourseID, CourseName and MaxPositions), with CourseID being auto_increment and unique I have another table which has CourseRegistration (Containing CourseID and CustomerID), with no unique fields. Customer information is stored elswhere. Without having a field in the Courses table holding the number of people currently registered, how would I return a result set which included: CourseID, CourseName, MaxPositions and the Number of people registered to take that course. Preferably this would not use features in MySQL 4.1, but anything is fair game at this point.
View Replies !
Theory Of Subqueries For 4.1.7
While converting SQL statements for a database change, I discovered a big performance hit in MYSQL with subqueries vices Sybase. I'm hoping that someone might be able to help me understand why? I have two tables USERS (2200 records) and JOB Decriptions (163 records). I wanted to retrieve all the job description not in the USER table. (No Indexes on JOBDESC currently) select JOBDESC from JOBS where JOBDESC not in ( select JOBDESC from USERS where JOBDESC not NULL ) In Sybase the query returns in about .56 secs In Mysql 4.1.7 query returns in about 8.78 secs The funny thing is if I run the query like so select JOBDESC from JOBS where JOBDESC not in ('President','Vice President','Treasaur','Secretary') returns in .03 secs. Those four descriptions are returned by the inner select. All select statements individually return in .03 secs. I created indexes on both tables for JOBDESC and reduced the time to ..97 secs. I used explain command to help understand what is going on hence the indexes, but why the difference in speed?
View Replies !
Removing Subqueries
the query i want execute is: select email from avalia where email in (select * from avalia where nome=´img/im1.jpg´) and email in (select * from avalia where nome=´img/im2.jpg´) but mysql 4.0 don't acept subqueries.
View Replies !
Optimization :: Subqueries
i have a little complex query that involves sub queries upto three levels. now thing is that , i think, mysql evaluates the sub queries every time that query is evaluated, whereas, i know that results for the third and 4th level queries are same for some number of queries. can we force mysql to store the result of the subqueries to be used later, instead of reexecuting the query.i studied mysql query optimization but, finally concluded is that things over there are just how mysql optimizes queries, not how can we optimize the query to be performed better. any resources for query optimization, i mean good resources ?
View Replies !
Subqueries And The IN Operator
hey all, i am having serious problems with getting my head around why this will not work. i am trying to return a number of games played with a count of the number of people in it. the table structures are as follows: games gameref | gamepot | gamedate | gamelength | ..1...... | .... 50.... | 12.12.04 | ... 03.00.00 | winnings gref | pref | amountwon | stake | 1 . .| 1 .. | ....20 ...... | 10...| i have used the dots to try and keep the table in some sort of form. i have only entered one row of data for each just to show the format of it, if this is not enough please ask for more. the games table holds each game that has been played, the total pot available to be won the date played and the duration of the game. the winnings table is teh winnings of each player in each game. the gref is the game it refers to, the pref is teh player it refers to, (there is a player table but is not used in this query at all). the amount that player won and the stake they entered into the pot. if ya didnt gues its a poker database. my problem is as follows. Quote: $sql = ("SELECT games.*, COUNT(winnings.gref) as playercount, winnings.gref FROM games, winnings WHERE gameref = gref group by gameref order by gamedate"); this query works a treat. it counts the amount of players in each game and outputs it all nicely.. but i wanted to edit the query to be able to show just all the games a single player had been in. Quote: $sql = ("select games.*, COUNT(winnings.gref) as playercount, winnings.gref from games, winnings where gameref in (select gref from winnings where pref ='$pid') group by gameref order by gamedate"); $pid is passed into the page by Quote: pt_register('POST','pid'); but this throws an error Quote: Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/ghoxmfjz/public_html/database/poker/allgames.php on line 32 Query failed! line 32 is: Quote: $nrows = mysql_num_rows($result); i can get it to work if i substitute the subquery for (1,2,3) being the first 3 games played. this would assume a certain player would have been in these 3 games. the problem i get is that the COUNT function just counts every player in everygame not the individuals. if i group it by gref it counts them (sort of.. it doubles the count) but then does not return any of the other information for the query. i am very confused any and all suggestions of things to look at or consider or a straight ur doing XXX wrong would be grand.. thx all Jon oh btw if you wish to look at the output if that helps the URL is: http://www.happygolucky.me.uk/database/poker/allgames.php with the variable $pid as the reference to try the player.
View Replies !
Subqueries Clarification
im using MySQL 4.1 the one the comes with XAMPP installer.. I have a question regarding correlated subqueries..in this version of MySQL, are correlated subqueries supported? as in like.. $query1= select field from table where field = 1; $query2= select field from table4 where field= 4; Insert into tmpTable ($query1,$query2)
View Replies !
SubQueries Prior
Does anybody know how to rewrite the following query to run on mysql 4.0.20. Is it possible to do this with JOINS? (I don't really have to join 2 tables... SELECT * FROM OAK_photos WHERE cat_id = (SELECT cat_id FROM OAK_photos WHERE id=$id) AND order_nr >= (SELECT order_nr FROM OAK_photos WHERE id=$id) ORDER BY order_nr ASC LIMIT 0 , 5 or should I run the 2 subqueries first as seperate queries and pass on the outcome via variables to the third query, the above main query?
View Replies !
Using CONCAT In Subqueries
SELECT tblmonths.fldMonth, tblyears.fldYear, tblmonths.fldID, CONCAT(tblmonths.fldID," ", tblyears.fldYear) FROM tblmonths, tblyears where CONCAT(tblmonths.fldID," ", tblyears.fldYear) NOT EXISTS (SELECT CONCAT(fldMonth, " ", fldYear) from tblexpensesclaims) GROUP BY tblyears.fldYear, tblmonths.fldID
View Replies !
Subqueries In The FROM Clause
I'm using 1&1 web hosting services with MySQL as database (Ver 14.12 Distrib 5.0.16, for pc-linux-gnu (i686) using readline 5.0). When I ran an example in Chapter 13.2.8.8 from MySQL 5.0 Reference Manual: CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT); INSERT INTO t1 VALUES (1,'1',1.0); INSERT INTO t1 VALUES (2,'2',2.0); SELECT sb1,sb2,sb3 FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb WHERE sb1 > 1; I always get the following error message: ERROR 1064 (00000): You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
View Replies !
Problem With Subqueries
I am trying to find the rows in table1 that do not have matching rows in table2 in order to set the value of a field in table1. I can get a query to return the rows that do have matches with this query: select item1 from table1 where item1 in (select item2 from table2); However, the following queries return no data: select item1 from table1 where not(item1 in (select item2 from table2)); select item1 from table 1 where item1 <> all(select item2 from table2); My understanding of the first query is that the not operation would be applied after evaluating whether item was in the result of the subquery. With the second query, I thought it should return true if item is not equal to every result in the subquery. If I try to use a left join and then simply find the rows that have a null value coming from table2, I get the correct resultset but I can't use that as a subquery for my update statement. Since table1 is what is being updated MySQL tells me that table1 cannot be used in the subquery for the update statement. I have this working in a stored procedure by importing the result of the join into a temporary table, but I'd rather not have to write all that data and then delete it.
View Replies !
|
TRACKER
