smantscheff

If you are grouping rows, the non-grouped columns have to be aggregate functions. Otherwise MySQL will pick one row of each group at random (I believe the first one in historical table order). Consider those examples; [ICODE]drop table if exists newproducts; create table newproducts (id integer, groupcode integer, updated integer); …

Are you sure this is a database problem? How high is the load on your server with intensive DB operations (use "top" to find out)? Try to identify the slow queries (using the slow query log) and optimize them. Do you have all the indexes you might use?

Normalization means in effect that you store any piece of information exactly once. A standard report format may (and should) be based on such a normalization, but contains lots of repeated information and structures. It does not make sense to map a report format 1:1 to a database design scheme. …

Post a full test case with table creation and data insertion code together with your presumably erroneous query.

[ICODE]SELECT [U]DISTINCT[/U] tag_name FROM photoTagged WHERE file_id=" . $row1['id'][/ICODE] should give you unique tag_names. If they are not unique, chances are that they contain invisible characters like trailing spaces in which they differ from one another.

And for summing up the field contents use: [CODE]SELECT sum(field1) as s1, sum(field2 as s2 from t2 [/CODE]

Since MySQL does not allow LIMIT clauses in subqueries, this is no trivial task. If you can tackle the problem on the client side in a procedural language, it's not a problem. But if you want to have exactly one query, it gets tricky. I remember dimly to have solved …

RTFM. And change your syntax to [CODE]SELECT * FROM `details` WHERE BRAND = 'bmw' OR [U]BRAND = [/U]'audi'[/CODE]

I do not see how you might need Cygwin to run MySQL. The server and client programs for Linux and Windows have the same functionality. If your C program uses some shell commands, that's another story, but just for MySQL you don't need it.

Google for "mysql primer" or "mysql tutorial" and you'll find for example: [URL="http://www.webdevelopersnotes.com/tutorials/sql/mysql_primer_creating_a_database.php3"]http://www.webdevelopersnotes.com/tutorials/sql/mysql_primer_creating_a_database.php3[/URL] which seems to be a decent primer.

Maybe you mean something like this: [ICODE]drop table leagues; create table leagues (id integer, f1 enum('no', 'yes'), f2 enum('no','yes')); insert into leagues values (1,'no','no'), (1,'yes','no'), (2,'no','no'); select id, if(sum(if(f1 = 'no', 0, 1)) = 0, 'no', 'yes') as field1, if(sum(if(f2 = 'no', 0, 1)) = 0, 'no', 'yes') as field2 …

See to it that you have indexes on all relevant fields. Show your "CREATE TABLE" statements so that we can see which are missing. Is there an index on ptntPatientDetailsLang.ptntPatientID ? If not, add it.

In your FROM clause you ought to have a different alias name for each instance of [ICODE]users[/ICODE].

Where does [ICODE]last_post_datetime[/ICODE] come from? Is it a field in [ICODE]categories[/ICODE]? There is at least one error in your SQL: the join clause [ICODE]ON NP.thread_id = NT.category_id[/ICODE] cannot be correct. Does the error persist when you fix that? And what are your unexpected results?

You can limit the paged entries in a [ICODE]SELECT DISTINCT[/ICODE] clause like that: [ICODE]drop table if exists a; drop table if exists b; drop table if exists ab; create table a (id integer); create table b (id integer); create table ab (a_id integer, b_id integer); insert into a values (1),(2),(3),(4),(5); …

What syntax errors do you get? Do you get them also from the command line MySQL client? And do they persist if you delimit your statements with semicolons? Also I think you have to add an alias clause to [U]every[/U] sub-select which uses the same tables as the main select, …

I think you could code a stand-alone procedure and call this procedure from your different triggers. But you cannot define one trigger for multiple tables in MySQL. Maybe it's possible with an ugly hack. In MySQL 5.1 triggers are stored as external files. You might link several of those files …

I don't know about your C libraries, but the MySQL server works the same on Windows and Linux.

Well, do some testing. Apart from that it all depends of the data structure and your indexes. And your programming skills. With a bunch of properly designed tables and indexes I doubt that with a standard web application with at most a few 10.000 entries per table you will see …

So show us your query, show us the results and what is the problem with it. Besides there is a flaw in your table design. If "rushyards" (whatever that means) is a function of athleteid and week, and teamid is a function of athleteid and week, too, these two (rushyards …

You can create an indexed 4-character field and update it using a trigger.

Set all character set variables (database, tables, connections) to UTF-8.

Your syntax is wrong at the comma. Condition clauses can be combined with "AND" and "OR" in SQL. Try [CODE]SELECT uName, pwd FROM users WHERE uName = '$uName' [U][B]AND[/B][/U] pwd = '$pwd'[/CODE] Also look exactly where the MySQL error message tells that the error occurs. That's exactly the place: the …

How can an album have exactly one SongID? There are more than one songs on an album. Seems to be a database design issue. Do a mysqldump of your database and post it here.

Mainly unique key issues.

Don't use the sum() function: [CODE]SELECT planCode, handset, planName, RRP, simRebate, commission, subsidy, subsidy + simRebate + commission - RRP AS total FROM plans RIGHT JOIN ( vhandsetmatrix LEFT JOIN vodahandsets USING ( handset ) ) USING ( planNum )[/CODE]

When I try this I get the error message [ICODE]Unknown column 'user_id' in 'NEW'[/ICODE] Probably you got some field names wrong (user_id/userid). My test case: [CODE]drop table if exists users; create table if not exists users ( user_id integer not null primary key auto_increment, lat decimal (12,7) not null, lon …

Looks like your delimiter statement is not accepted. Maybe it's filtered by some library layer in between? Did you enter the code at the MySQL command line client?

What is a "dynamic string", what are "fixed data"? Show what you did and what didn't work, then maybe someone will be able to help you.

RTFM. And try [CODE]update upload set email=null where id=154; [/CODE]

First make sure that the MySQL full text search really fits your needs. More often than not it doesn't. If it does, work with triggers. Have your referential keys in InnoDB tables and the text data in MyISAM and have the latter updated by operations on the former (or vice …

Have a look at the function last_insert_id(). It retrieves the last automatically generated id [B]for this session[/B] so there has to be no worries about other updates sneaking in. And bracket your two inserts with "begin transaction" and "commit". Apart from that, it seems to me like bad design having …

Of course it's possible. Google for "c# mysql library" or the llke. But first you should code as much of your database setup and logic as possible outside of C#. Do it in plain MySQL with some scripts or from the command line and learn to use the database hands-on. …

Well, if you have defined already a primary key, you cannot have another one. What does [ICODE]show create table pqrimasterdetail[/ICODE] say? You can instead add a second unique key: [ICODE]ALTER TABLE pqrimasterdetail ADD [B]Unique[/B] (PQRIMasterID,Type,YEAR,PhysiciansID);[/ICODE]

[url]http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/[/url]

This problem does not occur when using other interfaces, e.g. the MySQL command line or Navicat or PHP connections. Therefore I assume that you have some locking logic in your C program which is responsible, not MySQL. Do you use 3rd party libraries which might contain the locking? What is …

My contribution is a test case which should have been prepared by you. It shows that your expectations differ from the actual results: [ICODE]drop table if exists tbl1; create table tbl1 (id integer, eid integer, quantity integer); insert into tbl1 values (1,3,2),(2,4,3); drop table if exists tbl2; create table tbl2 …

What you cannot do is use the limit clause in subqueries. This would be an obvious way to go, by MySQL does not yet support it. Here is another way to do it: [ICODE]drop table if exists t1; drop table if exists t2; create table t1 (id1 integer, date1 datetime); …

Show us your query, show us your tables structure, including the indexes. Use the "EXPLAIN" command to see which indexes are actually used and which just sit there and wait. Which engine do you use?

What has this to do with mysql?

Maybe you are using another character encoding in php than in phpMyAdmin. Try to submit [ICODE] SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8'[/ICODE] (substitute the actual character set value for 'utf8') before you send your query.

Looks to me to a Java question rather than MySQL. In MySQL you cannot pass arrays as query parameters. You would have to explode your array ['a','b','c'] to a concatenation of column names 'a,b,c' and build a query like "select a, b, c from x..."

Please show us your script to determine what is going wrong. And your first [ICODE]"CREATE TABLE categories"[/ICODE] statement cannot be correct. It references an index field (itemID) which is not in the table. Please submit the table structure as shown by [ICODE]"SHOW CREATE TABLE categories"[/ICODE] and not your edited version.

Are you sure you want 30 days? An alternative would be [CODE]SELECT * FROM forms WHERE today >= DATE_SUB( CURDATE(), INTERVAL 1 month)[/CODE]

Create unique IDs with auto_increment fields and add whatever prefix you want in your interface.

You could use the replace function: [CODE]SELECT name FROM series WHERE replace(name,'The ','') LIKE 'word%' ORDER BY replace(name,'The ','') [/CODE] This would also replace "The" if it is somewhere in the string. To avoid that (and with no pattern matching replace function in MySQL) you could either write a function …

You have to select into a variable to use the results in a procedure. [url]http://dev.mysql.com/doc/refman/5.1/en/select-into-statement.html[/url]

Please submit the table structure and some test data.

It's hard to tell without the table structure. How about: [CODE]select [B]P.prod_id, [/B]PA.proposal_id, PA.evaluator_ID, PA.Primary_Evaluation_Status_ID, PA.Secondary_Evaluation_Status_ID, U.user_Given_Name, U.user_surname, from users U, proposal_appraisal PA, [B]proposals P[/B] where PA.evaluator_ID = U.user_id [B]and P.proposal_id=PA.proposal_id[/B] group by PA.proposal_id, PA.evaluator_ID order by PA.proposal_ID, U.user_ID desc[/CODE]

[CODE]SELECT Proposal_ID, Mark, AVG(Mark) AS Average FROM proposal WHERE (Proposal_ID >= 931) GROUP BY Proposal_ID HAVING Average >= 20;[/CODE]