Posts by smantscheff

Which indexes do you have?
You don't need the doubling of select clauses.
And do not select all columns (*) - it will slow things additionally.

Along those lines:

SELECT mt.emailid, sd.datesent,MAX(sd.datesent),sd.cusid
FROM the_main_table mt
LEFT JOIN sentdata sd
ON mt.emailid=sd.emailid
AND campaign_id = 'send2'
AND bademail = 0 
AND source IN ('source1','source2') 
AND oksend = 0

GROUP BY mt.emailid  
HAVING MAX(sd.datesent) = sd.datesent
ORDER BY MAX(sd.datesent) ASC 
LIMIT 50000

Have a look at EXPLAIN <yourQuery> for both cases. Maybe you can add indexes to speed up your LEFT JOIN query.

You can move the WHERE conditions into the left join clause to exclude the undesired rows. Why don't you prepare a nice little test case with complete table structures, test data and your query for us? It might clear things up.

That's correct. In a left join all values in the right table for which there is no matching record are NULL.

Order by the fields sent ASC and datesent DESC and use the first 500 rows.

You cannot have aggregate and non-aggregate columns from the same table in your query. MySQL does a bad job in not noticing this common error.
If you want the single reserve_rec items together with the total of shares, you need two table clauses in your FROM clause. Consider this:

drop table if exists my_members;
drop table if exists my_reserve_rec;
create table my_members (mem_id integer);
create table my_reserve_rec (mem_id integer, shares integer, expire_date datetime);


insert into my_members values (1),(2);
insert into my_reserve_rec values (1,2,'2011-01-01');
insert into my_reserve_rec values (1,3,'2012-01-01');
insert into my_reserve_rec values (1,4,'2012-01-01');
insert into my_reserve_rec values (1,5,'2012-01-01');

SET @expire_date = '2012-01-01';
SELECT m.mem_id, r.shares, 
(select(sum(shares)) from my_reserve_rec as r2 
    WHERE m.mem_id=r2.mem_id AND r2.expire_date >= @expire_date
) as sum_shares
FROM my_members as m, my_reserve_rec as r
WHERE m.mem_id=r.mem_id AND r.expire_date >= @expire_date;

A common error is the mixing of aggregating and non-aggregating functions in grouped selects. The select clause "child.*" will select all columns from child, and those which are not part of the "group by" clause will have arbitrary values. MySQL seems to return the values of the first row which it encounters for all non-aggregate columns. Therefore you will have to explicitly

SELECT child.nid, child.sid, max(rating) ...

to get the desired results.

Maybe you might use the INSERT IGNORE statement.
Generate a unique and reproducible discount code for customers with a certain amount of payments. Create a stored function for it (here called myCodeGenerator). Make the code column a unique key (maybe in conjunction with the customer id). Then use something like

INSERT IGNORE INTO discount_codes (id_customer,code)
  SELECT id_customer, myCodeGenerator(id_customer)
  FROM payments
  GROUP BY id_customer
  WHERE SUM(payment) > 100000

SELECT *
FROM tblpatient_pass p, tblpatient_info i
where p.RelationMR_no = i.MR_no
and username='$username'

Build a fast query, read it in rows into a PHP array and traverse the array in columns instead of rows to fit in your display logic.

To get a serious answer, submit a complete test case with all CREATE TABLE statements and some test data.
Until then, here a first try:

SELECT app_questions.quest_id, app_questions.app_question, app_questions.quest_type, app_questions.box_type, app_questions.quest_exception, 
app_answers.current_yr
max(app_answer.quest_answers),
FROM app_answers RIGHT JOIN app_questions ON app_answers.quest_id = app_questions.quest_id 
WHERE app_questions.step_desig='1' 
and  app_answers.chap_id = '357277'
and current_yr between 2010 and 2012
GROUP BY app_questions.sector_id, app_questions.quest_id, app_questions.quest_type, app_answers.current_yr

You will have to change your display logic with this query as you get one row for each year instead of columns for years, but it should return the same data as the original query.

Make sure that the order_id and prod_id which you try to insert are already in the orders and products table. If they are, show your test data to debug this.

But both MySQL and jsp have everything in UTF-8

And is this a problem?

I assume that you are working with php.
To feed your values into a database, use something like the following model and feed the output to mysql:

<?php
$x = 'a:5:{i:0;a:2:{s:6:"answer";s:2:"CA";s:5:"votes";s:1:"1";}i:1;a:2:{s:6:"answer";s:4:"CIMA";s:5:"votes";s:1:"1";}i:2;a:2:{s:6:"answer";s:4:"ACCA";s:5:"votes";s:1:"0";}i:3;a:2:{s:6:"answer";s:5:"CIPFA";s:5:"votes";s:1:"1";}i:4;a:2:{s:6:"answer";s:24:"MBA(specify in comments)";s:5:"votes";s:1:"1";}}';

$a = unserialize($x);
foreach( $a as $id => $entry )
	echo "insert into mytable (id, answer, votes) values ($id,'$entry[answer]', $entry[votes]);\n";

How do you set the caret? And how do you measure its position? Probably your display module counts one character per line break while mysql store CR+LF, or vice versa.

The last version of the function is in fact only a working prototype. Fore use in a production environment there should be precautions for i2 being NULL, the place markers should be parameters instead of literals (with guards against empty parameters) and there should be an optional parameter for case sensivity. But for quick and dirty admin work it's fine.

That's why the variable is called i1. With i2 the function reads:

CREATE FUNCTION cutQuote(t text) RETURNS text
    DETERMINISTIC
begin
        declare i1 integer;
	declare i2 integer;
        set i1 = locate( '[quote ', t );
        while (i1 > 0) do
                set i2 = locate( ']', t, i1 );
                set t = concat( substr( t, 1, i1 - 1 ), substr( t, i2 + 1));
                set i1 = locate( '[quote ', t );
        end while;
        return t;
end //

1) The lcoate function is case-sensitive. It won't find quote but QUOTE.
2) I made a mistake in the length of the first chunk which is cut from the text (bad cut and paste habits).
3) You mis-stated the length of the string to be cut.
Working sample:

drop function if exists cutQuote;
delimiter //
CREATE FUNCTION cutQuote(t text) RETURNS text
    DETERMINISTIC
begin
        declare i1 integer;
        set i1 = locate( '[quote ', t );
        while (i1 > 0) do
                set t = concat( substr( t, 1, i1 - 1 ), substr( t, i1 + 41));
                set i1 = locate( '[quote ', t );
        end while;
        return t;
end //
delimiter ;

select cutQuote('[quote author="caz128" date="1202738203"]I\'m exstatically enthusiastic about it.:coolgrin: 

I\'m sure that once those people who aren\'t so keen get used to the changes they\'ll come around 

The only thing I have missed so far is the little box that used to pop open and tell me I\'d got a new private message. Are there any intentions/possibilities of adding that back?[/quote]

Added to the possibilities list. During the design stage it was added temporarily in the top right hand corner but for some reason didn\'t sit comfortably. Will revisit.');

Result:

I'm exstatically enthusiastic about it.:coolgrin: 

I'm sure that once those people who aren't so keen get used to the changes they'll come around 

The only thing I have missed so far is the little box that used to pop open and tell me I'd …

Make sure that all parts work in the same character set - database, tables, fields, connection, html encoding, javascript encoding. The position differences most likely are due to one side counting the utf8 2-byte codes as one character, while the other side counts them as two characters.

Well, you should have at least a dim picture of what you are doing before you trust a total stranger to run code in your database. So don't run this code:

drop table phpbb_posts;

but instead

select posts_text, cutQuote(posts_text) from phpbb_posts

to see if the function works properly. And if it does, use the function in an update clause.

You seem to use an interface which fucks up the original line breaks (maybe phpMyAdmin?). Use the mysql command line client or HeidiSQL or Navicat instead.
Also, since you already created a function now, you will have to prepend your code with

DROP FUNCTION IF EXISTS cutQuote;

SELECT in a stored procedure does not display anything. Therefore you cannot use it for debugging.
In your 2nd sample you have a type: ANDA instead of AND.
campo is not the same variable as @campo.

When you code stored procedures you have to alter the standard delimiter. The standard delimiter is the semicolon which separates statements. You can change it with the "delimiter" statement, and then the code looks like:

delimiter //
CREATE FUNCTION cutQuote(t text) RETURNS text
    DETERMINISTIC
begin
        declare i1 integer;
        set i1 = locate( '[QUOTE ', t );
        while (i1 > 0) do
                set t = concat( substr( t, 1, i1 + 5 ), substr( t, i1 + 17));
                set i1 = locate( '[QUOTE ', t );
        end while;
        return t;
end //
delimiter ;

And don't use update before you have tested with a select that you get the desired results.

MySQL does not natively support regular expression replacements, therefore there is no built-in functionality for your problem.
You could either compile MySQL with regex support (there is an experimental plug-in somewhere on the net), or (more easily) write a function in MySQL which does the replacement.
The function would look like

CREATE FUNCTION cutQuote(t text) RETURNS text
    DETERMINISTIC
begin
        declare i1 integer;
        set i1 = locate( '[QUOTE ', t );
        while (i1 > 0) do
                set t = concat( substr( t, 1, i1 + 5 ), substr( t, i1 + 17));
                set i1 = locate( '[QUOTE ', t );
        end while;
        return t;
end;

Then use this function like in

update mytable set posts_texts = cutQuote(posts_texts);

Replace "sum" with "avg":

SELECT movies.movienum, movies.moviename, avg(reviews.ratingpoints) as ratings
FROM movies INNER JOIN reviews 
ON movies.movienum = reviews.movienum 
WHERE reviews.ratingpoints > 0
GROUP BY movienum

Must be a typo. ||, the OR operator, is by no means a number literal. It treats its operands as numbers, so one could argue that 'Student name' figures as a number literal here.

What is the error message? What is the table structure?

Hire a programmer.

job_name in table activity should be job_id.

select * from employee e, job j, activity a 
where e.emp_id = a.emp_id and j.job_id = a.job_id and a.date = <selected_date>
/* optional: */ and e.name = <selected_name>

But you will never learn anything from the homework of others.

It is not clear what you want. In your example, "moni" got lost. How would the result row containing moni look like?

So what is your question? Do you have any mysql code which does not work as expected? Then show it.

Show the row from the user table.

Use the character set clause in the load data command:

The character set indicated by the character_set_database system variable is used to interpret the information in the file. SET NAMES and the setting of character_set_client do not affect interpretation of input. If the contents of the input file use a character set that differs from the default, it is usually preferable to specify the character set of the file by using the CHARACTER SET clause, which is available as of MySQL 5.1.17. A character set of binary specifies “no conversion.”

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

You have to make sure that all components of the process of inserting your russian language strings use the same encoding. The database, tables and columns should be encoded with the utf-8 character set. Also your connection to the database - be it with the native mysql command line client, an access client like navicat or HeidiSQL, or a script language like PHP - has to set its connection character set properly. Only then the data are stored and retrieved as intended.

Wether you can connect to the mysql server depends on the combination of username, password and host entry in the mysql.user table. This table is not in your production database, but in a system database named mysql. If username and password match and the server from which the intruder operates matches the pattern in the mysql.user.host column, then the intruder can establish a connection. If this is sufficient to do any harm depends on the access rights which are granted to this user in the mysql access control tables.
For a quick test try to connect to your server from the outside using this admin password and see if you can access the database named mysql.

What are you trying? What is your code? What are the error messages?
If you since several months try to master the first steps of PHP/MySQL you should consider to take an introductory course to PHP/MySQL/HTML programming rather than wasting your time poking through the fog. You'll find plenty of those in the internet.

Yes.
I wonder, though, why you allow multiple payment_numbers. Make them unique and the primary key. In MySQL:

create table payment(
payment_number integer not null primary key,
payment_date date not null,
payment_amount float not null,
loan_number integer not null,
foreign key(loan_number) references loan(loan_number)
);

Show us the result of
EXPLAIN <yourquery>
for both cases.
Make sure that you have a query cache set up. If that does not help, you cannot speed up the query, but you can cache the results of SQL_CALC_FOUND_ROWS in a variable and display it until major changes happen to your data.

Set up a general query log. Cf. http://dev.mysql.com/doc/refman/5.0/en/server-logs.html

Locate your configuration file. It's usually called my.cnf and in /etc/mysql.
Locate a log-bin entry in my.cnf or add it, if it's not there. log-bin = <location of logfile> Restart the mysql server process.

@karthisathis: This is a sloppy solution. It relies on the fact that in mixing aggregate and non-aggregate function mysql takes the first encountered row for filling in the non-aggregate values. It will lead to incompatibility with other SQL dialects and leads the user to invalid assumptions about the results of queries with aggregate functions.

Does this mean that the date part is irrelevant and you're only working with the time part?

It's still not clear. How do you store the date? Do you store it at all?

That depends on the actual format you're using. If you're not keeping track of the date, it's simply

select * from mytable where timefield <= '08:00' or timefield >= '20:00'

First try if you can connect to the server using telnet:
telnet <HOST_IP> 3306
[replace 3306 with the actual port number of the mysql service; 3306 is the default]
If you cannot, you have to log into the server and probably change it's firewall's rules.

SELECT DISTINCT * FROM table WHERE date_add(`Date_Column`, interval 14 day) < now()

If you mix aggregate and non-aggregate functions in MySQL you get invalid results.
What you want is either:

select * from test
where (grp_code, root_desc) in
(select grp_code, root_desc from test group by grp_code, root_desc having count(grp_code) > 1)

+------------+----------+-----------+
| RIGHT_CODE | GRP_CODE | ROOT_DESC |
+------------+----------+-----------+
|          1 |        1 | 000001#   |
|         28 |        1 | 000001#   |
+------------+----------+-----------+

or

select * from test
where right_code in 
(select right_code from test
where (grp_code, root_desc) in
(select grp_code, root_desc from test group by grp_code, root_desc having count(grp_code) > 1)
);

+------------+----------+----------------+
| RIGHT_CODE | GRP_CODE | ROOT_DESC      |
+------------+----------+----------------+
|          1 |        1 | 000001#        |
|         28 |        1 | 000001#        |
|         28 |        2 | 000001#000001# |
+------------+----------+----------------+

Show some test data.
For using the IN clause you definitely don't need the group_concat function.

Not in a single query with general validity.
You could write a procedure which generates a CREATE TABLE statement with one column for each row of the original table and then execute this statement in a stored procedure. Or you could do it with a script language like PHP. But there is no query which returns columns for rows.
For this special case, you might query:

drop table if exists mytable;
create table mytable (id integer, data char);
insert into mytable values (1,'A'),(1,'B'),(1,'C');
select d1.ID, d1.Data as Data1, d2.Data as Data2, d3.Data as Data3
from mytable d1, mytable d2, mytable d3
where d1.ID=d2.ID and d2.ID=d3.ID
and d1.Data < d2.Data and d2.Data < d3.data ;

Get rid of the quote marks.

select 1 in (concat("'","1","'")); 
-- result: false
select 1 in (concat("'1'")); 
-- result: false
select (1 in ('1'));
-- result: true

And you don't need the group_concat neither:

select * from table_name where RIGHT_CODE IN 
(SELECT RIGHT_CODE 
 FROM table_name 
 GROUP BY `GRP_CODE` , `ROOT_DESC`
 HAVING count( `RIGHT_CODE` ) >=2
);

Alternatively sort by votes and use the first record:

select * from portfolio order by votes desc limit 1;