[Problem using SUM() with LEFT JOIN]

you can try this

 

SELECT SUM(budget_tot) AS budget_total, SUM(extrac_tot) AS extracost_total

FROM PROJECT, SBUDGETS, EXTRA_COSTS

WHERE extrac_proj_id = proj_id AND

budget_proj_id = proj_id AND

proj_id = 143

[MySql table name stored in php]

I am not sure why you have parentheses.

 

try this

$sql_command = \"SELECT * FROM \" . $tbl . \" WHERE id = \'$id\'\";

[relating problems]

create one more table that contains a mapping of client_ID to group_ID

[how to optimize queries in general?]

See -> ADOdb -> Making MySQL Efficient Tutorial

 

briefly scheme through it...

looks like a cool lib...will try out soon

thanks =)

[query is too long...can't send?]

I think that u mis-understand my question....

 

I only have one query, which is a select statment.

But the select statement is too long, it contains about 6000 characters, so it can\'t pass through...

[how to optimize queries in general?]

Have a look at this

 

http://mysql.progen.com.tr/doc/en/MySQL_indexes.html

 

http://www.databasejournal.com/features/my...10897_1382791_4

 

Hope that helps!

 

yea...indexing will help...but for each insert/delete, I have to pay for it since the index tree changes everytime I insert/delete. I am not sure whether it is worth...I will check with my database schema. Thanks.

[query is too long...can't send?]

Read this

 

http://www.mysql.com/doc/en/Batch_mode.html

 

If you are running mysql under Windows and have some special characters in the file that cause problems, you can do this:

 

dos> mysql -e \"source batch-file\"

 

hmm...it is the same as what I post \"source filename\".

what can I do if I want to use a php script?

[how to optimize queries in general?]

I\'m curious how people optimize their select statements if they are complex. MySQL 4.0 doesn\'t support nested queries(4.1 supports, but it is an alpha version), so many complex queries have to be broken down with some iterations in php scripts.

 

At the beginning I wrote a nested query and thought that it worked, after I ran it...I remembered that it was not a PostgreSQL DB....so I broke down the query into 3 queries with 2 loops.

 

However, the tables are too big(one has 200,000 rows, another has 20,000 rows, the last one has 6,000 rows), so the process takes about 2 hrs to complete on a LAN.

 

I just want to know, in general, how can I optimize select statements?

[query is too long...can't send?]

I have a 5000-character-long query.

I saved it in a textfile and sent it successfully in this way:

mysql> source myquery.txt

 

However, when I passed the query to the server in a php script, not the whole query was sent; only part of it was sent.

How can I fix it?