Re: Another cry for help..
Anders Norrbring skrev: Brent, you put me on the right track.. ;) Reading up a bit on syntax for variables, I came up with the following, which seems to work. SET @tid:=(SELECT tid FROM objects WHERE shortname = %s); SET @vid:=(SELECT vid FROM itemtypes WHERE itemtype LIKE %s); SELECT (count(*)+)/(SELECT COUNT(*) FROM score AS s2 WHERE s2.tid = @tid AND s2.vid = @vid) * 100 AS percentile WHERE s1.tid = @tid AND s1.vid = @vid AND s1.highScore (SELECT highScore FROM score WHERE tid = @tid AND vid = @vid AND uid = (SELECT uid FROM users WHERE username = %s)) Setting the vars with SET before doing the SELECT seems to do it.. Anders Hi.. I just want to take this one more round with you all.. Would the above be the easiest and most slim and efficient way to build the query? I'd rather avoid using views and temporary tables. Anders. Anders Norrbring skrev: Brent Baisley skrev: You might be able to use variables to store the result of the query. Although I've never tried assigning the result of a query to a variable, only field values. SELECT (count(*)+)/(SELECT COUNT(*) FROM score AS s2 WHERE s2.tid = @tid:=(SELECT tid FROM objects WHERE shortname = %s) AND s2.vid = @vid:=(SELECT vid FROM itemtypes WHERE itemtype LIKE %s) ) * 100 AS percentile WHERE s1.tid = @tid AND s1.vid = @vid AND s1.highScore (SELECT highScore FROM score WHERE tid = s1.tid AND vid = s1.vid AND uid = (SELECT uid FROM users WHERE username = %s)) Brent At a first glance, it doesn't work at all, I get NULL results from it, but I haven't spent any time trying to locate the problem yet.. So, I'm still open for ideas! On 12/20/07, Anders Norrbring [EMAIL PROTECTED] wrote: Hi.. I'm struggling with a query that I'm trying to simplify as much as possible, but I can't seem to get rid of using the very same subqueries several times. Would there be a way to optimize the following so I get rid of subqueries that do the exact same thing more than once? SELECT (count(*)+1) / (SELECT COUNT(*) FROM score AS s2 WHERE s2.tid = (SELECT tid FROM objects WHERE shortname = %s) AND s2.vid = (SELECT vid FROM itemtypes WHERE itemtype LIKE %s)) * 100 AS percentile FROM score AS s1 WHERE s1.tid = (SELECT tid FROM objects WHERE shortname = %s) AND s1.vid = (SELECT vid FROM types WHERE itemtype LIKE %s) AND s1.highScore (SELECT highScore FROM score WHERE tid = s1.tid AND vid = s1.vid AND uid = (SELECT uid FROM users WHERE username = %s)) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Another cry for help..
Hi.. I'm struggling with a query that I'm trying to simplify as much as possible, but I can't seem to get rid of using the very same subqueries several times. Would there be a way to optimize the following so I get rid of subqueries that do the exact same thing more than once? SELECT (count(*)+1) / (SELECT COUNT(*) FROM score AS s2 WHERE s2.tid = (SELECT tid FROM objects WHERE shortname = %s) AND s2.vid = (SELECT vid FROM itemtypes WHERE itemtype LIKE %s)) * 100 AS percentile FROM score AS s1 WHERE s1.tid = (SELECT tid FROM objects WHERE shortname = %s) AND s1.vid = (SELECT vid FROM types WHERE itemtype LIKE %s) AND s1.highScore (SELECT highScore FROM score WHERE tid = s1.tid AND vid = s1.vid AND uid = (SELECT uid FROM users WHERE username = %s)) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another cry for help..
Moon's Father skrev: Just a look at your sql query at first. Your like key word's right must like this: like 's%', then it'll use the index you created for your table. The second try you may use temporary table to replace your own query like (SELECT uid FROM users WHERE username = %s) Well, I am escaping the strings in sprintf in PHP, so the %s isn't an issue. Can't it be done without temporary tables? On Dec 20, 2007 4:23 PM, Anders Norrbring [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hi.. I'm struggling with a query that I'm trying to simplify as much as possible, but I can't seem to get rid of using the very same subqueries several times. Would there be a way to optimize the following so I get rid of subqueries that do the exact same thing more than once? SELECT (count(*)+1) / (SELECT COUNT(*) FROM score AS s2 WHERE s2.tid = (SELECT tid FROM objects WHERE shortname = %s) AND s2.vid = (SELECT vid FROM itemtypes WHERE itemtype LIKE %s)) * 100 AS percentile FROM score AS s1 WHERE s1.tid = (SELECT tid FROM objects WHERE shortname = %s) AND s1.vid = (SELECT vid FROM types WHERE itemtype LIKE %s) AND s1.highScore (SELECT highScore FROM score WHERE tid = s1.tid AND vid = s1.vid AND uid = (SELECT uid FROM users WHERE username = %s)) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another cry for help..
Brent Baisley skrev: You might be able to use variables to store the result of the query. Although I've never tried assigning the result of a query to a variable, only field values. SELECT (count(*)+)/(SELECT COUNT(*) FROM score AS s2 WHERE s2.tid = @tid:=(SELECT tid FROM objects WHERE shortname = %s) AND s2.vid = @vid:=(SELECT vid FROM itemtypes WHERE itemtype LIKE %s) ) * 100 AS percentile WHERE s1.tid = @tid AND s1.vid = @vid AND s1.highScore (SELECT highScore FROM score WHERE tid = s1.tid AND vid = s1.vid AND uid = (SELECT uid FROM users WHERE username = %s)) Brent At a first glance, it doesn't work at all, I get NULL results from it, but I haven't spent any time trying to locate the problem yet.. So, I'm still open for ideas! On 12/20/07, Anders Norrbring [EMAIL PROTECTED] wrote: Hi.. I'm struggling with a query that I'm trying to simplify as much as possible, but I can't seem to get rid of using the very same subqueries several times. Would there be a way to optimize the following so I get rid of subqueries that do the exact same thing more than once? SELECT (count(*)+1) / (SELECT COUNT(*) FROM score AS s2 WHERE s2.tid = (SELECT tid FROM objects WHERE shortname = %s) AND s2.vid = (SELECT vid FROM itemtypes WHERE itemtype LIKE %s)) * 100 AS percentile FROM score AS s1 WHERE s1.tid = (SELECT tid FROM objects WHERE shortname = %s) AND s1.vid = (SELECT vid FROM types WHERE itemtype LIKE %s) AND s1.highScore (SELECT highScore FROM score WHERE tid = s1.tid AND vid = s1.vid AND uid = (SELECT uid FROM users WHERE username = %s)) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another cry for help..
Brent, you put me on the right track.. ;) Reading up a bit on syntax for variables, I came up with the following, which seems to work. SET @tid:=(SELECT tid FROM objects WHERE shortname = %s); SET @vid:=(SELECT vid FROM itemtypes WHERE itemtype LIKE %s); SELECT (count(*)+)/(SELECT COUNT(*) FROM score AS s2 WHERE s2.tid = @tid AND s2.vid = @vid) * 100 AS percentile WHERE s1.tid = @tid AND s1.vid = @vid AND s1.highScore (SELECT highScore FROM score WHERE tid = @tid AND vid = @vid AND uid = (SELECT uid FROM users WHERE username = %s)) Setting the vars with SET before doing the SELECT seems to do it.. Anders Anders Norrbring skrev: Brent Baisley skrev: You might be able to use variables to store the result of the query. Although I've never tried assigning the result of a query to a variable, only field values. SELECT (count(*)+)/(SELECT COUNT(*) FROM score AS s2 WHERE s2.tid = @tid:=(SELECT tid FROM objects WHERE shortname = %s) AND s2.vid = @vid:=(SELECT vid FROM itemtypes WHERE itemtype LIKE %s) ) * 100 AS percentile WHERE s1.tid = @tid AND s1.vid = @vid AND s1.highScore (SELECT highScore FROM score WHERE tid = s1.tid AND vid = s1.vid AND uid = (SELECT uid FROM users WHERE username = %s)) Brent At a first glance, it doesn't work at all, I get NULL results from it, but I haven't spent any time trying to locate the problem yet.. So, I'm still open for ideas! On 12/20/07, Anders Norrbring [EMAIL PROTECTED] wrote: Hi.. I'm struggling with a query that I'm trying to simplify as much as possible, but I can't seem to get rid of using the very same subqueries several times. Would there be a way to optimize the following so I get rid of subqueries that do the exact same thing more than once? SELECT (count(*)+1) / (SELECT COUNT(*) FROM score AS s2 WHERE s2.tid = (SELECT tid FROM objects WHERE shortname = %s) AND s2.vid = (SELECT vid FROM itemtypes WHERE itemtype LIKE %s)) * 100 AS percentile FROM score AS s1 WHERE s1.tid = (SELECT tid FROM objects WHERE shortname = %s) AND s1.vid = (SELECT vid FROM types WHERE itemtype LIKE %s) AND s1.highScore (SELECT highScore FROM score WHERE tid = s1.tid AND vid = s1.vid AND uid = (SELECT uid FROM users WHERE username = %s)) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another cry for help..
Brent Baisley skrev: You're right that wouldn't work because you can't assign the query results to a variable. You want to put the variables in the query and assign them to the value of the field. ...WHERE s2.tid = (SELECT @tid:=tid AS tid FROM objects WHERE shortname = %s)... That should work better, or at least put you on a better track. I haven't checked this yet, but would it be better/smoother than putting the variables assignments in separate SET statements in front of the SELECT as I mentioned? Anders On 12/20/07, Anders Norrbring [EMAIL PROTECTED] wrote: Brent Baisley skrev: You might be able to use variables to store the result of the query. Although I've never tried assigning the result of a query to a variable, only field values. SELECT (count(*)+)/(SELECT COUNT(*) FROM score AS s2 WHERE s2.tid = @tid:=(SELECT tid FROM objects WHERE shortname = %s) AND s2.vid = @vid:=(SELECT vid FROM itemtypes WHERE itemtype LIKE %s) ) * 100 AS percentile WHERE s1.tid = @tid AND s1.vid = @vid AND s1.highScore (SELECT highScore FROM score WHERE tid = s1.tid AND vid = s1.vid AND uid = (SELECT uid FROM users WHERE username = %s)) Brent At a first glance, it doesn't work at all, I get NULL results from it, but I haven't spent any time trying to locate the problem yet.. So, I'm still open for ideas! On 12/20/07, Anders Norrbring [EMAIL PROTECTED] wrote: Hi.. I'm struggling with a query that I'm trying to simplify as much as possible, but I can't seem to get rid of using the very same subqueries several times. Would there be a way to optimize the following so I get rid of subqueries that do the exact same thing more than once? SELECT (count(*)+1) / (SELECT COUNT(*) FROM score AS s2 WHERE s2.tid = (SELECT tid FROM objects WHERE shortname = %s) AND s2.vid = (SELECT vid FROM itemtypes WHERE itemtype LIKE %s)) * 100 AS percentile FROM score AS s1 WHERE s1.tid = (SELECT tid FROM objects WHERE shortname = %s) AND s1.vid = (SELECT vid FROM types WHERE itemtype LIKE %s) AND s1.highScore (SELECT highScore FROM score WHERE tid = s1.tid AND vid = s1.vid AND uid = (SELECT uid FROM users WHERE username = %s)) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query help, please..
I'm looking at a situation I haven't run into before, and I'm a bit puzzled by it. I have this table structure: Table USERS: userid, class Table OBJECT: userid, class, result Now I want to query the database for a certain user's result in a specified class, which is very, very easy. No problems. But, I also want to find out the user's position relative to others depending on the result. So, if the specified user's result is the 9:th best of all of the users, I want to have a reply from the DB query that say he has position number 9. I really can't figure out how to do that... Somehow I have to make MySQL calculate the position based on the value in the result column. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with query...
I'm trying to set up a query, but I don't really get the result I expected, so can someone please help me out here? The query I've built is this: SELECT a1.username FROM accountuser AS a1 LEFT JOIN (freeaccounts AS f1, payments AS p1) ON (a1.username = p1.username AND p1.username = f1.username) WHERE a1.username LIKE 'cit%' AND a1.imp + a1.pp + a1.se + a1.auth != 0 AND (f1.free IS NULL OR f1.free = false) AND (p1.validdate UNIX_TIMESTAMP(NOW()) OR p1.validdate IS NULL) The accountuser table is always fully populated. The freeaccounts and payments tables are only occupied with the 'username' field if it's been used previously, so they may not contain any data. What I expect to get from the query is 'username' from the accountuser table when: The a1.username starts with 'cit', AND Any of a1.imp, a1.pp, a1.se or a1.auth is not 0, AND f1.free is either not populated or false. AND p1.validdate is either not populated, or the timestamp is before NOW. Everything seems to work except for the passed time check. If I set p1.validdate to a timestamp for something next week, the username is still returned in the result. Grateful for any hints... Anders. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SV: Help with query...
Hello where is the FROM for f1 table? Martin-- Why would there be any FROM for the f1 table? It's not needed in the result. Anyway, I've come up with a query that actually seems to do what I'm looking for; SELECT a1.username FROM accountuser AS a1 LEFT JOIN payments AS p1 ON (a1.username = p1.username) LEFT JOIN freeaccounts AS f1 ON (a1.username = f1.username) WHERE a1.username LIKE 'cit%' AND a1.imp + a1.pp + a1.se + a1.auth != 0 AND (p1.validdate UNIX_TIMESTAMP(NOW()) OR p1.validdate IS NULL) AND (f1.free = false OR f1.free IS NULL) Thanks for the hints, you who gave them.. ;) Anders. - Original Message - From: Anders Norrbring [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, October 14, 2007 7:13 AM Subject: Help with query... I'm trying to set up a query, but I don't really get the result I expected, so can someone please help me out here? The query I've built is this: SELECT a1.username FROM accountuser AS a1 LEFT JOIN (freeaccounts AS f1, payments AS p1) ON (a1.username = p1.username AND p1.username = f1.username) WHERE a1.username LIKE 'cit%' AND a1.imp + a1.pp + a1.se + a1.auth != 0 AND (f1.free IS NULL OR f1.free = false) AND (p1.validdate UNIX_TIMESTAMP(NOW()) OR p1.validdate IS NULL) The accountuser table is always fully populated. The freeaccounts and payments tables are only occupied with the 'username' field if it's been used previously, so they may not contain any data. What I expect to get from the query is 'username' from the accountuser table when: The a1.username starts with 'cit', AND Any of a1.imp, a1.pp, a1.se or a1.auth is not 0, AND f1.free is either not populated or false. AND p1.validdate is either not populated, or the timestamp is before NOW. Everything seems to work except for the passed time check. If I set p1.validdate to a timestamp for something next week, the username is still returned in the result. Grateful for any hints... Anders. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User quotas, how?
Hiya all! I'm curious about if there's a simple way to manage user quotas in a MySQL database? F.x. user one can have 10MB data in the database and user two can have a maximum of 5MB? Anders Norrbring -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User quotas, how?
Hiya all! I'm curious about if there's a simple way to manage user quotas in a MySQL database? F.x. user one can have 10MB data in the database and user two can have a maximum of 5MB? Anders Norrbring -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Send someone a database
- Subject: Send someone a database - - Dear All - - I want to send a MySQL database to a client by email. Which is the best - way - to do it? Michael, I usually just dump the database to a file, zip/rar/arj it and send as a file attach. Use mysqldump like this; mysqldump -u USER -h HOST.DOMAIN.XXX -p DATABASE_TABLE the_database.sql Anders Norrbring Norrbring Consulting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cronjob / rights problem.
I've set up a new MySQL server on a box with multiple IP addresses, and the SQL server only binds to ONE of these addresses. Also, I've been changing some user rights in the SQL setup, and now I get a cronjob error, related to user rights... My big problem is that I don't even know where I should start looking for it. The system and MySQL setup is in all other aspects the distributed versions from the SuSE Linux Standard Server 8 distribution, based on United Linux 1.0. The cronjob error mailed to me is the following: SCRIPT: clean_catman, OK. SCRIPT: clean_core, OK. SCRIPT: do_mandb, OK. SCRIPT: logrotate exited with RETURNCODE = 1. SCRIPT: ouput (stdout stderr) follows /usr/bin/mysqladmin: refresh failed; error: 'Access denied for user: '@localhost' (Using password: NO)' error running postrotate script Reload syslog service..done SCRIPT: logrotate --- END OF OUTPUT SCRIPT: slots, OK. SCRIPT: ouput (stdout stderr) follows psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? SCRIPT: slots --- END OF OUTPUT Can somebody please help me out here? If the /usr/bin/mysqladmin tries to use a user from localhost (any) then it fails, because localhost doesn't have access to the SQL server... What user should be granted access (and from where) to make this job work correctly? Anders Norrbring Norrbring Consulting Halmvägen 42 SE-691 48 Karlskoga SWEDEN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cronjob / rights problem - 2:nd try.
[Second try, the first one rendered a few hints that didn't help] I've set up a new MySQL server on a box with multiple IP addresses, and the SQL server only binds to ONE of these addresses. Also, I've been changing some user rights in the SQL setup, and now I get a cronjob error, related to user rights... My big problem is that I don't even know where I should start looking for it. The system and MySQL setup is in all other aspects the distributed versions from the SuSE Linux Standard Server 8 distribution, based on United Linux 1.0. The cronjob error mailed to me is the following: SCRIPT: clean_catman, OK. SCRIPT: clean_core, OK. SCRIPT: do_mandb, OK. SCRIPT: logrotate exited with RETURNCODE = 1. SCRIPT: ouput (stdout stderr) follows /usr/bin/mysqladmin: refresh failed; error: 'Access denied for user: '@localhost' (Using password: NO)' error running postrotate script Reload syslog service..done SCRIPT: logrotate --- END OF OUTPUT SCRIPT: slots, OK. SCRIPT: ouput (stdout stderr) follows psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? SCRIPT: slots --- END OF OUTPUT Can somebody please help me out here? If the /usr/bin/mysqladmin tries to use a user from localhost (any) then it fails, because localhost doesn't have access to the SQL server... What user should be granted access (and from where) to make this job work correctly? Anders Norrbring Norrbring Consulting Halmvägen 42 SE-691 48 Karlskoga SWEDEN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
