mysql-query

MySQL Select Records if OTHER records don't match

Hi I have a table with one record per person and per item of clothing so peter, jumper,blue peter,trousers,green sue,dress,green peter,jumper,red gordon,jumper,green I want to write a query to select all people with green jumpers but only if they have no other color jumper So in the above case it would ONLY select Gordon not greedy ...

Multiple Update

I'm trying to update two query's in my php script but i can't get it to work. The code is: UPDATE table SET price = '14.50' WHERE type LIKE 'finger', 'Knuckle' AND quantity <=50; Okay, ive fixed the WHERE clause but i don't understand what i need to change with the '14.50' part which is now picking up an error? ...

Select Mysql commutative sum of each row

I want to achieve by mysql query +------+---------+ | frq | -any- | +------+---------+ | 10 | 10 | | 15 |10+15=25 | | 15 |25+15=40 | +------+---------+ please help with code references, thanks ...

How do you select every n-th row from mysql

I have a series of values in a database that I need to pull to create a line chart. Because i dont require high resolution I would like to resample the data by selecting every 5th row from the database. ...

Summing values for an overview

I want get the time used for a case so I can create an overview. The SELECT query for the cases looks like this: SELECT bc.id, bc.title, bc.estimateCurrent FROM cases bc For one case I can get the used time like this: SELECT SUM(TIME_TO_SEC(TIMEDIFF(dateEnding, dateBeginning))) AS calculatedTime FROM timesheet WHERE `#case` = ? ...

How to select the last 24 hours of rows from a row offset

Im trying to compute a 24 hour rms (root mean squared) from a dataset stored in MySQL. I Need to grab the last 24 hours of points that occur within 24 hour period before a particular row offset. For instance if I wanted to compute 24hour rms for row id 1250 which has a timestamp of June 7th 2007 at midnight I would need to get all the po...

MySQL: how to write a query for a relationship that is based on a range, not a foreign key?

I have three tables in MySQL that are related, yet not technically linked to each other by a foreign key. They are: users, levels, and classes. The users table has a karma column, a numeric type. Based on this karma number, I want to know the user's level, which I retrieve from the levels table. This is not a hard relationship, since a...

mysql query

Hello, in mysql, how do you count the non empty cells in one row? And I want to count the cells between certain columns only, say between columns 3-10. not all the columns... again, in that row only. ...

MySQl Query Execution Plan

Hi, Does MySQL optimizer changes the execution plan based on the WHERE condition ? If I add one extra column for the comparison in WHERE condition , does it affect query execution plan ? Thanks, Sunil ...

Two tables of emails addresses, return where one is not in the other

I have a list of e-mails from one database which I want to check against a list of unsubscribed e-mails from another database. If they exist in both lists, then I don't want them returned. SELECT distinct `payer_email` as `email` FROM `database1`.`paypal_table` WHERE `payer_email` != (SELECT `email` FROM `database2`.`Unsubscribers` ...

Optimizing an embedded SELECT query in mySQL

Ok, here's a query that I am running right now on a table that has 45,000 records and is 65MB in size... and is just about to get bigger and bigger (so I gotta think of the future performance as well here): SELECT count(payment_id) as signup_count, sum(amount) as signup_amount FROM payments p WHERE tm_completed BETWEEN '2009-05-01' AND ...

SQL GROUP_CONCAT + SUM + AVG

SELECT *, GROUP_CONCAT(rating) AS rating_total, SUM(rating_total) AS rating_sum, AVG(rating_sum) AS rating_avg FROM ratings GROUP BY pid For some reason the sum and average don't execute....how do you make this statement work? ...

Get Auto Increment value with MySQL query

I currently have a database with over 6 million rows and growing. I currently do SELECT COUNT(id) FROM table; in order to display the number to my users, but the database is getting large and I have no need to store all of those rows except to be able to show the number. Is there a way to select the auto_increment value to display so t...

mysql select of rows involving csv

Hi, I have a table called txn in which a column called pid is a csv. I am using query in the following way: SELECT * FROM txn where pid like '%,11%'; ie.,if pid=,1,2,7,11,4 , then this particular row should be selected.. But my problem is..since i am using like and if pid=,1,111,112 or something like this,then even though it do...

Copy data from one field to another on every row

So I've got: id number 1 0 2 0 3 0 Is there a sql statement to copy everything from id into number? I'm about to write a php scrip to select, then update every row. My SQL knowledge is pretty basic, but I'm sure there's a smart guy way to to do this: Background: The id used to be a number that was displayed in...

MySQL query with join optimization

I got a query: SELECT a.nick,grp,count(*) FROM help_mails h JOIN accounts a ON h.helper=a.id WHERE closed=1 GROUP BY helper, grp, a.nick What is wrong with this join? When I made 2 queries: SELECT helper,grp,count(*) FROM help_mails h WHERE closed=1 GROUP BY helper, grp; SELECT nick FROM accounts WHERE id IN (...) It is 100 times fast...

Analyze execution time of queries on MySQL database?

I am using a MySQL database with myPHPadmin as the frontend (I am not sure I have remote/client access yet). I have a script that queries the database and would like to see how long each query takes? What is the easiest way to do this? Could I install another PHP app on the server? Thanks! ...

Is it possible to use a MySql User Defined Variable in a .NET MySqlCommand?

I'm trying to execute a query that currently works in phpMyAdmin but it does not working when executing it in .NET using the MySqlAdapter. This is the Sql statement. SELECT @rownum := @rownum +1 rownum, t . * FROM ( SELECT @rownum :=0 ) r, ( SELECT DISTINCT TYPE FROM `node` WHERE TYPE NOT IN ('ad', 'chatroom') )t It is using...

How do I build a query for this?

I have this table: +--------+---------+-----------+------------+ | bed_id | bed_num | place_id | bed_date | +--------+---------+-----------+------------+ | 1 | 150 | 1 | 1244344305 | | 2 | 250 | 1 | 1244345469 | | 3 | 145 | 2 | 1244348496 | | 3 | 146 | 2 | 124434849...

Problem in MySQLi query

This query produces error: 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 '' at line 1 Maybe becuase I'm tired, I am not findhing the problem here... mysqli_query($db, "INSERT INTO `tbl_bugresponse` (`id`, `bugid`, `by`, `content`, `time`) VALUE...