Re: arrg need help summing Colum's
- Original Message -
From: "Brian E Boothe" <[EMAIL PROTECTED]>
To:
Sent: Friday, April 14, 2006 5:09 AM
Subject: arrg need help summing Colum's
why cant i get this to sum > ???
$link = mysql_connect("localhost","root","goobers") or
die(mysql_error());
mysql_select_db("workorder", $link);
$result = mysql_query("SELECT SUM(`ElecRem`) AS total
FROM orders", $link);
//$total = mysql_fetch_row($result);
echo mysql_result($result); // outputs
total
//return $total[0];
echo mysql_error(); ?>
It would REALLY REALLY help if you gave us some idea why you think there is
anything wrong with this code. You haven't indicated how the code is
misbehaving or what statements, if any, are working correctly and which are
failing.
Are you successfully getting connected to the server? If yes, how do you
know?
Are you successfully connecting the database ('workorder')? If yes, how do
you know?
What happens when you run the query? You haven't given us any clue at all.
Does the statement work but return an incorrect answer? Does it fail with an
error message? If so, what is the error message?
Or is it the statement that computes 'total' that is failing in some way? If
so, what is wrong with it?
All you've done is given us a fragment of code without clearly identifying
the failing code or giving us any real symptoms, other than a remark about a
summing problem.
Without more information it is VERY hard to guess what might be wrong.
--
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.1/310 - Release Date: 12/04/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WHERE doesn'r works
- Original Message - From: "Tom Lobato" <[EMAIL PROTECTED]> To: Sent: Saturday, April 22, 2006 4:03 AM Subject: Re: WHERE doesn'r works From: <[EMAIL PROTECTED]> you really need to show your php code and the output from an echo of the update line so that people can try to spot what you're doing wrong. from what i've seen, people are basically just guessing at the issue with you saying "tried that, didn't work". updating mysql records via php code, with a where, works just fine. i.e., this isn't a php/mysql bug. rather there's something in your code that's not quite right. so, show your code, the table (definition and data) that you're trying to update, and output from appropriate echoing of statements and we can probably help you figure out your problem. Perfectly, I didnt show it before just for dont bore you with so many code =) The echo ouput: UPDATE clientes SET tipo='r', nome_fantasia='cc', estado='24' WHERE id = '5' Most of the time, when I see a column named 'id', it is defined as an integer. If _your_ 'id' column is defined as an integer, then the reason your WHERE clause is failing is very simple: Your WHERE clause is looking for all the rows where the 'id' value is a character-string containing '5', not the integer value 5. In other words, remove the apostrophes on either side of the 5 in the WHERE clause so that it says: WHERE id = 5 _not_ WHERE id = '5' and your WHERE clause will probably start working just fine. If your 'id' column is defined as a CHAR, VARCHAR or similar data type, then the apostrophes around the 5 are fine and there is some other problem. The php and db structure and data are attacheds. I don't see them in my copy of the email Two infos: 1) Im not a expert 2) The code is in the beggining of the development, so are too many uglyness yet =) Also, just a small note about English grammar since English doesn't appear to be your first language: contractions like 'didnt' and 'dont' should _always_ be spelled with apostrophes. In other words: use don't, not dont; use didn't, not didnt. Unfortunately, even some people who know only English are starting to spell contractions without the apostrophes but this is always wrong and makes the writer look illiterate. Obviously, we make allowances for those who are relatively new to English but I wanted you to know the right way to handle contractions. I assume you want to write English as well as you can so please don't copy the bad habits of English-speakers who don't have enough education or self-respect to spell their own language correctly. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please help, can not delete database
What makes you think the delete of the database failed? It looks like the message from the DROP command indicates that the database was dropped successfully. -- Rhino - Original Message - From: "Randy Paries" <[EMAIL PROTECTED]> To: Sent: Saturday, April 22, 2006 9:22 AM Subject: please help, can not delete database Hello, Not sure what is going on i have mysql Ver 12.22 Distrib 4.0.17, I have a database that i can not delete. i do this #mysqladmin drop billmax -u admin --password Enter password: Dropping the database is potentially a very bad thing to do. Any data stored in the database will be destroyed. Do you really want to drop the 'billmax' database [y/N] y Database "billmax" dropped But it does not do anything. I have tried restarting the DB and restarting the box as well Please help Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Find Most Recent Autoincrement Index Assigned???
- Original Message - From: "David T. Ashley" <[EMAIL PROTECTED]> To: Sent: Saturday, April 22, 2006 12:53 AM Subject: How to Find Most Recent Autoincrement Index Assigned??? I'm using PHP, and I sometimes INSERT new records in a table. MySQL assigns a new autoincrement int field on each INSERT ... nothing surprising there. It goes 1, 2, 3, etc. What query can I use to find out what value this int autoincrement assigned field was? I could of course SELECT based on what was just inserted, but that seems inefficient. Thanks for any help, RTFM? If you search the MySQL manual on "increment", you'll get several hits, one of which is "3.6.9 Using AUTO_INCREMENT". Here is the link: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html If you read that page, you should find your answer -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: please help, can not delete database
I'm putting this back on the list where it belongs; that enables everyone to help and to learn from the discussion, either now or in the future via the list archive. -- Ahh, so you've tried to re-create the database after it appeared to be safely dropped! You didn't say that in your note so I wanted to be sure you had done that much before writing the note. Have you looked in the MySQL log to see if it is reporting any problems with the DROP? If you are using INNODB, have you tried a SHOW STATUS INNODB to see if it reported any problems? -- Rhino - Original Message - From: "Randy Paries" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Sent: Saturday, April 22, 2006 11:04 AM Subject: Re: please help, can not delete database when i go back and try to create it , it says it already exisit? Randy On 4/22/06, Rhino <[EMAIL PROTECTED]> wrote: What makes you think the delete of the database failed? It looks like the message from the DROP command indicates that the database was dropped successfully. -- Rhino - Original Message - From: "Randy Paries" <[EMAIL PROTECTED]> To: Sent: Saturday, April 22, 2006 9:22 AM Subject: please help, can not delete database Hello, Not sure what is going on i have mysql Ver 12.22 Distrib 4.0.17, I have a database that i can not delete. i do this #mysqladmin drop billmax -u admin --password Enter password: Dropping the database is potentially a very bad thing to do. Any data stored in the database will be destroyed. Do you really want to drop the 'billmax' database [y/N] y Database "billmax" dropped But it does not do anything. I have tried restarting the DB and restarting the box as well Please help Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006 -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WHERE doesn'r works
- Original Message - From: "Tom Lobato" <[EMAIL PROTECTED]> To: Sent: Saturday, April 22, 2006 4:18 PM Subject: Re: WHERE doesn'r works From: "Rhino" <[EMAIL PROTECTED]> Most of the time, when I see a column named 'id', it is defined as an integer. If _your_ 'id' column is defined as an integer, then the reason your WHERE clause is failing is very simple: Your WHERE clause is looking for all the rows where the 'id' value is a character-string containing '5', not the integer value 5. In other words, remove the apostrophes on either side of the 5 in the WHERE clause so that it says: WHERE id = 5 _not_ WHERE id = '5' I tried this, but didn't work yet. I tried double, simple and no quotes, same problem: WHERE only works when executed directly in the mysql client, no from mysql api of the php. See the table creation: CREATE TABLE `clientes` ( `id` int(5) NOT NULL auto_increment, `tipo` char(1) default NULL, `razao_social` varchar(30) default NULL, Since your table definition says that 'id' is definitely an int, WHERE id = 5 _should_ work. But obviously, it doesn't. I'm not sure what to try next. It's possible that php is messing you up somehow but I don't know how to be sure; I don't know php so I don't know the problems that you can encounter with it. There is one thing slightly odd about your table definition: you have defined 'id' as int(5). Normally, I define a column like 'id' as int, not int(5). According to the manual, it is okay for you to have int(5) but I wonder if your problem is caused by the int(5)? Perhaps you could try changing the column definition from int(5) to int and see if the php code works after that? It shouldn't make any difference but you never know: perhaps this will solve the problem. If that doesn't work, you could try searching for bug reports involving int (or int(5)) columns; perhaps this is a known bug? If you don't find anything in the bug reports, perhaps you are the first to find this problem; in that case, you could create a new bug report. Maybe someone will be able to suggest a workaround. I'd be surprised if this is a bug though; it seems like very basic functionality that should have been debugged a long time ago. and your WHERE clause will probably start working just fine. If your 'id' column is defined as a CHAR, VARCHAR or similar data type, then the apostrophes around the 5 are fine and there is some other problem. The php and db structure and data are attacheds. I don't see them in my copy of the email Also, you can see the codes in... http://www.spalha.com.br/spalha/DB_code.html http://www.spalha.com.br/spalha/insert_client_code.html (thanks to GESHI project, http://qbnz.com/highlighter/index.php =) Two infos: 1) Im not a expert 2) The code is in the beggining of the development, so are too many uglyness yet =) Also, just a small note about English grammar since English doesn't appear to be your first language: contractions like 'didnt' and 'dont' should _always_ be spelled with apostrophes. In other words: use don't, not dont; use didn't, not didnt. Unfortunately, even some people who know only English are starting to spell contractions without the apostrophes but this is always wrong and makes the writer look illiterate. Obviously, we make allowances for those who are relatively new to English but I wanted you to know the right way to handle contractions. I assume you want to write English as well as you can so please don't copy the bad habits of English-speakers who don't have enough education or self-respect to spell their own language correctly. So I will have begin to pay the list =) Beyond mysql I learn English too? Well, thank you by the hint, I'll stay alive about this. No charge for the English pointers :-) I just want you to know the correct way to write things. I hope you'd do the same for me if I was trying to write Portuguese and made a consistent mistake :-) -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help for my jointure
First of all, I'm going to guess that English is not your first language and tell you that "jointure" is not the word normally to describe the process of combining two tables in a database: the word you want is "joining". Second, there are many kinds of joins and you haven't specified which kind you want to do. If you look in the MySQL manual, you will see that there are cross joins, inner joins, straight joins, natural joins, left joins, right joins, etc. You need to figure out which kind of join you want because your decision will affect the way you need to write your SQL. Third, the manual gives some information and examples on how to do joins. You haven't specified which version of MySQL you are using but if it is Version 5.0, the topic you want is http://dev.mysql.com/doc/refman/5.0/en/join.html. If you are using a different version, you can find the various editions on this page http://dev.mysql.com/doc/. Fourth, the manual does _not_ do a very good job of explaining the differences between the types of joins. This has been a known deficiency for some time and I am disappointed that this has (apparently) not been addressed yet. I wish I could suggest a good place to see a clear description of how the join types differ but I can't. Maybe someone else here has seen a decent tutorial on the differences between the types of joins However, if you plan to do an inner join, which is the kind most people do most of the time, your syntax will look like this: select id, conf from confs as c inner join conf_id as i on c.id = i.id where id != '101.33.55.123' If you need to do a different kind of join, please specify which kind you want to do and perhaps someone here can suggest the right syntax. -- Rhino - Original Message - From: "Patrick Aljord" <[EMAIL PROTECTED]> To: Sent: Tuesday, April 25, 2006 8:48 PM Subject: need help for my jointure I have a table confs like this: id int 5 auto_increment primary key; conf text; and another table conf_ip like this: id int 5 auto_increment primary key; conf_id int 5; ==>foreing key of confs ip varchar 150; I would like to select id, conf from confs where ip!='some val'; how can I do this? thanx in advance Pat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.6/323 - Release Date: 24/04/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.6/323 - Release Date: 24/04/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to find size of my database
It would be easier to help if you specified what you meant by "size". Do you mean the amount of space the database is using on your hard drive? Or the number of tables in the database? Or the number of rows in the tables? Or somethng else altogether? You should also mention which version of MySQL you are using; later versions have features and commands not found in earlier versions. Also, the size of a give database may differ from version to version of MySQL. -- Rhino - Original Message - From: "Shivaji S" <[EMAIL PROTECTED]> To: Sent: Tuesday, May 02, 2006 3:53 PM Subject: How to find size of my database Hi, is there any command to find the total size of mysql db. Regards, Shivaji. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: How to find size of my database
You still haven't said what you mean by "size"! Let me put it to you this way: what units would be used in the answer you want? In other words, would the answer look like: 1. 234,000 KB? 2. 2.75 million rows? 3. 321 tables? Or something else altogether? -- Rhino - Original Message - From: "Shivaji S" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Cc: Sent: Tuesday, May 02, 2006 4:33 PM Subject: Re: Re: How to find size of my database Hi Rhino, Thanks for the input. I mean to say the total Database Size ,that is to see my current Database Size.I am using 4.0.20 version Regards, Shivaji On Wed, 03 May 2006 Rhino wrote : It would be easier to help if you specified what you meant by "size". Do you mean the amount of space the database is using on your hard drive? Or the number of tables in the database? Or the number of rows in the tables? Or somethng else altogether? You should also mention which version of MySQL you are using; later versions have features and commands not found in earlier versions. Also, the size of a give database may differ from version to version of MySQL. -- Rhino - Original Message - From: "Shivaji S" <[EMAIL PROTECTED]> To: Sent: Tuesday, May 02, 2006 3:53 PM Subject: How to find size of my database Hi, is there any command to find the total size of mysql db. Regards, Shivaji. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Re: How to find size of my database
Daniel de Veiga has already answered you on how to determine the size of your database by using the file system and simply looking at the size of the physical files in your database. Another possibility is that you could use the SHOW TABLE STATUS command in MySQL. If you go to your MySQL prompt and select a database, then use the SHOW TABLE STATUS command, like this: set tmp; show table status like '%'; you'll find a column called Data_length which tells you "the length of the data file" for each table. If you simply add the size of each table in the database together, you should have the size of the whole database. Please note that I'm not sure how accurate my suggestion is; you might find that Daniel's approach gives you a better answer. I'm not sure if the Data_length column considers all the overhead that you might have with a table, such as index sizes, pointer sizes, unusable space, etc. Perhaps someone else can jump in with a better approach; I'm inclined to think it can't be quite as easy as I suggested. Also, even if the Data_length column gives an accurate answer for the size of a table, it is rather tedious to have to execute the SHOW TABLE STATUS command and then manually sum up the various sizes. I don't think you can simply execute an SQL query that does all the work for you, which is very unfortunate. It is entirely possible that there is a MySQL command that gives you the actual size of each database directly, although I didn't find it when I searched the manual. Again, perhaps someone with more administrative experience with MySQL can suggest a better approach. If not, perhaps we need to make a feature request of the MySQL people :-) This would appear to be a very useful command to create if it doesn't already exist! -- Rhino - Original Message - From: "Shivaji S" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Cc: Sent: Tuesday, May 02, 2006 5:12 PM Subject: Re: Re: Re: How to find size of my database Hi Rhino, sorry ,for my unclear reply. the size i meant is in KB or MB.. Regards, Shivaji On Wed, 03 May 2006 Rhino wrote : You still haven't said what you mean by "size"! Let me put it to you this way: what units would be used in the answer you want? In other words, would the answer look like: 1. 234,000 KB? 2. 2.75 million rows? 3. 321 tables? Or something else altogether? -- Rhino - Original Message - From: "Shivaji S" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Cc: Sent: Tuesday, May 02, 2006 4:33 PM Subject: Re: Re: How to find size of my database Hi Rhino, Thanks for the input. I mean to say the total Database Size ,that is to see my current Database Size.I am using 4.0.20 version Regards, Shivaji On Wed, 03 May 2006 Rhino wrote : It would be easier to help if you specified what you meant by "size". Do you mean the amount of space the database is using on your hard drive? Or the number of tables in the database? Or the number of rows in the tables? Or somethng else altogether? You should also mention which version of MySQL you are using; later versions have features and commands not found in earlier versions. Also, the size of a give database may differ from version to version of MySQL. -- Rhino - Original Message - From: "Shivaji S" <[EMAIL PROTECTED]> To: Sent: Tuesday, May 02, 2006 3:53 PM Subject: How to find size of my database Hi, is there any command to find the total size of mysql db. Regards, Shivaji. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to find size of my database
Thank you VERY much, Mark! Your reply is EXCELLENT and gives us all a lot of very useful information. This is the kind of information that should be in the MySQL manual. Paul DuBois, if you're reading this, please consider adding all of Mark's information to the manual! I think this reply also points to a definite need within the MySQL community, namely monitoring tools. After all, any decent administrator is going to want to know the size of his databases at some point. I don't follow the development of tools for MySQL but if there are no tools to monitor database size, I would imagine there is a definite market for such tools. After all, why should each of us independently re-invent the wheel? This seems like an opportunity for an entrepreneurial type to make some money serving a market. Or for people who have already developed monitoring tools to contribute them freely to the MySQL community. Thanks again, Mark! I know I will revisit your reply when I get around to doing proper monitoring of my MySQL databases when they finally go into production. -- Rhino - Original Message - From: "Mark Leith" <[EMAIL PROTECTED]> To: "Shivaji S" <[EMAIL PROTECTED]> Cc: "Rhino" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; Sent: Wednesday, May 03, 2006 9:20 AM Subject: Re: How to find size of my database On Wed, 03 May 2006 Rhino wrote : Daniel de Veiga has already answered you on how to determine the size of your database by using the file system and simply looking at the size of the physical files in your database. Another possibility is that you could use the SHOW TABLE STATUS command in MySQL. If you go to your MySQL prompt and select a database, then use the SHOW TABLE STATUS command, like this: set tmp; show table status like '%'; you'll find a column called Data_length which tells you "the length of the data file" for each table. If you simply add the size of each table in the database together, you should have the size of the whole database. Please note that I'm not sure how accurate my suggestion is; you might find that Daniel's approach gives you a better answer. I'm not sure if the Data_length column considers all the overhead that you might have with a table, such as index sizes, pointer sizes, unusable space, etc. Perhaps someone else can jump in with a better approach; I'm inclined to think it can't be quite as easy as I suggested. I'll jump in for you.. Indeed, you are right that Data_length that does not cover space allocated but unused (i.e space freed up by DELETE or UPDATE statements that is not released back to the filesystem, before an OPTIMIZE TABLE for example). There are other columns within the output however - Data_free and Index_length. Therefore to get the total allocated space to a database: SUM(data_length) + SUM(index_length) Total of actual data: (SUM(data_length) - SUM(data_free)) + SUM(index_length) Allocated but unused: SUM(data_free) Also, even if the Data_length column gives an accurate answer for the size of a table, it is rather tedious to have to execute the SHOW TABLE STATUS command and then manually sum up the various sizes. I don't think you can simply execute an SQL query that does all the work for you, which is very unfortunate. Unfortunately within 4.0 there is no way to do this with a SQL query. You can do this on 5.0 however. For example: SELECT s.schema_name, CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length)) /1024/1024,2),0.00),"Mb") total_size, CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),"Mb") data_used, CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),"Mb") data_free, IFNULL(ROUNDSUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free)) /((SUM(t.data_length)+SUM(t.index_length)))*100),2),0) pct_used, COUNT(table_name) total_tables FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema WHERE s.schema_name = "sakila" GROUP BY s.schema_name ORDER BY pct_used DESC\G *** 1. row *** schema_name: sakila total_size: 6.62Mb data_used: 6.62Mb data_free: 0.01Mb pct_used: 99.91 total_tables: 22 1 row in set (0.08 sec) It is entirely possible that there is a MySQL command that gives you the actual size of each database directly, although I didn't find it when I searched the manual. Again, perhaps someone with more administrative experience with MySQL can suggest a better approach. If not, perhaps we need to make a feature request of the MySQL people :-) This would appear to be a very useful command to create if it doesn't already exist! I have a little administrative experience ;) I'm going to confuse the matter now, as the
Re: Effective-dating
I don't think I noticed the original post for this discussion when it first
arrived - or maybe I did and mistook it for spam having to do with social
dating ;-) - but I just saw Sheeri's reply and want to jump in if I may.
Most professional databases in corporate environments that I have seen over
the years use the approach of having begin and end dates on each row. Those
dates indicate when the row is valid. For instance, if the row is recording
interest rates, the table would get a new row whenever the current interest
rate changed. Assuming no one knew when the interest rate would change
again, the new row would typically set the current date as the begin date
and the end date would be set to null. The row for the previous interest
rate would be updated at the same time and have its end date set to the
current date (or the day before in some cases).
Queries could easily determine the current interest rate by just finding the
only row in the table whose end date was null. Older interest rates could be
find by searching for the row whose begin date was on or before the search
date and whose end date was on or after the search date. Eventually, as
older interest rates were no longer desired for queries very often, you
might move them to archive tables with names like "IntRates_2005" and remove
them from the main interest rates table altogether.
I don't know enough about performance in MySQL to venture an opinion of how
this would perform. You'll need to research that yourself.
--
Rhino
- Original Message -
From: "sheeri kritzer" <[EMAIL PROTECTED]>
To: "Douglas Sims" <[EMAIL PROTECTED]>
Cc: "mysql List"
Sent: Thursday, May 04, 2006 3:15 PM
Subject: Re: Effective-dating
Coming to the table SOOO late. But this has special relevance as I'm
working on an application that stores event dates and therefore will
also need to solve this problem.
The biggest question I have is "What will this be used for?" My first
thought is to have at least 2 tables -- one table with the rows that
are "expired" and another with "non-expired rows".
In an events database, for example probaby 75% of the queries will be
current and future events. Folks will be interested in past events,
but usually as a part of a separate logical flow. Users may want past
events for research, but probably won't need to compare previous
events to current/future ones. (they might want to compare events all
in the past, for instance how many people attended each meeting,
average rating, etc; or they might want to compare current/future
events for conflicts. etc. But rarely both in the same query).
If you have reporting to do that might include both tables, you could
replicate them to MyISAM tables and make a MERGE table for your
reporting purposes.
Sorry this is so late; usually I get to MySQL list mail about once a
week, but the Users Conference took up a lot of my time! I hope this
helps
-Sheeri
On 4/10/06, Douglas Sims <[EMAIL PROTECTED]> wrote:
Does anyone know of a thorough discussion of effective dating on the
web, particularly with respect to MySQL, or have any opinions you
could share?
I've worked with effective-dated tables in MS SQL Server and never
been particularly awe-struck by how well it works. I can think of
three ways of doing it:
1) Store a "Begin" date and an "End" date for each row and then
reference the row with 'WHERE {transaction date} BETWEEN {begin} AND
END. This inevitably winds up with overlapping rows that shouldn't
be or gaps where you don't want them, and also requires an extra date
column, but the select queries are simplest. Also, what about
indexing the dates?
2) Store an "Expires" date with each row, but then to find the actual
row you have to do a subselect or some messy joins and I'm not at all
confident this will be optimized reasonably.
3) Store an "Effective as of" date with each row but this has
essentially the same problem as 2.
None of the SQL books on my shelf even mentions this, including
Jeremy Zawodny's "Hi-Performance MySQL" and the "MySQL Reference
Manual."
This page is interesting but doesn't explain the different options
nor try to analyze which is best and under what circumstances: http://
llamasery.com/forums/showthread.php?p=34945
Strangely enough, most of what I find by googling the topic
"effective dating" has to do with meeting girls efficiently - which
is also interesting, but outside the scope of this list and not
immediately relevant to the system I'm working on.
Douglas Sims
[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://li
Re: Output to a file
- Original Message - From: "Payne" <[EMAIL PROTECTED]> To: Sent: Friday, May 05, 2006 12:09 AM Subject: Output to a file Hey, been trying to output a select statment to a file, all the books I have only show how to input from a file, what is the correct way I thought I could do select * from my_toy >> `/tmp/my_toys` But I get an error. Here is a snippet from some documentation about MySQL which I wrote for myself. It shows a different technique for capturing output from a batch file into an output file; if the batch file contains 'select * from my_toy', it will capture the output in a file. It's not exactly what you want but maybe it will be "close enough". Running a script from OS prompt If you are connected to the database and are at an OS prompt, use this pattern: mysql < batch-file > output-file For example, if I want to run a script or batch file named my_batch_file.sql and write the output of the script to a file named my_batch_file.out, I'd need to do this: mysql < my_batch_file.sql > my_batch_file.out If you are NOT connected to the database, use this pattern: mysql -u username -p < batch_file > output_file For example, if your user name is 'fred' and your password is 'dino' and you want to run a script or batch file named my_batch_file.sql against database 'barf' and write the output to a file called my_batch_file.out, you'll need to do this: mysql barf -u fred -p < my_batch_file.sql > my_batch_file.out [Be sure to supply the password when prompted.] -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.4/332 - Release Date: 04/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PRINT statement?
- Original Message - From: "Stephen Cook" <[EMAIL PROTECTED]> To: "MySQL List" Sent: Sunday, May 07, 2006 3:53 AM Subject: PRINT statement? Is there a statement similar to PRINT in T-SQL (MicroSoft SQL Server)? It would be handy to debug some scripts. If you're talking about a script that is running SQL, you can simply use the SELECT statement without any FROM, WHERE, ORDER BY, GROUP BY or HAVING clauses. For example: select "Creating Foo table" as "Action"; will produce the following output: +--+ | Action | +--+ | Creating Foo table | +--+ 1 row in set (0.00 sec) If you're talking about an OS script, you can use OS commands to display things. For example, I have some BASH scripts on our Linux server so I can use the BASH echo command, like this: #!/bin/bash report_date=`/bin/date` echo "Report Date:" $report_date; to produce this output: Report Date: Sun May 7 09:42:57 EDT 2006 -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/333 - Release Date: 05/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to synchronize two databases
You may want to consider automating the synchronization of your databases via replication. Check out the Replication chapter in the MySQL manual (chapter 6 in the MySQL 5.0 manual). I don't know if Replication can cope with changes to the table structure - I've never played with Replication - but the manual should tell you whether this is a problem or is handled well by MySQL. -- Rhino - Original Message - From: "abhishek jain" <[EMAIL PROTECTED]> To: Sent: Sunday, May 07, 2006 11:41 AM Subject: How to synchronize two databases Dear Friends, I have two databases with the same name and table structure but the content is different, I need to synchronize them i mean the data inside one database need to be updated with the other one leaving the etries which are similar. Let me give a single example: DB1:table1 Rows like : userid name Ist row 1 abhishek IInd row 2 jain III row 3 rahul DB2:table1 Rows like : userid name Ist row 1 abhishek IInd row 2 amitabh III row 3 vijay Now i want like : Rows like : userid name Ist row 1 abhishek IInd row 2 jain III row 3 rahul IV row 4 amitabh V row 5 vijay NOTE: Here DB1 = database names , table1 = table name . How can i do that via mysql or that i need to prepare a script for this can anyone help. Regards, Abhishek Jain No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/333 - Release Date: 05/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/333 - Release Date: 05/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to convert strings to 'proper case' ?
I'd be surprised if things actually turned out to be quite as simple as you describe. For example, let's say that your column actually contained book titles or names of people. Would you really want to see any of the following in your column: - A Diplomatic History Of The Un? (more likely: A Diplomatic History of the UN) - A.b. Mcdonald? (more likely: A. B. McDonald) - The Life And Times Of King George Iii? (more likely: The Life and Times of King George III) In any case, I don't think a simple SQL UPDATE will do what you want to do, at least not very easily. You'll almost certainly want some real programming statements to do the string manipulation that you need. You haven't said whether your data is already in tables or whether you are planning to load the data into new tables. You also haven't said whether the data is in all upper case, all lower case or in some form of mixed case. If the data is not already in tables, I'd be inclined to change the case of the data with a scripting language that was appropriate for your operating system and then load the corrected data into the tables. For instance, on Linux, I might write a bash shell script to reformat the data, which is presumably sitting in a flat file somewhere, then load the reformatted data into the tables. This gives you the option of choosing from several different scripting languages, some of which you may already know fluently. That could save you a lot of time. If the data is already in tables, you could unload it to a flat file, fix it with a shell script, and then reload it to the database. Or, you could write a User Defined Function (UDF) or Stored Procedure (SP) in order to update the existing values. Then you could call the procedure or function to do the necessary work at any time you found data with the wrong case. If you write an SP, you could pass the table name and column name to the procedure. Then, the procedure could do a loop that operated on every value in that column of the table. For each row, it could read the existing value, create a revised value using string manipulation techniques, then update the current value with the revised value. Creating the revised value would likely be the only tricky part and even that might not be very hard if it really were only necessary to convert the first letter of each word to a capital. If the code actually had to handle more complex cases like the ones I put at the beginning of this note, the code would be more complicated; it might even be impossible if the language you were using for the SP or UDF didn't have many string manipulation techniques. In that case, you might need to choose a different language or you could go back to unloading the data from the database, manipulating it outside MySQL, and then reloading it. That's all I'm going to say for the moment but if you decide to try a UDF or SP and can state what programming languages you are willing to use for the code - and whether the conversion is really as simple as capitalizing just the first letters of the words - I might be able to give you more specific suggestions. Unfortunately, I don't have a current version of MySQL and can't really install one so I can only talk hypothetically, based on UDFs and SPs that I've written in DB2 and on what I've seen in the MySQL manuals. I can't actually write you a simple UDF or SP for MySQL that would do at least the basic parts of the conversion you want. Maybe someone else on this mailing list has an example that you could have which is actually known to work in MySQL. Otherwise, you might only have hypothetical guidelines and manual articles to guide you as you try to write your UDF or SP. That can be time-consuming if you've never done any coding like that before. But it could be fun too if you are in the right frame of mind! -- Rhino - Original Message - From: "C.R.Vegelin" <[EMAIL PROTECTED]> To: Sent: Tuesday, May 09, 2006 1:41 AM Subject: How to convert strings to 'proper case' ? Hi List, I want to convert strings to proper-case, where only the 1st char of each word is uppercase, such as: "This Is An Example." Any idea how to do this with MySQL 5.0.15 ? Thanks, Cor No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 08/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 08/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by leads to an empty set.
- Original Message - From: "Mohammed Sameer" <[EMAIL PROTECTED]> To: Sent: Tuesday, May 09, 2006 9:56 AM Subject: Order by leads to an empty set. Hi all, I have a strange problem and I can't really understand what's going on! mysql> SELECT n.nid, n.sticky, n.created FROM node n WHERE n.type='image' AND (n.uid = 1 OR n.status = 1) ORDER BY n.created desc; Empty set (0.00 sec) mysql> SELECT n.nid, n.sticky, n.created FROM node n WHERE n.type='image' AND (n.uid = 1 OR n.status = 1); +-+++ | nid | sticky | created| +-+++ | 73 | 0 | 1141048224 | | 75 | 0 | 1141736038 | . | 93 | 0 | 1145039899 | | 97 | 0 | 1145189131 | +-+++ 51 rows in set (0.00 sec) I'm using a standar drupal installation: mysql> desc node; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | nid | int(10) unsigned | | PRI | NULL| auto_increment | | type | varchar(32) | | MUL | || | title| varchar(128) | | MUL | || | uid | int(10) | | MUL | 0 || | status | int(4) | | MUL | 1 || | created | int(11) | | MUL | 0 || | changed | int(11) | | MUL | 0 || | comment | int(2) | | | 0 || | promote | int(2) | | MUL | 0 || | moderate | int(2) | | MUL | 0 || | sticky | int(2) | | | 0 || | vid | int(10) unsigned | | | 0 || +--+--+--+-+-++ 12 rows in set (0.00 sec) | version | 4.1.12 CentOS release 4.2 (Final) Any idea ? Am I doing something wrong ? Did I hit a bug ? If the two queries really are identical except that one has an ORDER BY added to it, this would appear to be a bug, although it would be a VERY strange one! But a bug isn't the _only_ possibility; in fact, I can think of three things that are probably much more likely. 1. Is there any possibility that a DELETE took place between the first query and the second? If the query without the ORDER BY returned 51 rows, then a DELETE executed by you - or someone unknown to you - removed all the rows, then the query WITH the ORDER BY executed, this would explain the behaviour you saw without any bug being involved. 2. Is there any possibility that the two queries took place against different tables or databases or systems? If the query without the ORDER BY ran against a version of the table that had 51 rows in it (perhaps the production version of the table) and the query with the ORDER BY ran against a different version of the table, maybe one that was empty (perhaps a test version of the table), this would explain the behaviour you saw without any bug being involved. This kind of thing could happen if you had two command prompts open and each was pointed at a different version of the table. If you were juggling several things at once, you might forget that the two prompts pointed at different systems and not realize that the queries had been done against different tables. 3. Is there any possibility that the query with the ORDER BY which you have given us in your email is not the one which returned 0 rows and that it is not identical to the other query that lacks the ORDER BY? The best approach for reporting query problems is to copy and paste the query from your MySQL environment into your email but some people simply type the query directly into the email. That opens the possibility that you typed the query inaccurately and may explain the problem. Frankly, I find it quite unlikely that ORDER BY would fail so I would strongly recommend that you consider the alternate scenarios I have suggested and rule those out first. If you can rule them out, then you may have encountered a real bug. Naturally, you should report that bug if you can satisfy yourself that it really IS a bug. -- Rhino Another -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 08/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sum of counts
- Original Message -
From: "Chris Sansom" <[EMAIL PROTECTED]>
To: "MySQL List"
Sent: Tuesday, May 09, 2006 11:47 AM
Subject: Sum of counts
Here comes a newbie question...
I want to get a total of entries from four tables which all match a
particular id. The result for the id I'm testing (21) should be 233. In my
naivety, I thought something like this would work:
select count(a.id) + count(b.id) + count(c.id) + count(d.guide_id)
from table_a as a, table_b as b, table_c as c, table_d as d
where a.id = 21 and b.id = 21 and c.id = 21 and d.id = 21
...but no - I get about eight and a half million! I tried fiddling about
with joins but got no better luck. In fact if I replace those +'s with
commas I get four equal values of about 2.12 million.
In the end I got the correct result like this:
select
(select count(*) from table_a where id = 21) +
(select count(*) from table_b where id = 21) +
(select count(*) from table_c where id = 21) +
(select count(*) from table_d where id = 21)
as total
Two questions:
1 Is this the best way to do it? If not, what is?
2 This is fine in MySQL 5 (on my development platform), but 3.23 (on the
live platform until the host upgrades us) doesn't support subqueries, so
as an interim measure is there any better way than doing four separate
queries and adding up the total in the PHP script?
The reason you are getting so many rows has nothing to do with the way you
are using the count(*) function and adding the different count() results
together. The problem is that you are doing your joins incorrectly. Whenever
you join tables, you need to specify what the tables have in common by
writing "joining predicates"; the number of joining predicates you usually
write is the number of tables being joined minus one. Therefore, since you
are joining four tables, you need three joining predicates: one to join the
first table to the second, one to join the second table to the third, and
one to join the third table to the fourth.
A joining predicate looks like this:
table1.col4 = table2.col3
In other words, there are two column names with an equal sign in the middle.
I think you are attempting to get the same result by saying "a.id. = 21 and
b_id = 21" (etc.) but this is not having the effect that you want. In your
case, I think you need to change the original query to this:
select count(a.id) + count(b.id) + count(c.id) + count(d.guide_id)
from table_a as a, table_b as b, table_c as c, table_d as d
where a.id = b.id
and b.id = c.id
and c.id = d.id
and a.id = 21
and b.id = 21
and c.id = 21
and d.id = 21
This says that:
- a row in table_a is joined to a row in table_b when the 'id' value in
table_a is identical (and non-null) to the 'id' value in table_b
- a row in table_b is joined to a row in table_c when the 'id' value in
table_b is identical (and non-null) to the 'id' value in table_c
- a row in table_c is joined to a row in table_d when the 'id' value in
table_c is identical (and non-null) to the 'id' value in table_d
- the final result should only have rows where the id columns in each of the
four tables contain 21.
The conditions you wrote, namely 'a.id = 21', 'b.id = 21', 'c.id = 21' and
'd.id = 21' are called "local predicates", i.e. conditions that affect only
one table. In your case, you've said that out of all the rows in the result
set after the joins have been done, you only want rows where a.id, b.id,
c.id, and d.id are 21.
Unfortunately, you don't have any joining clauses so MySQL joins EVERY ROW
of the first table to EVERY row of the second table and EVERY ROW of the
second table gets joined to EVERY row of the third table and EVERY row of
the third table gets joined to EVERY row of the fourth table. This is called
a Cartesian product and is usually considered very bad news because it gives
you huge result sets in which most of the rows are joined to rows to which
they shouldn't be joined. I think you wrote this query on the assumption
that your local predicates would ensure that only the correct rows were
joined but, as you can see, that isn't the case.
If you try the query I gave you, you should see that it only joins rows when
the ids are equal; then, the local predicates ('a.id = 21', etc.) ensure
that you only get the joined rows you want, namely the ones that contain
21.)
This is a very common beginner mistake. Unfortunately, the MySQL manual
doesn't yet explain how to do joins very well; this is something that is
badly needed, in my opinion.
--
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 08/05/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: comparing postgis with mysql
There used to be a page at the MySQL site which showed comparison between MySQL and its competitors. For example, one page contrasted MySQL with DB2. These pages were lengthy and compared the product feature for feature. I just had a look but I'm having trouble finding the comparison pages. I'm not sure if they have been removed or whether the site has been redesigned so that you can find things more easily - which usually results in it being _harder_ to find things, in my experience :-) Have a look for yourself; maybe you can find those comparisons. I'm not sure if postgis was one of the databases compared to MySQL though. -- Rhino - Original Message - From: "Parang Saraf" <[EMAIL PROTECTED]> To: Sent: Tuesday, May 09, 2006 4:06 PM Subject: comparing postgis with mysql hello, I am relatively new in this field. I am designing a database to store the events extracted from the oceans. This project later demands of publishing data on web. I am not able to decide which database to use. Mysql with spatial extension or the postgis one. I would prefer to use windows platform. Can someone suggest or can provide me with some links that compare the two databases. Do you think Mysql has a better spatial elements handling capacity in comparison to postgis.? please reply soon. Thanks and Regards Parang Saraf [EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 08/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 08/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sum of counts
Hi Chris, Joerg, and everyone else following this discussion, Joerg, you are correct; the best way to sum the tables is individually and then add the sums together with program logic of some kind, such as might be found in a script or application program or stored procedure. I'm afraid I jumped in and gave correct but irrelevant information. I saw that Chris's query lacked joining conditions so I explained why they were needed and how to write them. Unfortunately, this was premature: I should have thought about the basic problem more carefully first. Joerg, you are absolutely right: if one table contains students and another contains teachers, you don't count the number of people in the school by JOINING the tables together. You count the people in each table separately and add the two sums together. I don't know why that didn't come to me when I read Chris's question but it didn't. My apologies to all for wasting your time with an inappropriate solution. I'll try not to do that again! -- Rhino - Original Message - From: "Joerg Bruehe" <[EMAIL PROTECTED]> To: "Chris Sansom" <[EMAIL PROTECTED]> Cc: "Rhino" <[EMAIL PROTECTED]>; "MySQL List" Sent: Wednesday, May 10, 2006 12:04 PM Subject: Re: Sum of counts Hi Chris, all, Re-inserting Chris' original question: | I want to get a total of entries from four tables which all match a | particular id. The result for the id I'm testing (21) should be 233. | In my naivety, I thought something like this would work: | | select count(a.id) + count(b.id) + count(c.id) + count(d.guide_id) | from table_a as a, table_b as b, table_c as c, table_d as d | where a.id = 21 and b.id = 21 and c.id = 21 and d.id = 21 IMO, this is simply no task for a join, probably not a task for any single SQL statement. The easiest way is to have four separate "SELECT count(*) FROM table_?" with the '?' replaced by 'a' .. 'd'. More explanations below: Chris Sansom wrote: At 13:28 -0400 9/5/06, Rhino wrote: The reason you are getting so many rows has nothing to do with the way you are using the count(*) function and adding the different count() results together. The problem is that you are doing your joins incorrectly... In your case, I think you need to change the original query to this: select count(a.id) + count(b.id) + count(c.id) + count(d.id) from table_a as a, table_b as b, table_c as c, table_d as d where a.id = b.id and b.id = c.id and c.id = d.id and a.id = 21 and b.id = 21 and c.id = 21 and d.id = 21 First, the transitive equality on the 4 "id" columns (first 3 conditions) together with one restriction to 21 (say, on "a.id") has no different effect than the 4 conditions "= 21", in mathematical view 3 of these 7 conditions can be dropped. (Not "any 3", but several different combinations.) But that is not the cause of the problem - this is the join approach: Remember that a join does a cartesian product, this is in no way helpful to the solution of your task! Let us construct a minimized example: Just two tables, each with three rows, all having that magic value 21: Table aTable b id cntid cnt 21 1 21 4 21 2 21 5 21 3 21 6 Doing a natural join on the "id" column will yield 9 rows: a.id a.cnt b.id b.cnt 211 214 211 215 211 216 212 214 212 215 212 216 213 214 213 215 213 216 Summing "a.id" and "b.id" results in 18, where the correct value is 6. Hi Rhino Many thanks for the very full and frank response, but sadly it didn't work. I do understand exactly what you said, and I even took it further, adding in: and a.id = c.id and a.id = d.id and b.id = d.id ...so that every table is thus related to every other one, but I'm /still/ getting that damned eight and a half million instead of the 233 I expect! See above - demanding all 4 columns to be equal to 21 is equivalent to demanding one is 21, and all are equal, and also equivalent to some other combinations of conditions. If you have a mathematical education, apply your knowledge of "transitivity" to the problem. I'm baffled by this, though the version I did with subqueries works very nicely (and it's simple enough to do four separate queries and add them together in the script for the older MySQL). Frankly spoken: This is the way to go! From your problem description, there seems to be no connection between the tables that would warrant joining them. For a very coarse analogy: If you have separate tables for the teachers and the pupils of a school, and want to know the number of all peoples going there dai
Re: PRINT statement?
I am not familiar with the PRINT command so I don't know what it does. I played with MS SQL Server once for a couple of days a few years back and that is the only contact I've ever had with SQL Server. If you can tell me what PRINT does, in detail, maybe I can suggest another alternative. -- Rhino - Original Message - From: "Stephen Cook" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Cc: "MySQL List" Sent: Wednesday, May 10, 2006 8:09 PM Subject: Re: PRINT statement? I've started using the SELECT with no other clauses but I am still curious about a PRINT-like command. It is for SQL scripts. Rhino wrote: - Original Message - From: "Stephen Cook" <[EMAIL PROTECTED]> To: "MySQL List" Sent: Sunday, May 07, 2006 3:53 AM Subject: PRINT statement? Is there a statement similar to PRINT in T-SQL (MicroSoft SQL Server)? It would be handy to debug some scripts. If you're talking about a script that is running SQL, you can simply use the SELECT statement without any FROM, WHERE, ORDER BY, GROUP BY or HAVING clauses. For example: select "Creating Foo table" as "Action"; will produce the following output: +--+ | Action | +--+ | Creating Foo table | +--+ 1 row in set (0.00 sec) If you're talking about an OS script, you can use OS commands to display things. For example, I have some BASH scripts on our Linux server so I can use the BASH echo command, like this: #!/bin/bash report_date=`/bin/date` echo "Report Date:" $report_date; to produce this output: Report Date: Sun May 7 09:42:57 EDT 2006 -- Rhino -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 09/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 09/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PRINT statement?
". Therefore, if you drop the first SELECT (and its UNION), you will find that the column names of the result set are the values from the (new) first SELECT, i.e. "minimum_education_years=" and "@minimum_education_years", and, more importantly, that the width of the columns is too narrow and some of the information is truncated. For example the value shown for the second variable name is shown as "birthdate_of_youngest_le" and the VALUE of that variable is shown only as "1990", NOT the correct value, which is "1990-05-11". The danger is that it is not obvious that the value of the variable has been truncated. When I first encountered this, I thought I'd written the date_sub() function incorrectly and messed around with it for awhile before I discovered the truncation problem. Therefore, my technique is to always use the first SELECT to set the column names for the result set AND to control the width of the result set columns. -- Okay then, aside from the issue of string expressions, which I'm not sure about yet, I think we can see that SELECT can do everything else that the PRINT command supports. -- Rhino - Original Message - From: "Quentin Bennett" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]>; "Stephen Cook" <[EMAIL PROTECTED]> Cc: "MySQL List" Sent: Wednesday, May 10, 2006 11:59 PM Subject: RE: PRINT statement? From Transact-SQL Help file: PRINT Returns a user-defined message to the client. Syntax PRINT 'any ASCII text' | @local_variable | @@FUNCTION | string_expr Arguments 'any ASCII text' Is a string of text. @local_variable Is a variable of any valid character data type. @local_variable must be char or varchar, or be able to be implicitly converted to those data types. @@FUNCTION Is a function that returns string results. @@FUNCTION must be char or varchar, or be able to be implicitly converted to those data types. string_expr Is an expression that returns a string. Can include concatenated literal values and variables. The message string can be up to 8,000 characters long; any characters after 8,000 are truncated. -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Thursday, 11 May 2006 3:51 p.m. To: Stephen Cook Cc: MySQL List Subject: Re: PRINT statement? I am not familiar with the PRINT command so I don't know what it does. I played with MS SQL Server once for a couple of days a few years back and that is the only contact I've ever had with SQL Server. If you can tell me what PRINT does, in detail, maybe I can suggest another alternative. -- Rhino - Original Message - From: "Stephen Cook" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Cc: "MySQL List" Sent: Wednesday, May 10, 2006 8:09 PM Subject: Re: PRINT statement? I've started using the SELECT with no other clauses but I am still curious about a PRINT-like command. It is for SQL scripts. Rhino wrote: - Original Message - From: "Stephen Cook" <[EMAIL PROTECTED]> To: "MySQL List" Sent: Sunday, May 07, 2006 3:53 AM Subject: PRINT statement? Is there a statement similar to PRINT in T-SQL (MicroSoft SQL Server)? It would be handy to debug some scripts. If you're talking about a script that is running SQL, you can simply use the SELECT statement without any FROM, WHERE, ORDER BY, GROUP BY or HAVING clauses. For example: select "Creating Foo table" as "Action"; will produce the following output: +--+ | Action | +--+ | Creating Foo table | +--+ 1 row in set (0.00 sec) If you're talking about an OS script, you can use OS commands to display things. For example, I have some BASH scripts on our Linux server so I can use the BASH echo command, like this: #!/bin/bash report_date=`/bin/date` echo "Report Date:" $report_date; to produce this output: Report Date: Sun May 7 09:42:57 EDT 2006 -- Rhino -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 09/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 09/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.6/336 - Release Date: 10/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.6/336 - Release Date: 10/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Returning records in a circle
- Original Message - From: "Steffan A. Cline" <[EMAIL PROTECTED]> To: Sent: Friday, May 12, 2006 1:38 PM Subject: Returning records in a circle Is there a way I can get a set of records incrementally such as to get 2 then the next query get the next 2 then at the end of all records to get the 2 from the beginning? I need to keep going incrementally by 2 in a circle. Are you trying to get these rows purely via SQL at the command line or in an SQL script? Or would an application be an option for you? If you are not willing to consider application code to grab the rows you want, the answer to your question is "maybe". SQL has always been intended to return ALL of the rows that satisfy a query with a single invocation of the query, no matter how many rows that is. So if your query says: select * from mytab; you will normally get all of the rows that satisfy that query in one go, whether there are 0 rows, 100 rows, or a 100 million rows in the result. You _might_ be able to get the results you want by using the LIMIT clause. I'm not sure what version of MySQL you are using but the LIMIT clause is described in the MySQL 3.23/4.0/4.1 manual on this page: http://dev.mysql.com/doc/refman/4.1/en/select.html. The problem is that you'll still pretty much need some sort of script in order to keep executing the query to get the next two records and you may need to change the parameters of the LIMIT clause at the same time. If you are willing to write application code, things get a lot easier. For instance, a Java program could easily grab rows from a result set for you two at a time, let you process them, then grab two more, etc. I expect that it would similarly easy to do the same thing in Perl and PHP and C. In short, a program gives you a lot more ability to do what you want to do with your database data. But some shops have very little programming expertise and prefer to do everything via SQL. If you work for one of those shops, you might not be able to get your records two at a time with SQL alone, unless you can write a script that takes advantage of the LIMIT clause. I don't pretend to know MySQL exhaustively so someone else may have another suggestion for you but the only two approaches I can think of that might meet your needs are to use the LIMIT clause or to write an application. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.6/337 - Release Date: 11/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Returning records in a circle
I'm sorry but I think you're all making this more complicated than it needs to be. I could easily write a Java program that obtained a result set, then processed that set in almost any way you wanted, including two records at a time. I could probably do the same in Perl or any other language supported by MySQL, although it might take a bit of time to learn the necessary parts of the language. Processing a result set is a very well-understood process and has been widely done millions of times in dozens of languages. You should not need to add any columns to the table to do it either. I might be able to offer more detail - and maybe even an example! - if a few things were explained to me. I'm still not clear on why two records are being processed at a time and what the relationship is between the records. About the only time I see twinned records are when someone is doing an accounting application where each debit is matched by an offsetting credit. But this doesn't seem to the case here. Perhaps you don't need to process two records at once? Also, am I right in assuming that you are allowing new records to be written to the table - and allowing existing records to be updated - while you run your query? If yes, is it possible that the keys of the new records will be interspersed with the existing keys? Or will new records always have a key value that is higher than the highest previous key value? If inserts and updates are happening, do they absolutely HAVE to happen simultaneously with your query? Or could you store them off to the side briefly or even just suspend the insert and update applications while the query runs? If there are no inserts and updates to worry about, you should be able to avoid any updates of duplicates by simply sorting all of the desired rows into order based on the key and then processing them one (or two) at a time. Things get a bit trickier if the table is being updated/inserted while your new program is running. I don't want to say much more until you've clarified what it is you are doing -- Rhino - Original Message - From: "George Law" <[EMAIL PROTECTED]> To: "Steffan A. Cline" <[EMAIL PROTECTED]>; Sent: Friday, May 12, 2006 3:33 PM Subject: RE: Returning records in a circle I ran into this same type of question the other evening at a local linux group. I think that once you reach the end of the results set the only way to start back at the beginning of that results set is to do the query again. Once option - do your query and grab all the rows load them into a data structure - ie - a list of associative arrays Then all you need to do is incrementally go through the array. when you hit the end, just reset the index back to 0. -Original Message- From: Steffan A. Cline [mailto:[EMAIL PROTECTED] Sent: Friday, May 12, 2006 2:52 PM To: mysql@lists.mysql.com Subject: Re: Returning records in a circle Well, basically it can be done to an extent in some kind of code. Basically I am drawing from a table 2 records at a time. I want to make sure that all records are pulled at least once. If at all possible not to have 2 from the same vendor. So, in this case. I have columns id, html, vendor So I can add columns as needed. I tried adding a flag and after returning to the client the 2 records I'd mark it as flag = 1 then search like this Select id, html from urls order by flag, id desc limit 3 Then after I have those I would then set the last of the 3 to flag = 1 so that on the next search I get the 2 after. In theory it worked fine but when multiple people hit the page simultaneously I had flags in different places and not in order. Maybe just mark them as flag = 1 after returned and then on search if found is 0 then set all to flag = 0 so they can be seen again? This doesn't seem so bad but them I guess I'd use distinct? If I cant use distinct with other parameters... ex: select id,html distinct(vendor) from urls where flag=0 limit 2; Would it be like : Select id,html from urls where flag = 0 group by distinct(vendor) limit 2 ? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 The Executive's Choice in Lasso driven Internet Applications Lasso Partner Alliance Member --- From: Rhino <[EMAIL PROTECTED]> Date: Fri, 12 May 2006 14:20:10 -0400 To: "Steffan A. Cline" <[EMAIL PROTECTED]>, Subject: Re: Returning records in a circle - Original Message - From: "Steffan A. Cline" <[EMAIL PROTECTED]> To: Sent: Friday, May
Re: Baffled by error
- Original Message - From: "Mike Blezien" <[EMAIL PROTECTED]> To: "MySQL List" Sent: Tuesday, May 16, 2006 1:29 PM Subject: Baffled by error Hello, MySQL 4.1.12 trying to figure out why I keep getting this error with the following query: SELECT c.account_id,a.name,a.company,SUM(c.agent_product_time) AS mins FROM account a LEFT JOIN calls c ON c.account_id = a.id WHERE c.calldate >= DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 9 AND SUM(c.agent_product_time) >= '500' GROUP BY a.account_id ORDER BY mins ERROR: # - Invalid use of group function Any help appreciated... I mostly use DB2, not MySQL, and I know that MySQL is a bit looser about what it will allow in its SQL. However, if this were happening in DB2, I'd say that it's because you needed to change the GROUP BY to say: GROUP BY a.account_id, a.company In DB2, the GROUP BY normally needs to include EVERY column/expression from the SELECT clause that is not in a column function. In your case, you are doing a SUM on c.agent_product_time so that means DB2 would expect you to put BOTH of the other two columns from the SELECT in your GROUP BY. Now, if each account_id is associated with a single company, this should give you the result you probably envisioned. But if a given account_id can be associated with more than one company, then your revised query will give you one sum for each COMBINATION of account_id and company. For example: ACCOUNT_IDCOMPANYMINS 001 ABC Co.45 001 DEF Ltd. 90 But maybe that's what you wanted all along? -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.6/340 - Release Date: 15/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Baffled by error
- Original Message - From: "Rhino" <[EMAIL PROTECTED]> To: "Mike Blezien" <[EMAIL PROTECTED]>; "MySQL List" Sent: Tuesday, May 16, 2006 1:56 PM Subject: Re: Baffled by error - Original Message - From: "Mike Blezien" <[EMAIL PROTECTED]> To: "MySQL List" Sent: Tuesday, May 16, 2006 1:29 PM Subject: Baffled by error Hello, MySQL 4.1.12 trying to figure out why I keep getting this error with the following query: SELECT c.account_id,a.name,a.company,SUM(c.agent_product_time) AS mins FROM account a LEFT JOIN calls c ON c.account_id = a.id WHERE c.calldate >= DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 9 AND SUM(c.agent_product_time) >= '500' GROUP BY a.account_id ORDER BY mins ERROR: # - Invalid use of group function Any help appreciated... I mostly use DB2, not MySQL, and I know that MySQL is a bit looser about what it will allow in its SQL. However, if this were happening in DB2, I'd say that it's because you needed to change the GROUP BY to say: GROUP BY a.account_id, a.company Sorry, I didn't notice the name in the SELECT. Therefore, the GROUP BY should be: GROUP BY a.account_id, a.name, a.company In DB2, the GROUP BY normally needs to include EVERY column/expression from the SELECT clause that is not in a column function. In your case, you are doing a SUM on c.agent_product_time so that means DB2 would expect you to put BOTH of the other two columns from the SELECT in your GROUP BY. Oops, that should be: DB2 would expect you to put ALL of the other THREE columns from the select in your GROUP BY. Now, if each account_id is associated with a single company, this should give you the result you probably envisioned. But if a given account_id can be associated with more than one company, then your revised query will give you one sum for each COMBINATION of account_id and company. For example: ACCOUNT_IDCOMPANYMINS 001 ABC Co.45 001 DEF Ltd. 90 Oops, that should be: Now, if each account_id is associated with a single name and company, this should give you the result you probably envisioned. But if a given account_id can be associated with more than one company and the company with more than one name, then your revised query will give you one sum for each COMBINATION of account_id, name and company. For example: ACCOUNT_IDNAME COMPANYMINS 001 Bob ABC Co.45 001 DaveABC Co.20 001 Mary DEF Ltd. 90 001 Jane DEF Ltd. 190 But maybe that's what you wanted all along? -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.6/340 - Release Date: 15/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.6/340 - Release Date: 15/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.6/340 - Release Date: 15/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
- Original Message - From: "John Meyer" <[EMAIL PROTECTED]> To: "List: MySQL" Sent: Tuesday, May 30, 2006 5:09 PM Subject: Query problem Setup TITLES: TITLE_ID AUTHORS: AUTHOR_ID TITLE_AUTHOR: (TITLE_ID,AUTHOR_ID) Problem: Given a title, I need to find all the authors who aren't connected with that particular book. That's a pretty odd requirement, I must say. If your database has thousands or millions of books, you'd have to assume that virtually ALL of the authors in the database are NOT connected with a particular book. I'm trying to think of a situation where that list of people who were not connected with the book was actually useful Okay, maybe if you were looking for authors who could review the book, the query you want could be useful for identifying potential reviewers. Even if that was thousands of authors, it's still a smaller list than the list of all human beings on Earth :-) In any case, the query is pretty simple assuming you are using a version of MySQL which supports subqueries: select AUTHOR_ID from AUTHORS where AUTHOR_ID not in (select AUTHOR_ID from TITLE_AUTHOR where TITLE_ID = 123) You simply plug in the title_id of the book in question in place of 123 and you're all set. If I were creating the database, I would have an id _and_ an author name in the AUTHORS table and I'd have an id _and_ a title in the TITLES table. Then I'd modify the query above to do joins so that the result showed me the author names and searched so that I was looking for the book title, not the book id. But I'm guessing that you already have that in mind and just simplified the question to get the bare essence of it. Or maybe you only have a few dozen books and will quickly memorize the author names and titles that go with each author id and title id. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.7.4/351 - Release Date: 29/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sql optimization
You're hard-coding ten thousand discrete values between the two IN clauses?? I'm not sure how MySQL processes a query like that but I can imagine that the performance would not be good. Frankly, I am: a) suprised that you had the patience to type in all of those values (and verify that you typed them correctly) b) amazed that MySQL actually executes a statement that long without complaining that the statement is too long Are the discrete values really sequential like the example you show? Because if they are you might find your life a lot easier if you simply write your INs as BETWEENs. For example, delete from modnaptrrecord_zone where modnaptrrecord_zone.modnaptrrecord_id between 593536 and 789123 and modnaptrrecord_zone.modnaptrrecord_id not between 593536 and 789123 Even if there were a few ranges, the query would still be a lot shorter if written with BETWEENs. For example, delete from modnaptrrecord_zone where (modnaptrrecord_zone.modnaptrrecord_id (between 100 and 500) or (modnaptrecord_zone.modnaptrrecord_id between 1 and 11000)) and (modnaptrrecord_zone.modnaptrrecord_id not (between 100 and 500) or (modnaptrecord_zone.modnaptrrecord_id between 1 and 11000)) I've probably got at least one of those conditions backward but you probably get the idea. It's a LOT easier to write ranges than long lists of sequential values! Another way that might improve performance a lot is if you use a subqueries with your IN clauses. For example: delete from modnaptrrecord_zone where modnaptrrecord_zone.modnaptrrecord_id in (select id from other_table where customer_country = 'USA') and modnaptrrecord_zone.modnaptrrecord_id not in (select id from other_table where customer_country = 'CANADA') In other words, if the values that need to go into the IN list can be obtained by reading a table with a SELECT, put the SELECT statement within the brackets instead of listing the hundreds of discrete values. Of course, this assumes that you are using a version of MySQL that supports subqueries!! By the way, I'm assuming that the lists needed for your two IN clauses are different from one another in some way. Your query makes no sense if the two lists are the same. For example, if your lists were the same - I'll just use a few values for each list to make my point clear - you might have a query like this: delete from modnaptrrecord_zone where modnaptrrecord_zone.modnaptrrecord_id in (1, 2, 3) and modnaptrrecord_zone.modnaptrrecord_id not in (1, 2, 3) In other words, you want to delete rows in table modnaprrecord_zone where the modnaptrrecord_id IS 1, 2 or 3 AND where the mondaptrrecord_id IS NOT 1, 2, or 3 at the same time. That doesn't make sense. Now, if the values in the lists are not in ranges and can't easily be obtained via SELECT queries, you may want to consider modifying your tables to simplify this query. Let's say that those long IN lists are just trying to specify customers who are in good standing with you, i.e. customers who don't owe you any money and are prompt to pay their accounts. You might find it a lot easier to add a column to your customer table that indicates the customer's status and then simply search on the value of that status flag. For example, let's say that a customer could have three statuses: "Excellent", "Good" and "Poor". You could have a process that determines the proper status for that customer; that might be a batch job that runs once a day or once a week. Then, your query could simply search for the status like this: delete from modnaptrrecord_zone where modnaptrrecord_zone.modnaptrrecord_id in (select id from customer where status = 'Poor') and modnaptrrecord_zone.modnaptrrecord_id not in (select id from customer where status = 'Good') -- Rhino - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, June 20, 2006 11:22 AM Subject: Sql optimization Hi All, I am using INNODB.I have a delete quetry something like this : delete from modnaptrrecord_zone where modnaptrrecord_zone.modnaptrrecord_id in (593536 ,593537 ,593538 ,593539 ,593540 ) and modnaptrrecord_zone.modnaptrrecord_id not in (593536 ) This is taking a very long time to execute..somewhere around 15mins. The values in the "id in" and "not in" of the where clause are around 1. I am sending the "SHOW INNODB STATUS also. Can somebody let me know, why this is taking such a long time ? And how to optimize this query ? Thanks Prasad The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may cont
Re: How to look for balanced parenthesis?
- Original Message - From: "mos" <[EMAIL PROTECTED]> To: Sent: Sunday, July 09, 2006 1:35 PM Subject: How to look for balanced parenthesis? I have a complicated SQL statement with around a dozen "if(this,val1,val2)" embedded in it and there are even nested If clauses. I'm getting syntax errors because I'm not balancing the "( )" properly. Is there any free software out there for Windows that I can copy and paste the SQL statement into that will show me where the parenthesis are unbalanced? It doesn't have to know about SQL, I just to either highlight the parenthesis range or find out when the parenthesis become out of sync. I'm going to give you one answer that you almost certainly won't like: Eclipse. Eclipse is an IDE for developing programs, especially Java, and it has a parenthesis matcher which also handles braces and square brackets. Installing Eclipse solely for the bracket matcher is a bit like using atomic weapons to kill mosquitos but if you were going to develop applications anyway and wanted a great IDE, it might be the answer to your problem. It's free by the way. You can get it at http://eclipse.org. Another editor that can also match brackets is PFE, Programmer's File Editor. It's also free and is a good editor. You can find it many places, including http://www.lancs.ac.uk/staff/steveb/cpaap/pfe/pfefiles.htm. It only runs on Windows though. Another decent little editor that has the feature is TextPad. It's also free and can be found at http://www.textpad.com/. There are probably more basic editors out there that have bracket matchers but I can't name any for you. I'm not even sure what the feature you want is supposed to be called: "bracket matcher", "parenthesis balancer", or whatever. It's getting to be a pretty standard feature in editors in recent years, although it doesn't seem to be in NotePad or WordPad. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.9/382 - Release Date: 2006-07-04 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to look for balanced parenthesis?
- Original Message - From: "Miles Thompson" <[EMAIL PROTECTED]> To: Sent: Sunday, July 09, 2006 8:10 PM Subject: Re: How to look for balanced parenthesis? At 03:48 PM 7/9/2006, Rhino wrote: - Original Message - From: "mos" <[EMAIL PROTECTED]> To: Sent: Sunday, July 09, 2006 1:35 PM Subject: How to look for balanced parenthesis? I have a complicated SQL statement with around a dozen "if(this,val1,val2)" embedded in it and there are even nested If clauses. I'm getting syntax errors because I'm not balancing the "( )" properly. Is there any free software out there for Windows that I can copy and paste the SQL statement into that will show me where the parenthesis are unbalanced? It doesn't have to know about SQL, I just to either highlight the parenthesis range or find out when the parenthesis become out of sync. I'm going to give you one answer that you almost certainly won't like: Eclipse. Eclipse is an IDE for developing programs, especially Java, and it has a parenthesis matcher which also handles braces and square brackets. Installing Eclipse solely for the bracket matcher is a bit like using atomic weapons to kill mosquitos but if you were going to develop applications anyway and wanted a great IDE, it might be the answer to your problem. It's free by the way. You can get it at http://eclipse.org. Another editor that can also match brackets is PFE, Programmer's File Editor. It's also free and is a good editor. You can find it many places, including http://www.lancs.ac.uk/staff/steveb/cpaap/pfe/pfefiles.htm. It only runs on Windows though. Another decent little editor that has the feature is TextPad. It's also free and can be found at http://www.textpad.com/. There are probably more basic editors out there that have bracket matchers but I can't name any for you. I'm not even sure what the feature you want is supposed to be called: "bracket matcher", "parenthesis balancer", or whatever. It's getting to be a pretty standard feature in editors in recent years, although it doesn't seem to be in NotePad or WordPad. -- Rhino Rhino, Eclipse can't edit files on the server, can it? I used to use UltraEdit, but then switched to EditPlus because it can edit remote files almost transparently. (Opening a file FTP's it down, you edit local copy, Saving FTP's it back.) I really don't know if Eclipse can edit files on a server. My workspace is on my local PC so I've never tried editing a file on a server. I can certainly see that it would be convenient to be able to edit a file on a server without having to manually transfer the file back and forth! But Eclipse is pretty smart so I'd be a little surprised if they forced you to transfer the file manually. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.9/382 - Release Date: 2006-07-04 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ON DELETE CASCADE question
If I understand your question correctly - and I'm not sure I do - Table 2 is the parent and Table 1 is the child. In other words, there is a one-to-many relationship between the parent, Table 2, and the child, Table 1. Therefore, if the parent table, Table 2, contains one row for each department of a company and the child table, Table 1, contains one row for each employee, we would have a situation like this: Table 2 - DEPARTMENT === DeptnoDeptname A00Administration B01Sales C01Manufacturing Table 1 - EMPLOYEE = EmpnoDeptnoName 100A00Smith 101B01Brown 102C01Wong 103A00Green 104A00White 105C01Ferguson If I'm following you so far, you're asking what happens in the DEPARTMENT table if you delete an employee from the EMPLOYEE table? For example, if you delete employee 105 or even employee 101 from the EMPLOYEE table, will there be any impact on the DEPARTMENT table? The answer is _NO_. The DELETE rule between a parent table and its child table controls what happens in the _child_ table if a row is deleted from the _parent_ table; deleting a row in the child table has no effect on the parent table. Therefore, if you delete employee 105 from the EMPLOYEE table, the only effect is that employee 105 is removed from the EMPLOYEE table; there is no effect of any kind on the DEPARTMENT table. Ditto for employee 101 who is the only employee in department B01: only the employee 101 row is removed and there is no impact on the DEPARTMENT table at all. If you want to see the DELETE rule between the tables take effect, you need to delete a row from the _DEPARTMENT_ table. If the DELETE rule in effect is CASCADE, as you have proposed, then deleting the B01 row from DEPARTMENT will also cause the deletion of all B01 rows in the EMPLOYEE table. By the same token, deleting the C01 row from the DEPARTMENT table will cause the deletes of _all_ of the C01 rows in the EMPLOYEE table. -- Rhino - Original Message - From: "James Sherwood" <[EMAIL PROTECTED]> To: "mysqllist" Sent: Monday, July 17, 2006 8:44 AM Subject: ON DELETE CASCADE question Hello. I have a question about on delete cascade. If i have 2 tables such as this: Table1Table2 PrikeyPrikey Table2foreinkey name name description description Now if I delete a row from table1 that has a foreign key from table 2 should it delete the row in table 2? Thanks, James No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.10/385 - Release Date: 2006-07-11 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.10/385 - Release Date: 2006-07-11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Chemistry search
- Original Message - From: "Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem" <[EMAIL PROTECTED]> To: Sent: Wednesday, August 23, 2006 10:15 PM Subject: Chemistry search Has anyone come up with searching a chemicals based database using mols or smilies? Huh? What do you mean by "mols" - molar weights? And why would anyone search for anything to do with chemicals based on smilies? How would ":-)" or symbols like that help? Or is this some other sense of the word "smilies" than the customary Internet one? -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.11.5/425 - Release Date: 2006-08-22 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what this error was
- Original Message - From: "VenuGopal Papasani" <[EMAIL PROTECTED]> To: Sent: Tuesday, September 05, 2006 12:28 AM Subject: what this error was Hi, I was trying to import some of the data into the 'datavalue' table of my database i was getting th following error Error Code : 1452 Cannot add or update a child row: a foreign key constraint fails (`gtest1/datavalue`, CONSTRAINT `FKEB92DC077A7A2FFA` FOREIGN KEY (`dataElement`) REFERENCES `dataelement` (`id`)) (5428 ms taken) My table structures: dataelement CREATE TABLE `dataelement` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255) NOT NULL, `shortName` varchar(255) default NULL, `description` varchar(255) default NULL, `active` bit(1) default NULL, `type` varchar(255) default NULL, `aggregationOperator` int(11) default NULL, `parent` int(11) default NULL, `alternativeName` varchar(255) default NULL, `code` varchar(255) default NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), UNIQUE KEY `shortName` (`shortName`), UNIQUE KEY `alternativeName` (`alternativeName`), UNIQUE KEY `code` (`code`), KEY `FKE1611C125DC41C92` (`parent`), CONSTRAINT `FKE1611C125DC41C92` FOREIGN KEY (`parent`) REFERENCES `dataelement` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 -- datavalue CREATE TABLE `datavalue` ( `dataElement` int(11) NOT NULL, `period` int(11) NOT NULL, `source` varchar(255) NOT NULL, `flag` varchar(255) default NULL, `value` varchar(255) default NULL, `storedBy` varchar(255) default NULL, `timestamp` datetime default NULL, `comment` varchar(255) default NULL, PRIMARY KEY (`dataElement`,`period`,`source`), KEY `FKEB92DC077F9CE9CC` (`period`), KEY `FKEB92DC077A7A2FFA` (`dataElement`), CONSTRAINT `FKEB92DC077A7A2FFA` FOREIGN KEY (`dataElement`) REFERENCES `dataelement` (`id`), CONSTRAINT `FKEB92DC077F9CE9CC` FOREIGN KEY (`period`) REFERENCES `period` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 The message is pretty clear; a dataelement value on a row failed to satisfy the foreign key constraint during either an insert or an update. Let's say that the dataelement value had a foreign constraint that limited the values to 'M' or 'F'. If you added a new row to the table and the value in the new dataelement column was anything but 'M' or 'F', you would have violated the foreign constraint and gotten that error. Or, if you had updated an existing row and tried to change the dataelement value to anything but 'M' or 'F', you would get that same message. You need to look at the values that are allowable in this dataelement column and then see what value is actually being inserted or what the existing value is being changed to. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.11.7/437 - Release Date: 2006-09-04 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: E103
It really is best if we keep conversations like this on the list rather than offline; if we use the list, EVERYONE on the list can help and can learn from your problem. In this case, I was only trying to suggest a better way to ask your question so that more people could help. I don't know what is wrong myself but I thought if your question had more information, more people would have an idea about what might be wrong. I'm not very familiar with the normal contents of the MySQL error log. However, I don't see anything that looks like a severe error in your attachment. Is it possible that MySQL is actually working okay? Can you run a query? Maybe the mysterious E103 error is really just an "informational" message and doesn't actually indicate a serious problem Rhino - Original Message - From: <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Sent: Monday, June 20, 2005 9:37 AM Subject: Re: E103 > I don't know where does this error come from! > I'm using Windows XP. The version of MySQL is 4.1.10a. I tryied to install > it in binary way. > The ERR file is in C:\mysql\data; it is not the console who gave it. I > attached it to this message, so you ca see it too. > > Thank you sincerely for your help, > Denisa > > > > > I don't see any E103 message in the MySQL manual. Are you sure it is > coming from MySQL and not some other program on your system? > > > > If you're sure the message comes from MySQL, can you please tell us > more? > > 1. What version of MySQL are you running? > > 2. What operating system and version are you using? > > 3. What information did MySQL write to your console besides "E103"? > Usually, there are additional messages. What exactly were you doing when > the error was displayed? > > 4. Did you get an error log? (See > > http://dev.mysql.com/doc/mysql/en/error-log.html). If yes, what does it > contain? > > > > Rhino > > > > - Original Message - > > From: <[EMAIL PROTECTED]> > > To: > > Sent: Monday, June 20, 2005 8:36 AM > > Subject: E103 > > > > > >> Hello, > >> I'm trying to install MySQL and I have an error file named E103; it has 0 > >> Ko. What do I have to do in this case? > >> Denisa Eustasius > >> PS: it's the first time I install MySQL and I'm not informatician! -- > >> MySQL General Mailing List > >> For list archives: http://lists.mysql.com/mysql > >> To unsubscribe: > >> http://lists.mysql.com/[EMAIL PROTECTED] > >> -- > >> No virus found in this incoming message. > >> Checked by AVG Anti-Virus. > >> Version: 7.0.323 / Virus Database: 267.7.8/22 - Release Date: > 17/06/2005 > > > > > > > > -- > > No virus found in this outgoing message. > > Checked by AVG Anti-Virus. > > Version: 7.0.323 / Virus Database: 267.7.8/22 - Release Date: 17/06/2005 > > > > > No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.8/22 - Release Date: 17/06/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.8/22 - Release Date: 17/06/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CHECK constraint
But if you follow Partha's approach, you will need to make sure that the
only inserts to the table occur via the view. Nothing in the definition of
the view itself prevents a user with the necessary authority from inserting
directly into the table. You'll need to ensure that your GRANTs don't permit
any person or program to insert data directly via the table.
Rhino
- Original Message -
From: "Partha Dutta" <[EMAIL PROTECTED]>
To: "'Michael Kruckenberg'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Cc:
Sent: Saturday, June 25, 2005 11:11 AM
Subject: RE: CHECK constraint
> Another approach would be to use a view with a CHECK OPTION. This will
> allow the view to behave exactly like a check constraint:
>
> CREATE VIEW tblJob_view AS
> SELECT JobId, CustomerId, JobType, Description,
> QuotationDate, OrderDate
> FROM tblJob
> WHERE JobType IN ('DesignOnly', 'DesignInstall', 'InstallOnly')
> WITH CHECK OPTION
>
> This will force or constraint the view to only accept inserts where the
> criteria specified in the WHERE clause matches.
>
> --
> Partha Dutta, Senior Consultant
> MySQL Inc, NY, USA, www.mysql.com
>
> Are you MySQL certified? www.mysql.com/certification
>
> > -Original Message-
> > From: Michael Kruckenberg [mailto:[EMAIL PROTECTED]
> > Sent: Saturday, June 25, 2005 10:57 AM
> > To: [EMAIL PROTECTED]
> > Cc: mysql@lists.mysql.com
> > Subject: Re: CHECK constraint
> >
> > A trigger is a good place to check the value, and change it, bit I
> > don't believe you can actually generate a MySQL error within the
> > trigger that will prevent the data from being inserted. Currently
> > (unless there's been an update to triggers that's not yet in the
> > docs), you can only change the value before it gets inserted.
> >
> > If you are looking to enforce the values going into your JobType
> > column, you might be better off creating a JobType table, with a
> > foreign key restraint between the tblJob.JobType and JobType.Name,
> > and make sure that the only entries in the JobType.Name column are
> > those you want to appear in the tblJob.JobType column.
> >
> > On Jun 25, 2005, at 10:28 AM, Chris Andrew wrote:
> >
> > > Dear List,
> > >
> > > My system is RedHat EL3 and MySQL 5.0.7-beta.
> > >
> > > I wanted to implement a check constraint (below), but after some
> > > testing
> > > and googling, it seems I can't do this with MySQL. I've read
> > > suggestions
> > > that check(s) should be done using triggers. Is a trigger a preferred
> > > method of achieving the following:
> > >
> > > CREATE TABLE tblJob (
> > > JobId SMALLINT UNSIGNED NOT NULL,
> > > CustomerIdSMALLINT UNSIGNED NOT NULL,
> > > JobType VARCHAR(20) NOT NULL DEFAULT 'DesignInstall',
> > > Description VARCHAR(100) NOT NULL,
> > > QuotationDate DATE NOT NULL,
> > > OrderDate DATE,
> > > CHECK (JobType IN ('DesignOnly', 'DesignInstall', 'InstallOnly')),
> > > PRIMARY KEY (JobId, CustomerId)
> > > ) TYPE=InnoDB;
> > >
> > > Regards,
> > > Chris
> > >
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:http://lists.mysql.com/mysql?
> > > [EMAIL PROTECTED]
> > >
> >
> > Mike Kruckenberg
> > [EMAIL PROTECTED]
> > "ProMySQL" Author
> > http://www.amazon.com/exec/obidos/ASIN/159059505X
> >
> >
> >
> > --
> > 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]
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.323 / Virus Database: 267.7.11/26 - Release Date: 22/06/2005
>
>
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.7.11/26 - Release Date: 22/06/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER by Question
Is "The" your only problem word? What about "A" or "An" and other words that are usually ignored when sorting things like book titles? I'd be surprised if there was any way to ignore specific words in an ORDER BY; I've been writing SQL for 20+ years and have never seen anything like that. I think what you'll need to do is modify your data so that words like "The", "A", "An", etc. are at the end of the column values. I believe libraries traditionally cataloged books as illustrated in these examples: The Raven ==> Raven, The A Voyage to the Moon and a Trip Around It ==> Voyage to the Moon and a Trip Around It, An Another approach that *might* be easier - if you are using an appropriate version of MySQL - is to create a view that modifies the data for you. You'd need logic like this, which is pseudocode, NOT real SQL: create view view01 as select case when word(subscriber,1) = 'The' then substring(2nd through final words) concatenate 'The' when word(subscriber,1) = 'An' then substring(2nd through final words) concatenate 'An' else subscriber end, other-columns from ... where ... A view can't contain an ORDER BY so you'll have to put the order by in the query that uses the view but that's easy: select subscriber, ... from view01 order by subscriber Rhino - Original Message - From: "Jack Lauman" <[EMAIL PROTECTED]> To: Sent: Sunday, June 26, 2005 2:06 PM Subject: ORDER by Question > I'm using a query similar to the following to get an ordered list. > > SELECT ORDER BY Subscriber ASC, Name ASC; > > How do I change this so that if the 'Name' field begins with "The " that > the sort begins on the second word? In other words I'd like to be able > to return the word "The" but have it sort on whatever the second word is. > > Thanks, > > Jack > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > No virus found in this incoming message. > Checked by AVG Anti-Virus. > Version: 7.0.323 / Virus Database: 267.8.1/28 - Release Date: 24/06/2005 > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.1/28 - Release Date: 24/06/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER by Question
I can see I'm going to have to review the manual pages on ORDER BY; sorry, I didn't realize that MySQL's ORDER BY allowed these sorts of expressions. I use DB2 most of the time and I was speaking on the assumption that MySQL's capabilities in the ORDER BY were very close to those of DB2, which is usually true. Not this time though! Rhino - Original Message - From: "Hassan Schroeder" <[EMAIL PROTECTED]> To: "Jack Lauman" <[EMAIL PROTECTED]>; Sent: Sunday, June 26, 2005 2:44 PM Subject: Re: ORDER by Question > Jack Lauman wrote: > > > SELECT ORDER BY Subscriber ASC, Name ASC; > > > > How do I change this so that if the 'Name' field begins with "The " that > > the sort begins on the second word? In other words I'd like to be able > > to return the word "The" but have it sort on whatever the second word is. > > SELECT... ORDER BY Subscriber ASC, TRIM(LEADING "The " FROM Name) ASC; > > :: should work :-) > > -- > Hassan Schroeder - [EMAIL PROTECTED] > Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com > >dream. code. > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > No virus found in this incoming message. > Checked by AVG Anti-Virus. > Version: 7.0.323 / Virus Database: 267.8.1/28 - Release Date: 24/06/2005 > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.1/28 - Release Date: 24/06/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why would a UNION be 100x slower than the same SELECT...
Why do you think you're using a UNION in your query? The keyword UNION doesn't appear anywhere in your query. You don't even have a second query being UNIONed to the first. All you've got is a pair of parentheses surrounding your original query, which seems to perform okay. For what it's worth, I don't see why a pair of parentheses would change the performance but calling it a UNION just confuses the issue, in my view. Rhino - Original Message - From: "Kevin Burton" <[EMAIL PROTECTED]> To: Sent: Monday, June 27, 2005 4:17 PM Subject: Why would a UNION be 100x slower than the same SELECT... > Here's a big problem I'm having. > > If I have a query like: > > SELECT * FROM FOO WHERE FOO.LAST_UPDATED < 1119898418779 AND > FOO.FEED_ID = 1 ORDER BY FOO.LAST_UPDATED DESC LIMIT 10 > > it only takes about 10ms or so to execute. > > but... if I rewrite it to wrap it in a union like so: > > ( SELECT * FROM FOO WHERE FOO.LAST_UPDATED > < 1119898418779 AND FOO.FEED_ID = 1 ORDER BY FOO.LAST_UPDATED DESC LIMIT > 10 ) ORDER BY LAST_UPDATED DESC LIMIT 10 > > then its 100x slower and takes about 1000ms > > No tmp disk tables were created (or at least thats what show status is > telling me). > > Any idea whats going on and how I could fix this? > > Kevin > > -- > > > Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. > See irc.freenode.net #rojo if you want to chat. > > Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html > >Kevin A. Burton, Location - San Francisco, CA > AIM/YIM - sfburtonator, Web - http://peerfear.org/ > GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > No virus found in this incoming message. > Checked by AVG Anti-Virus. > Version: 7.0.323 / Virus Database: 267.8.2/29 - Release Date: 27/06/2005 > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.2/29 - Release Date: 27/06/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple GROUP / ORDER problem
There may well be a way to do what you want but I'd like to make a brief point then let other people with more time give you the answer you want. Simply put, GROUP BY is NOT intended to give you a specific record out of a group the way that you are trying to do. It's job is to do SUMMARIZATION of a group of records. For example, a typical use of GROUP BY would be something like this: select deptno, sum(salary) from employee group by deptno Given a table of employee rows with one row for each employee and where each row contained at least the employee's department number and salary, this query would group all employees together based on their department number then compute and report the total salary paid to each department. Therefore, GROUP BY is telling the database to add up the salaries for each of the people that belong to the first department and report it, then do the second department, then the third department, and so on until all departments have been handled. The query does NOT return the rows of SPECIFIC employees. The query you want, on the other hand, is trying to choose a particular one of the rows in your group and is not doing summarization at all. In short, I don't think you want to be doing a GROUP BY at all. Instead, I think you want to use ORDER BY and perhaps some WHERE clauses to do what you want to do. Just something for you to think about Rhino - Original Message - From: "Lee Denny" <[EMAIL PROTECTED]> To: Sent: Wednesday, June 29, 2005 8:00 AM Subject: Simple GROUP / ORDER problem > Hello, > > If got a simple sessions table basically holds a session id and datetime > field for last modification also a session type, so I can have several > records with the same session id, with different types and different > modification time. I want to get the latest modified record for any given > session, and I'm using > > SELECT * FROM translines GROUP BY session_id ORDER BY session_modified DESC > > on this example data set : > session_id | type | date_modified > d36631973996623650e5e1caae5686ca 1 2005-06-29 11:40:00 > d36631973996623650e5e1caae5686ca 2 2005-06-29 11:34:41 > d36631973996623650e5e1caae5686ca 1 2005-06-29 10:50:41 > d36631973996623650e5e1caae5686ca 3 2005-06-29 10:50:41 > 09ebae8272301839c519cc3bb0ca 2 2005-06-28 20:38:18 > > Although this returns the individual sessions in the correct order, the > group by is returning the earliest record for that session_id so I get: > > d36631973996623650e5e1caae5686ca 3 2005-06-29 10:50:41 > 09ebae8272301839c519cc3bb0ca 2 2005-06-28 20:38:18 > > Rather than > > d36631973996623650e5e1caae5686ca 1 2005-06-29 11:40:00 > 09ebae8272301839c519cc3bb0ca 2 2005-06-28 20:38:18 > > Which is what I want, I'm sure this is a misunderstanding on my part, but > does any one have any ideas? > > All the best, > > Lee > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > No virus found in this incoming message. > Checked by AVG Anti-Virus. > Version: 7.0.323 / Virus Database: 267.8.5/32 - Release Date: 27/06/2005 > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.6/33 - Release Date: 28/06/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group Summary by Month
I think you need to start with GROUP BY. You'll need something along this line: select partno, month, sum(cost) from mytable group by partno, month; This ensures that you get one summary row showing the total cost of for each part number for each month. Naturally, you can add WHERE conditions to get only certain part numbers and only certain months. Rhino - Original Message - From: "Ed Reed" <[EMAIL PROTECTED]> To: Sent: Friday, July 29, 2005 4:31 PM Subject: Group Summary by Month I have a table that contains all the part number and cost transactions over time. I wanna get a get the summary cost of each part number for each month. For example, when I supply a single part number to this query the results should be something like this. +--+--+ | Month | Cost | +--+--+ | Oct 04 | 19521.00 | | Nov 04 | 854.00 | | Dec 04 | 191.00 | | Jan 05 | 7015.00 | | Feb 05 |21.00 | | Mar 05 |25.00 | | Apr 05 | 13571.00 | | May 05 | 1015.00 | +--+--+ Can anyone point me in a direction to start on this? -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.9.6/59 - Release Date: 27/07/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Writing a file into a BLOB field with Java
There's an example of what you want at this URL: http://forum.java.sun.com/thread.jspa?threadID=576315&messageID=2886886 Rhino - Original Message - From: "C.F. Scheidecker Antunes" <[EMAIL PROTECTED]> To: Sent: Wednesday, August 17, 2005 4:22 AM Subject: Writing a file into a BLOB field with Java > Hello all, > > Does anyone knows howto insert a row in a database that has a blob field > using Java? > > I need to read a file and then write it to a blob field on the database. > > Thanks, > > C.F. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > No virus found in this incoming message. > Checked by AVG Anti-Virus. > Version: 7.0.338 / Virus Database: 267.10.10/73 - Release Date: 15/08/2005 > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.11/74 - Release Date: 17/08/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple Count Query
I think you want this or something similar: select count(distinct fld2) from yourtable where fld4 = 'am'; N.B. I have not tested this solution. Rhino - Original Message - From: "Ed Reed" <[EMAIL PROTECTED]> To: Sent: Friday, September 09, 2005 6:08 PM Subject: Simple Count Query I know this has to be a simple query but its really kickin' my butt. I have the table below where fld1 is the year, fld2 is a number, fld 3 is a subnumber, and fld4 is the user. I need to know the count of all the records for user am without the sub number getting in the way. For example, the first record for user am shows in the 3rd year number 1 with two sub records was for user am . That needs to be counted as one item. So when all the items are counted I should have a total of 5 items for user am and not 17 like you'd normally get Any thoughts? thanks +--+--+--+--+ | fld1 | fld2 | fld3 | fld4 | +--+--+--+--+ | 3 | 1 | a | am | | 3 | 1 | b | am | | 3 | 2 | a | am | | 3 | 3 | a | pm | | 3 | 3 | b | pm | | 3 | 3 | c | pm | | 4 | 1 | a | pm | | 4 | 2 | a | pm | | 4 | 3 | a | am | | 4 | 3 | b | am | | 4 | 3 | c | am | | 4 | 3 | d | am | | 4 | 3 | e | am | | 4 | 3 | f | am | | 4 | 4 | a | am | | 4 | 4 | b | am | | 5 | 1 | a | pm | | 5 | 1 | b | pm | | 5 | 1 | c | pm | | 5 | 1 | d | pm | | 5 | 1 | e | pm | | 5 | 2 | a | am | | 5 | 2 | b | am | | 5 | 2 | c | am | | 5 | 2 | d | am | | 5 | 2 | e | am | | 5 | 2 | f | am | +--+--+--+--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.18/91 - Release Date: 06/09/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.18/91 - Release Date: 06/09/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple Count Query
Oops, that should be: select count(distinct fld1, fld2) from yourtable where fld4 = 'am'; That version of the query is tested and works with your data; it returns the fact that there are 5 combinations of fld1 and fld2 for all the rows where fld4 is 'am'. Rhino - Original Message - From: "Rhino" <[EMAIL PROTECTED]> To: "Ed Reed" <[EMAIL PROTECTED]>; Sent: Friday, September 09, 2005 6:26 PM Subject: Re: Simple Count Query > I think you want this or something similar: > > select count(distinct fld2) > from yourtable > where fld4 = 'am'; > > N.B. I have not tested this solution. > > Rhino > > > - Original Message - > From: "Ed Reed" <[EMAIL PROTECTED]> > To: > Sent: Friday, September 09, 2005 6:08 PM > Subject: Simple Count Query > > > I know this has to be a simple query but its really kickin' my butt. I have > the table below where fld1 is the year, fld2 is a number, fld 3 is a > subnumber, and fld4 is the user. I need to know the count of all the > records for user am without the sub number getting in the way. For example, > the first record for user am shows in the 3rd year number 1 with two sub > records was for user am . That needs to be counted as one item. So when all > the items are counted I should have a total of 5 items for user am and not > 17 like you'd normally get > > Any thoughts? > > thanks > > +--+--+--+--+ > | fld1 | fld2 | fld3 | fld4 | > +--+--+--+--+ > | 3 | 1 | a | am | > | 3 | 1 | b | am | > | 3 | 2 | a | am | > | 3 | 3 | a | pm | > | 3 | 3 | b | pm | > | 3 | 3 | c | pm | > | 4 | 1 | a | pm | > | 4 | 2 | a | pm | > | 4 | 3 | a | am | > | 4 | 3 | b | am | > | 4 | 3 | c | am | > | 4 | 3 | d | am | > | 4 | 3 | e | am | > | 4 | 3 | f | am | > | 4 | 4 | a | am | > | 4 | 4 | b | am | > | 5 | 1 | a | pm | > | 5 | 1 | b | pm | > | 5 | 1 | c | pm | > | 5 | 1 | d | pm | > | 5 | 1 | e | pm | > | 5 | 2 | a | am | > | 5 | 2 | b | am | > | 5 | 2 | c | am | > | 5 | 2 | d | am | > | 5 | 2 | e | am | > | 5 | 2 | f | am | > +--+--+--+--+ > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > No virus found in this incoming message. > Checked by AVG Anti-Virus. > Version: 7.0.344 / Virus Database: 267.10.18/91 - Release Date: 06/09/2005 > > > > > -- > No virus found in this outgoing message. > Checked by AVG Anti-Virus. > Version: 7.0.344 / Virus Database: 267.10.18/91 - Release Date: 06/09/2005 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > No virus found in this incoming message. > Checked by AVG Anti-Virus. > Version: 7.0.344 / Virus Database: 267.10.18/91 - Release Date: 06/09/2005 > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.18/91 - Release Date: 06/09/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compare tables from two systems
- Original Message - From: "Claire Lee" <[EMAIL PROTECTED]> To: Sent: Wednesday, October 05, 2005 5:58 PM Subject: compare tables from two systems > We have two clusters running the same data > independently and saving output to databases on two > systems (two hosts) respectively. We need to compare > the output each day. Is there an easy way to compare > tables from two systems? We are sure the structure of > the tables are all the same and we need to compare the > data. Any advice is welcome. Thanks. > I'm not sure if there are any existing table comparison tools out there; in an ideal world, someone would have written something that works on any relational database that would compare any two tables to see if they are the same. I've never actually heard of such a tool but I've never looked for one either. If there is no such tool out there, you could create one of your own without too much difficulty. One way that should be pretty easy would be to do a mysqldump of each table then do standard file compares of each of the two resulting files. I have a freeware program for Windows called ExamDiff which seems to do that job okay, although I've never tried comparing output files from mysqldump with it. There may be one very difficult problem with this approach: it assumes that the mysqldump will write the INSERT statements for the individual rows in a specific order, ideally primary key sequence, for each table. Unfortunately, I don't see any options for mysqldump that ensure that this will happen and I don't see any statement in the manual that say it will happen automatically. Therefore, it is entirely possible that the mysqldumps of each table will write the INSERTs in some sequence other than primary key order. For example, mysqldump might use the sequence in which the rows were stored, retrieving them from oldest to newest, rather than primary key sequence. In any case where primary key sequence is not used - or where no primary key exists on the table - the two mysqldump files could have completely different sequences even though they have the identical rows. That would almost certainly preclude this approach working. Why not just try doing mysqldumps of each of your two tables and then do a file compare of the two files and see what happens? It shouldn't take long and you'll soon see if this approach will work. By the way, why are you keeping two independent - yet supposedly identical copies - of your tables? Isn't that redundant *and* expensive? Wouldn't it make more sense to backup a single copy of the database regularly so that you can restore when you have problems? Or, if you need the same database in two different locations, why not use replication to ensure that the two copies are automatically synchronised? Rhino > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.9/116 - Release Date: 30/09/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting from 2 tables
If you are using a version of MySQL that supports subqueries, you can do something like this: select * from unprocessed_url_table where url not in (select url from processed_url_table) In other words, the inner query (the part in brackets) is returning a list of all the URLs that have been processed, according to the table which lists the processed URLs; the rest of the query is getting rows of tables whose URLs have *not* been processed. I *think* MySQL V4.1.x supports subqueries but I might be wrong; perhaps only V5.x supports it. I'm afraid I haven't been doing much with MySQL lately and forget exactly when each feature was made available. If you don't have a version that supports subqueries, it should be possible to do what you want a different way, probably with temporary tables. Ask again in you need to know how to do it that way. I think I've got an example lying around of how to do it with temporary tables. Rhino - Original Message - From: "Nurullah Akkaya" <[EMAIL PROTECTED]> To: Sent: Saturday, October 08, 2005 8:43 PM Subject: selecting from 2 tables > i have two tables one of them is a que of urls and the other is list > of urls that have been processed > now i want to select a url from Que that is not in the processed urls > table how can i do this in one select i do not want my application to > do two seperate select statements? > > thx.. > > > > Nurullah Akkaya What lies behind us > and what > [EMAIL PROTECTED] lies before us are tiny matters > Registered Linux User #301438 compared to what lies within us. > > WARNING all messages "If at first an idea is not > containing attachments absurd, there is no hope for it" > or html will be silently Albert Einstein > deleted. Send only > plain text. > > Because the people who are crazy enough to think > they can change the world, are the ones who do. > > No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.13/124 - Release Date: 07/10/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.13/124 - Release Date: 07/10/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Triggers
I have never written a trigger in MySQL but I've done a number of them in DB2 so, assuming the same principles apply to both: I think the problem is that you are attempting to make the triggered action - the UPDATE or INSERT - occur in the same table (or a join involving the same table) as the one which contained the triggering action. For example, after an update of the 'locations' table, you want MySQL to update 'locations' (or join involving 'locations'): this looks circular/recursive to me and I suspect from the error message that is not permitted by MySQL. I expect that your problems will go away if the table that triggers the action is always different from the table that is changed as a result of the trigger. In other words, if an action in Table X is your trigger, the action taken when the trigger is pulled should take place in some table OTHER THAN Table X. I just skimmed the article on CREATE TRIGGER in the manual and don't see any prohibition against making the triggered action take place in the same table as the one that caused the triggering action; I suspect that was an oversight on the part of the technical writer who may have (reasonably) assumed that no one would want the triggered action to occur in the same table as the triggering action. Then again, maybe its not there because it's okay for both the triggered and triggering actions to be on the same table. Maybe Paul Dubois or one of the other writers can address this point with certainty. Rhino - Original Message - From: "Steffan A. Cline" <[EMAIL PROTECTED]> To: Sent: Sunday, October 09, 2005 3:40 AM Subject: Triggers > I am trying to use triggers for the first time with MySQL 5.0. I have read > the manual but I am not understanding why I would run into the following > problem. > > I created the following triggers: > > CREATE TRIGGER UpdPopCoord AFTER UPDATE ON locations FOR EACH ROW > UPDATE locations, zipcodes > SET locations.lat=zipcodes.lat, locations.lon=zipcodes.lon > WHERE (locations.zip=zipcodes.zip AND (locations.lat IS NULL) AND > (locations.lon is NULL)); > > CREATE TRIGGER InsPopCoord AFTER INSERT ON locations FOR EACH ROW > UPDATE locations, zipcodes > SET locations.lat=zipcodes.lat, locations.lon=zipcodes.lon > WHERE (locations.zip=zipcodes.zip AND (locations.lat IS NULL) AND > (locations.lon is NULL)); > > Upon insert or update I get the following error: > > ERROR 1442 (HY000): Can't update table 'locations' in stored > function/trigger because it is already used by statement which invoked this > stored function/trigger. > > What exactly is the meaning of this? Is there no way around this? I only > want to update the one that was just inserted/updated. > > > > Thanks > > Steffan > > --- > T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 > Steffan A. Cline > [EMAIL PROTECTED] Phoenix, Az > http://www.ExecuChoice.net USA > AIM : SteffanC ICQ : 57234309 > The Executive's Choice in Lasso driven Internet Applications > Lasso Partner Alliance Member > --- > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > No virus found in this incoming message. > Checked by AVG Anti-Virus. > Version: 7.0.344 / Virus Database: 267.11.13/124 - Release Date: 07/10/2005 > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.13/126 - Release Date: 09/10/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: deleting rows in 2 tables
- Original Message - From: "Merlin" <[EMAIL PROTECTED]> To: Sent: Sunday, October 09, 2005 1:31 PM Subject: deleting rows in 2 tables > Hi there, > > I am wondering if there is a possiblity to delete rows in more than one > table with one query. > > At the moment I am doing this: > > I do have at the moment 3 querys!: ># get table1_id >SELECT table1_id >from ... >WHERE ID = ... > ># delete dependent entry >DELETE >FROM $DB.$T5 >WHERE >ID = '$data[table1_id]' >LIMIT 1 > ># delete main entry >DELETE >FROM $DB.$T4 >WHERE >ID = '$data[id]' >LIMIT 1 > > Is there a better solution as this? > Have you considered using foreign keys with DELETE CASCADE rules? Basically, if you define the dependent tables as children of the first (parent) table via the appropriate foreign keys and primary keys, and if you establish DELETE CASCADE on the foreign keys, you could be sure that whenever you deleted a row in the parent table, all dependent rows in all dependent tables are deleted automatically. In other words, you set up the rules in your table definitions and then simply delete what you like from the parent table; the dependent rows will be deleted for you without you having to write any explicit DELETE statements for the dependent tables. For example, if your parent table was EMPLOYEE and your dependent tables were EMPLOYEE_CARS and EMPLOYEE_CLAIMS, you'd have the definitions set up roughly like this: create table EMPLOYEE empno integer not null, ... primary key(empno)); create table EMPLOYEE_CARS empno integer not null, licence_plate_state char(2) not null, licence_plate_number char(8) not null, ... primary key(empno, licence_plate_state, licence_plate_number), foreign key(empno) references EMPLOYEE on delete cascade)); create table EMPLOYEE_CLAIMS empno integer not null, claim_number integer not null,, ... primary key(empno, claim_number), foreign key(empno) references EMPLOYEE on delete cascade)); Then, once the tables are defined, all your program needs to do is delete a given employee and the employee's cars and claims will be deleted automatically, via the DELETE CASCADE rules in the definitions of the dependent tables: delete from EMPLOYEE where empno = 10; --- If you want to use this approach, I believe you have to be using InnoDB tables; I don't think the other "engines" support foreign keys. By the way, this whole concept is called "Referential Integrity" and is very useful for ensuring that your tables are consistent with one another. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.13/126 - Release Date: 09/10/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: one product in more categories
d and cat_id as two separate foreign keys in the association table; this will necessitate using InnoDB as your storage engine but it will ensure that your association table only ever contains valid data, which is very useful in my opinion. However, if you can't use InnoDB for some reason, you could omit the foreign key definitions as they are only enforced in InnoDB (unless this has changed recently). Sorry for the long reply but I needed to explain how things are properly done before I told you what was wrong with your design and how to make it better. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.12.0/134 - Release Date: 14/10/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: one product in more categories
- Original Message -
From: <[EMAIL PROTECTED]>
To:
Sent: Tuesday, October 18, 2005 4:26 PM
Subject: Re: one product in more categories
> For the same project (below) I have problem with building table for
> product prices.
> In "regular" online store, price is usually part of the products table.
> But, I need a solution for multiple prices. E.g.
> QTY -2550 100 200
> Price - $1.59 $1.39 $1.19 $0.99
>
> Also, if product is On Sale I need to be shown both prices: regular and
> sale price
> QTY -2550 100 200
> Price -$1.59 $1.39 $1.19 $0.99
> Sale - $0.99 $0.99 $0.99 $0.99
>
> What would be structure of "Quantity" and "Price" tables?
>
> My guess:
>
> CREATE TABLE ac_quantities (
> quantity_id INT(8) NOT NULL AUTO_INCREMENT,
> quantity INTEGER(6) NOT NULL,
> product_id INTEGER(8) NOT NULL,
> PRIMARY KEY(quantity_id),
> INDEX ac_quantities_index1(quantity)
> );
>
>
> CREATE TABLE ac_prices (
> price_id INT(8) NOT NULL AUTO_INCREMENT,
> price FLOAT(10,2) NOT NULL,
> product_id INTEGER(8) NOT NULL,
> product_type ENUM('regular','sale') NOT NULL DEFAULT 'regular',
> PRIMARY KEY(price_id),
> INDEX ac_prices_index1(price)
> );
>
>
>
> How "close" am I?
> :)
>
Your guess at the solution is probably usable but it could cause
difficulties that can be avoided with a different design.
You could do what you want in a single table. It would look like this:
PRICE Table (Primary Key: Product_ID + Quantity)
===
Product_IDQuantityRegular_PriceSale_Price
--
1 251.59 0.99
1 501.39 0.99
1 100 1.19 0.99
1 200 0.99 0.99
2 25 3.99 -
2 50 3.59 -
The dash in the sale price is used to denote a null. As you probably know, a
null is a special value that means "unknown or not applicable". Here, a null
in a sale price means that there is no sale price for this combination of
product_ID and quantity; people have to pay the regular price.
I should mention one other possibility that you may want to consider in your
design.
In the example you've given, the sale price was the same for that product
regardless of the quantity; I'm guessing that is NOT the normal situation
and that sale price varies with quantity most of the time, at least
somewhat. For example, the sale price might be 0.99 for up to 100 items but
then 0.79 for quantities over 100. The design I just stated should work fine
for that case. However, if the sale price was always the same for a given
product regardless of the quantity, you could do the table a bit
differently: omit the Sale_Price column and create a row that was
specifically for the sale price; it could use a reserved quantity like 0 to
indicate that it was a sale price row:
PRICE Table (Primary Key: Product_ID + Quantity)
===
Product_IDQuantityRegular_Price
-
1 251.59
1 501.39
1 100 1.19
1 200 0.99
1 0 0.99
Here, the last row indicates that the sale price for Product 1 is 0.99
regardless of the quantity. This makes the table a bit smaller but still
shows all the data. You just have to remember that your query always
searches for a quantity of 0 to obtain the sale price.
Rhino
>
>
> [EMAIL PROTECTED] wrote:
>
> >"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote on 10/18/2005 01:50:20 PM:
> >
> >
> >
> >>Hi to all!
> >>
> >>I have tables products and categories
> >>
> >>CREATE TABLE categories (
> >> cat_id INT(6) NOT NULL AUTO_INCREMENT,
> >> cat_name VARCHAR(45) NULL,
> >> cat_description TEXT NULL,
> >> cat_parent INTEGER(4) UNSIGNED NULL,
> >> cat_status ENUM(0,1) NULL DEFAULT 0,
> >> PRIMARY KEY(cat_id),
> >> INDEX ac_categories_index1(cat_status)
> >>);
> >>
> >>CREATE TABLE products (
> >> product_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
> >> product_no VARCHAR(12) NULL,
> >> product_name VARCHAR(45) NULL,
> >> product_description TEXT NULL,
> >> product_colors TEXT NULL,
> >> product_includes TEXT NULL,
> >> product_catalog VARCHAR(45) NULL,
> >> product_status ENUM(0,1) NULL,
Re: Reporting question
I'm not *certain* of this but I'm pretty sure that SQL will never give you "zero rows" of the kind you want from a query on a single table. (Then again, I'm not an SQL Genius so maybe someone else can suggest something that would work.) However, you can probably accomplish what you want fairly easily by creating a second table. Populate the second table with every date in the date range you care about - such as every single date in 2005 - and then do an outer join between the second table and the first. That result should produce a row for every date, including the ones that aren't represented in the first table. SQL would normally give you a null in the "Hits" column for rows whose date didn't exist in the first table but you should be able to use a coalesce() function to convert that to a zero. Mind you, null already means "unknown or not applicable" so maybe that is sufficient. The second table need not be permanent; you could generate it as a temp table when you need it so that it includes the appropriate range of dates (or days). In fact, that might be safer: if you make the date table permanent, you will have a problem later once your report is run for a period that falls fully or partially after the highest date in the date table. I assume you can write a program or script on your own to generate the INSERT statements that contain the desired dates. If you need code examples on any aspect of this, let me know and I can probably knock something together. Also, if you're not clear about the concept I'm suggesting, let me know and I'll try to clarify with a short example. Rhino - Original Message - From: "Erich Beyrent" <[EMAIL PROTECTED]> To: Sent: Tuesday, October 25, 2005 3:00 PM Subject: Reporting question > This is probably a silly question, but here goes... I have a table that > logs access to web services, and I am looking to create a report to > track the number of times each service was accessed in a given month. > > I know I can loop through the days in the month in PHP and do a seperate > query for each day, but is there a way to do this in MySQL and populate > dates not accessed with zeros? In other words, if I have hits for the > 23rd and 25th, but not the 24th, can I get something like > > +++ > | Day| Hits | > +++ > | 23 | 46 | > | 24 | 0 | > | 25 | 156| > +++ > > Thanks in advance! > > -- > Erich Beyrent > -- > http://www.beyrent.net > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.1.361 / Virus Database: 267.12.5/148 - Release Date: 25/10/2005 > > -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.361 / Virus Database: 267.12.5/148 - Release Date: 25/10/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with syntax 4 creating 1 table
- Original Message - From: Ed To: mysql@lists.mysql.com Sent: Tuesday, October 25, 2005 6:40 PM Subject: Problem with syntax 4 creating 1 table Shawn green was very kindly helping me ouy trying to make mysql tables and I thought I had go the hang of it until I get this error-any ideas? Thanks a lot CREATE TABLE PurchasedProducts( `int_saleCart` INT AUTO_INCREMENT NOT NULL ,`int_ClientID` INT,`int_ProductID` INT,`int_Quantity` INT,`int_saleCart` PRIMARY KEY ( int_saleCart ) ) ENGINE = MYISAM MySQL said: #1064 - 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 'PRIMARY KEY ( int_saleCart ) ) ENGINE = MYISAM' at line 6 You need a comma before the "PRIMARY KEY" clause. That should solve your problem. In other words, change the second last line so that it looks like this: `int_saleCart`, PRIMARY KEY ( int_saleCart ) --^---- Rhino No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.361 / Virus Database: 267.12.5/148 - Release Date: 25/10/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with decimal values
- Original Message - From: "Tatjana Cukic" <[EMAIL PROTECTED]> To: "MYSQL ANSWERS" Sent: Wednesday, October 26, 2005 4:05 AM Subject: Problem with decimal values > Hi People, > > it is strange, i am using MySQL version 4.1 (so dont > expect some bugs) on Windows 2000, and if i create a > field (e.g. temperature) as > > temperature decimal(4,3) > > if i wanna insert the value 100, it is storing as > 99.999 > > does anybody knows the reason why doesnt show it as > 100.000 but as 99.999 when my size is defined as 4 but > not 2 > > Thanks a lot! In SQL, a definition of a DECIMAL column usually follows this pattern: DEC(x,y). In this pattern, 'x' is the number of digits in the entire number, i.e. the number of digits on BOTH sides of the decimal point, and 'y' is the number of decimal places. Therefore, when you define your decimal as DEC(4,3), you are saying that there will never be more than 4 digits in the entire number and three of those digits will be decimal places. The largest number you can store in a DEC(4,3) column is 9.999. If you want to be able to store 100 (or rather 100.000), you will need to change your definition to DEC(x,3) where 'x' is any value greater than or equal to 6. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.361 / Virus Database: 267.12.5/149 - Release Date: 25/10/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Now what's that called?
I have no idea what the product was called but if the email was sent to this mailing list, you should be able to find it on the MySQL site; they archive all of the traffic on the various MySQL mailing lists and have a search engine for locating it; all you need to do is throw appropriate search terms at it. The home page for the mailing lists is http://lists.mysql.com/. The item you want may very well be in the "MySQL Announcements" area. Rhino - Original Message - From: "Alexander Shaw" <[EMAIL PROTECTED]> To: Sent: Monday, October 31, 2005 2:37 PM Subject: Now what's that called? > Evening All, > > I knew I should have been paying attention. > > A while back I received an email about a software product that runs with > mysql for development error tracking/reporting and it's used by the mysql ab > team. Can't or the life of me remember what it's called. > > Can anyone help please? > > Alex > No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.6/151 - Release Date: 28/10/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.6/151 - Release Date: 28/10/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL help for qty Sold YTD...
I spent several minutes looking at your question and your data model and nothing jumped out at me that precluded you from determining the quantity of the this vendor's items sold via this data model. I might have missed something though. I'm a little concerned that your LineItem table appeared to have no primary key. In my opinion, the primary key of a line item table should be an order number (saleTranID?) and then a sequence number (1 for the first item on the order, 2 for the second, etc.) but you (apparently) have no primary key defined at all and don't have a sequence number either. However, that shouldn't keep this particular query from running or returning appropriate rows. I am also assuming that invID is an inventory ID - my brain kept reading it as "invoice ID" but I learned to ignore it ;-) - where an inventory ID uniquely identifies one product that you sell, e.g. invID 1 might be power supplies for Sony camcorders while invID 2 might be Palm Tungsten E PDAs. This is something I would normally call a product ID if I were doing the naming :-) If, in fact, invID *is* an invoice ID, i.e. something that uniquely identifies a particular sales transaction then there is something wrong which might explain why you're not getting any data. So, assuming I haven't misunderstood anything or simply missed something, I would be inclined to break the query down into chunks. Execute each chunk on its own andmake sure that each chunk delivers what you think it should. If it doesn't, either the query is wrong or the data isn't what you think it is. Verify that the data you expect is there by doing SELECTs against the relevant tables; if the data is there, it's got to be your query that is wrong. Inspect each chunk until you find the culprit(s) in either the SQL or the data. Also, for what it's worth, I would strongly suggest that you set up a test environment with a SMALL quantity of data in each table - 50 rows or less should be plenty for most situations - and try your queries against that test environment. That makes the testing process a lot less painful - why wait for many seconds or even minutes for the query to give you the wrong answer? - and let's you solve the problem faster. It might sound like a lot of work but it shouldn't be; just clone the "real" tables and then copy a small but representative sample of data from the real tables into the clones. You also asked about performance but there is no way anyone can comment on that without knowing a lot more about what indexes you have and, perhaps, which engine you are using. But, in my opinion, your first effort should be directed toward getting the query running correctly, THEN worry about making it go faster. Rhino - Original Message - From: <[EMAIL PROTECTED]> To: Sent: Friday, November 04, 2005 12:28 AM Subject: SQL help for qty Sold YTD... > I cannot figure this one out. I have a Category table with 50,000 > records, an Inventory table with over 2 million records. A Sales table > with 500,000 records. And a LineItem table with 800,000 records pairing > the Inventory ID with the Sales Transaction ID and Quantity. I need to > generate a Quantity sold year to date for a certain vendor. The vendor > code can be found in the Category table which has a relationship with > Inventory. I am trying a SQL statement like this: > > select sum(li.quantity) as qtysoldytd from LineItem li, Sales sa where > (li.saletranID=sa.saletranID and YEAR(sa.solddate)=2005) and li.InvID > IN (select invID from Inventory where categoryid IN (select categoryid > from Category where vendcode='AA')) > > this yields null when I know there are sales for that vendor in 2005. > Simplified schemas for the tables are as follows: > Category: > ++--+--+-+-+ + > | Field | Type | Null | Key | Default | Extra >| > ++--+--+-+-+ + > | vendcode | char(3) | YES | MUL | NULL| >| > | categoryID | int(10) unsigned | | PRI | NULL| > auto_increment | > ++--+--+-+-+ + > > Inventory: > +--+---+--+-+-+---+ > | Field| Type | Null | Key | Default | Extra | > +--+---+--+-+-+---+ > | categoryID | int(11) | YES | MUL | NULL| | > | invID| int(10) | | PRI | 0 | | > | itemnum | int(11) | YES | MUL | NULL| | > +--+---+--+-+-+---+ > > Sales: > +--+--+--+-+-+
Fw: Help with an SQL query
Oops, I meant to post this on the list AND copy the person asking the question Rhino - Original Message - From: "Rhino" <[EMAIL PROTECTED]> To: "Gobi" <[EMAIL PROTECTED]> Sent: Friday, November 04, 2005 1:46 PM Subject: Re: Help with an SQL query > I can't test this in MySQL- I'm using an older version of MySQL that doesn't > support subqueries - but it works in DB2 and it should do the trick for > getting the current weight of each VBS_id value: > > select VBS_id, date, weight > from VBS_table x > where date = > (select max(date) from VBS_table > where VBS_id = x.VBS_id); > > I'm assuming you are using a more version of MySQL which DOES support > subqueries! If not, you may be able to get the same result with temp tables > but I haven't tried that. Make sure to say something if you don't know how > to use temp tables to simulate subqueries. I'm dubious that this query can > be simulated with temp tables though > > (Rant: I REALLY wish people (all people, not just you) posting questions to > this mailing list would get in the habit of specifying which version of > MySQL they are using!! The answers to questions FREQUENTLY depend on the > MySQL version so it would really help reduce the size of answers if people > volunteered this information in the original question.) > > Also, I'm assuming that that MySQL supports correlated subqueries; I'm > really not sure so I'll let you try the actual query and see if it works for > you. > > Here's an explanation of how this query works, in case you've never seen one > like this before. > > This is called a correlated subquery; the key thing that makes it obvious > that this is a correlated subquery (in case you have to recognize one) is > that a correlation name, in this case 'x', appears in the FROM clause of the > outer query and again in the WHERE clause of the subquery. The subquery > appears in brackets in case you are not familiar with subqueries. > > A correlated subquery works backwards to a normal subquery. In a normal > subquery, the subquery is executed first and is only executed once: its > result is plugged into the outer query which then executes just once as > well. In a correlated subquery, both the outer query and the subquery are > executed _repeatedly_, possibly MANY times each. > > The outer query is executed in order to obtain one row, THEN the subquery is > executed to see if the row found by the outer query can be kept. In this > case, let's say that the outer query returned the first row of the table, > which has a VBS_id of 11 and a date of '10/3/2005': the subquery > determines the maximum (most recent) date for any row that has same VBS_id > as the one just found by the outer query; if the maximum date differs from > the date found by the outer query, the outer query row is discarded and does > not appear in the final result set. In this case, the maximum date for > VBS_ID is 10/8/2005 which is not equal to the value found by the outer > query, so that row is discarded. > > Having dealt with the first row of the outer query, the outer query executes > again and gets another row. Again, the subquery is executed to see if the > date is the same as maximum date for that VBS_id and again, the outer row is > only kept if its date matches the maximum date found by the subquery. And so > it goes, one row at a time, until the outer query has read every row of the > table; a single row of the outer query is obtained, then the subquery > determines if that row contains the maximum date for the VBS_id that was > just found by the outer query. The final result will contain only rows that > have the maximum dates for each VBS_id and will show the VBS_id, the maximum > date, and the weight at that date. That is the result you requested. > > Bear in mind that a correlated query can perform much more poorly than a > regular subquery, although the optimizer sometimes has tricks that make the > performance quite tolerable. > > There may be a solution that doesn't involve a correlated subquery but it's > not coming to me. However, others who are better at SQL may think of > something that does the job. > > Rhino > > > > - Original Message - > From: "Gobi" <[EMAIL PROTECTED]> > To: > Sent: Friday, November 04, 2005 3:05 AM > Subject: Help with an SQL query > > > > Not sure if this is the right place to ask. I have a table, Weight, > > with the following test data: > > > > idx VBS_ID DateWeight > > 11110/3/200511.5 > > 2119/5/2004 10 > > 3111
Re: Sub Selects, Alias Names and stored procedures
I don't have any idea about your second question but I have a thought on the
first one.
In DB2, which I use most of the time, you don't need to qualify a column
name like list_ID or acct_ID unless it is ambiguous. In this case, neither
one is ambiguous because both get used in single-table SELECT statements. If
I did those same queries in DB2, I would only expect an error message if I
did a join of two tables in which both tables had an acct_ID or list_ID;
then the SQL processor would get confused if the duplicated column names
weren't qualified in a SELECT list, GROUP BY, WHERE, or whatever.
Since DB2 and MySQL are presumably written to the same SQL standard, they
probably have the same rules; you only have to qualify a column name when it
is ambiguous.
That's my guess and I'm sticking to it until I hear otherwise from someone
more familiar with MySQL's implementation of SQL :-)
Rhino
- Original Message -
From: "Gordon Bruce" <[EMAIL PROTECTED]>
To: "MySQL List"
Sent: Friday, November 04, 2005 2:51 PM
Subject: Sub Selects, Alias Names and stored procedures
After reading one of the recent posts from Gobi [EMAIL PROTECTED]
I took his successful query and modified it for one of my tables. It indeed
produce the correct result, but in the process raised some questions.
1. Why do list_ID and acct_ID not have to be qualified with a table name or
alias?
mysql> SELECT list_ID, list_Name, acct_ID, list_Qty
-> FROM lists
-> INNER JOIN (SELECT acct_ID, max(list_ID) AS list_ID
-> FROM lists
-> GROUP BY acct_id
-> ) AS t
-> USING (acct_ID, list_ID)
-> WHERE list_Active = 'Yes'
-> AND cpny_ID = 'RER1'
-> LIMIT 100,10;
+-++-+--+
| list_ID | list_Name | acct_ID | list_Qty |
+-++-+--+
| 3 | Farm | BA8M | 0 |
| 10 | Woodbury | BA8Y | 100 |
| 2 | Brookview Heights 03-23-04 | BA9O | 278 |
| 4 | Magnet Mailing | BABA | 250 |
| 2 | Fall Back | BABM | 223 |
| 1 | Contact list | BACF | 71 |
| 4 | Friends/Family | BAE2 | 10 |
| 1 | St. Michael | BAE7 | 139 |
| 2 | JS Prospects | BAE8 | 196 |
| 1 | Home Focus | BAE9 | 55 |
+-++-+--+
10 rows in set (0.03 sec)
2. While the subselect does work, it appears to generate a cartesian
product. Initial guess with 5.0 and stored procedures would be that
CREATING TEMPORARY TABLE
INSERT max values in temporary
SELECT from main table joined with temporary
would run faster and still allow this to be done with 1 statement.
However, even though the explains would indicate that this was so {23508 *
7354 rows for subselect VS 6060 rows for temporary table}
actual times are {0.03 for subselect VS 0.19 for temporary table} . After
doing some playing, it is the INSERT into temporary that adds the
time even though the table was memory resident. Trying a similar request on
a table with 3.5M rows still favors the subselect
{27.50 sec for subselect VS 1 min 13.91 sec for temporary table}.
Has EXPLAIN just not caught up with SUBSELECT logic or is there something
else going on?
mysql> EXPLAIN
-> SELECT list_ID, list_Name, acct_ID, list_Qty
-> FROM lists
-> INNER JOIN (SELECT acct_ID, max(list_ID) AS list_ID
-> FROM lists
-> GROUP BY acct_id
-> ) AS t
-> USING (acct_ID, list_ID)
-> WHERE list_Active = 'Yes'
-> AND cpny_ID = 'RER1'
-> LIMIT 100,10;
++-+++--+-+-
+-+---+-+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
++-+++--+-+-
+-+---+-+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 7354 | |
| 1 | PRIMARY | lists | eq_ref | PRIMARY,acct_list_ID | PRIMARY | 6 |
t.acct_ID,t.list_ID | 1 | Using where |
| 2 | DERIVED | lists | index | NULL | PRIMARY | 6 | NULL | 23508 | Using
index |
++-+++--+-+-
+-+---+-+
3 rows in set (0.01 sec)
mysql> SELECT list_ID, list_Name, acct_ID, list_Qty
-> FROM lists
-> INNER JOIN (SELECT acct_ID, max(list_ID) AS list_ID
-> FROM lists
-> GROUP BY acct_id
-> ) AS t
-> USING (acct_ID, list_ID)
-> WHERE list_Active = 'Yes'
-> AND cpny_ID = 'RER1'
-> LIMIT 100,10;
+-++-+--+
| list_ID | list_Name | acct_ID | list_Qty |
+-++-+--+
| 3 | Farm | BA8M | 0 |
| 10 | Woodbury | BA8Y | 100 |
| 2 | Brookview Heights 03-23-04 | BA9O | 278 |
| 4 | Magnet Mailing | BABA | 250 |
| 2 | Fall Back | BABM | 223 |
| 1 | Contact list | BACF | 71 |
| 4 |
Re: Help with an SQL query
ure? Then, all you'd need to do is call that stored procedure every time you need that result. Rhino - Original Message - From: "Gobi" <[EMAIL PROTECTED]> To: Sent: Saturday, November 05, 2005 8:05 AM Subject: Re: Help with an SQL query > Rhino wrote: > > >I can't test this in MySQL- I'm using an older version of MySQL that doesn't > >support subqueries - but it works in DB2 and it should do the trick for > >getting the current weight of each VBS_id value: > > > >select VBS_id, date, weight > >from VBS_table x > >where date = > >(select max(date) from VBS_table > >where VBS_id = x.VBS_id); > > > >I'm assuming you are using a more version of MySQL which DOES support > >subqueries! If not, you may be able to get the same result with temp tables > >but I haven't tried that. Make sure to say something if you don't know how > >to use temp tables to simulate subqueries. I'm dubious that this query can > >be simulated with temp tables though > > > >(Rant: I REALLY wish people (all people, not just you) posting questions to > >this mailing list would get in the habit of specifying which version of > >MySQL they are using!! The answers to questions FREQUENTLY depend on the > >MySQL version so it would really help reduce the size of answers if people > >volunteered this information in the original question.) > > > >Also, I'm assuming that that MySQL supports correlated subqueries; I'm > >really not sure so I'll let you try the actual query and see if it works for > >you. > > > >Here's an explanation of how this query works, in case you've never seen one > >like this before. > > > >This is called a correlated subquery; the key thing that makes it obvious > >that this is a correlated subquery (in case you have to recognize one) is > >that a correlation name, in this case 'x', appears in the FROM clause of the > >outer query and again in the WHERE clause of the subquery. The subquery > >appears in brackets in case you are not familiar with subqueries. > > > >A correlated subquery works backwards to a normal subquery. In a normal > >subquery, the subquery is executed first and is only executed once: its > >result is plugged into the outer query which then executes just once as > >well. In a correlated subquery, both the outer query and the subquery are > >executed _repeatedly_, possibly MANY times each. > > > >The outer query is executed in order to obtain one row, THEN the subquery is > >executed to see if the row found by the outer query can be kept. In this > >case, let's say that the outer query returned the first row of the table, > >which has a VBS_id of 11 and a date of '10/3/2005': the subquery > >determines the maximum (most recent) date for any row that has same VBS_id > >as the one just found by the outer query; if the maximum date differs from > >the date found by the outer query, the outer query row is discarded and does > >not appear in the final result set. In this case, the maximum date for > >VBS_ID is 10/8/2005 which is not equal to the value found by the outer > >query, so that row is discarded. > > > >Having dealt with the first row of the outer query, the outer query executes > >again and gets another row. Again, the subquery is executed to see if the > >date is the same as maximum date for that VBS_id and again, the outer row is > >only kept if its date matches the maximum date found by the subquery. And so > >it goes, one row at a time, until the outer query has read every row of the > >table; a single row of the outer query is obtained, then the subquery > >determines if that row contains the maximum date for the VBS_id that was > >just found by the outer query. The final result will contain only rows that > >have the maximum dates for each VBS_id and will show the VBS_id, the maximum > >date, and the weight at that date. That is the result you requested. > > > >Bear in mind that a correlated query can perform much more poorly than a > >regular subquery, although the optimizer sometimes has tricks that make the > >performance quite tolerable. > > > >There may be a solution that doesn't involve a correlated subquery but it's > >not coming to me. However, others who are better at SQL may think of > >something that does the job. > > > >Rhino > > > > Wow, excellent explanation. By the way, my apologies... I am using > MySQL 5.0.15. I would be interested in knowing how to use temp tables > to simulate subqueries thoug
Re: LEFT JOIN problem
Have a look at the reply I just wrote for Gobi, entitled "Re: Help with an SQL query". You'll find an explanation there for how to use temporary tables and joins instead of subqueries in MySQL versions that don't support subqueries. Rhino - Original Message - From: "Guy Brom" <[EMAIL PROTECTED]> To: Sent: Saturday, November 05, 2005 10:28 AM Subject: LEFT JOIN problem > I have the following tables: > > languages > language_id int, > language_title varchar(80) > > objects > object_id int > object_language_id int > object_title varchar(100) > > I want to select ALL available languages, and match the translated object > (if it is exists) for a specific object_id. If it does not exists for that > specific language_id, I want to have NULL. > > Because I'm using mysql 4.0, no subqueries allowed. Is there any way to > rewrite the following? > > SELECT > language_id, > language_title, > (SELECT object_title FROM objects WHERE object_id = ? AND object_language = > l.id) AS object_title > FROM > languages l > > Thanks! > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.1.362 / Virus Database: 267.12.8/161 - Release Date: 03/11/2005 > > -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/161 - Release Date: 03/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Evaluating text as an expression
See comments interspersed below. Rhino - Original Message - From: "Duncan Miller" <[EMAIL PROTECTED]> To: Sent: Sunday, November 06, 2005 8:36 PM Subject: Evaluating text as an expression > I am trying to set up a table where the returned value is a result of > evaluating an expression that is stored as text (or varchar). > > The idea is to have a table with a couple of fields that can contain > numeric values or expressions eg > > Name Fred > Years 3 > Commission base 10% > Commission Commission Base + (Years * 2)% > > > I sort imagines that I could do it like SELECT Name, > Evaluate(Commission) or as a subquery. > Assuming you want to invoke this code with a function name, as in your example, what you're requesting is called a UDF (user-defined function). These are supported as early as MySQL 4.1. Basically, you create a function with a name of your choosing (usually with some restrictions), then write some code behind it to do the work you want. Then you drop that code into MySQL and it becomes just another function that you can use, just like the standard ones built into MySQL. See this page of the 4.1 manual for more information: http://dev.mysql.com/doc/refman/4.1/en/create-function.html. > Another example I have is to be able to store queries in a table and be > able to call them in one call to the database rather than through the > provider eg > > Select evaluate(queryText) from queryTable where queryId = x > > This is probably a bit more redundant now that 5 has stored procedures > etc but still... > I haven't seen the exact functionality you are describing in either DB2 or MySQL but what you are describing is not too different from stored procedures. A stored procedure is basically the name of some code that you can invoke, passing in parameters if you like, and that returns a result set. They are invoked via CALL statements though, not via SELECT statements. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/162 - Release Date: 05/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Evaluating text as an expression
The kind of thing you are talking about, where you execute a text string that is supplied at runtime, is called dynamic SQL. I've used it in the database which I use most of the time, DB2, but had never seen it in MySQL when I was making my initial reply to your question. I just had a look at the MySQL manual to see if it was there, since I was concerned that I might have simply overlooked it earlier. As it turns out, I *had* indeed missed the existence of dynamic SQL in MySQL! Mind you, the MySQL manual uses the term 'prepared statements' instead of 'dynamic SQL'. You didn't say what version of MySQL you are running but if you are using 5.0 or later, dynamic SQL is possible for you. Have a look at http://dev.mysql.com/doc/refman/5.0/en/sqlps.html. This would appear to be what you originally wanted. If you are on MySQL 4.1.x, prepared _SQL_ statements are, apparently, not available but you can accomplish the same effect with the C API. If you look at http://dev.mysql.com/doc/refman/4.1/en/c-api-prepared-statements.html and the subsequent parts of that chapter, you will see the API described. Now, I don't know how you feel about writing statements that aren't SQL but this may offer an acceptable way for you to do what you want to do. By the same token, Java offers ways to send text strings to a program at runtime and having them interpreted to return whatever you want. However, I know that some people want to be able to do everything in pure SQL without any application code in the picture; if that is your situation, you may want to upgrade to 5.0 or 5.1 so that you have the capabilities you need. Naturally, stored procedures and user defined functions are still valid ways of solving the problems you posed. I apologize profusely for giving you the impression that what you wanted was not available in MySQL; I simply had never looked for dynamic SQL capabilities in MySQL and had never stumbled across their existence in the manual when looking for other things. I spend most of my time in DB2 and only occasionally work with MySQL and my own copy of MySQL is 4.0.x which doesn't support prepared statements so I just never knew that they were there in the later releases. Sorry for any confusion I caused! Rhino - Original Message ----- From: Duncan Miller To: Rhino Cc: mysql@lists.mysql.com Sent: Monday, November 07, 2005 1:22 AM Subject: Re: Evaluating text as an _expression_ Thanks.I see what you mean. I used the 'Evaluate' as an example because in some code you can use that function to execute a text string as code. I sort of thought there may be something similar in SQL / MySQL to allow the execution of a resultant string as if it were code.And yeah a lot of things that were workarounds before can now be done as stored procedures.Of course I could just pass the string to a generic stored procedure to return the result. I'm assuming I can call a stored procedure within an SQL command. Will check it out further. Thanks againRhino wrote: See comments interspersed below. Rhino - Original Message - From: "Duncan Miller" <[EMAIL PROTECTED]> To: Sent: Sunday, November 06, 2005 8:36 PM Subject: Evaluating text as an _expression_ I am trying to set up a table where the returned value is a result of evaluating an _expression_ that is stored as text (or varchar). The idea is to have a table with a couple of fields that can contain numeric values or expressions eg Name Fred Years 3 Commission base 10% Commission Commission Base + (Years * 2)% I sort imagines that I could do it like SELECT Name, Evaluate(Commission) or as a subquery. Assuming you want to invoke this code with a function name, as in your example, what you're requesting is called a UDF (user-defined function). These are supported as early as MySQL 4.1. Basically, you create a function with a name of your choosing (usually with some restrictions), then write some code behind it to do the work you want. Then you drop that code into MySQL and it becomes just another function that you can use, just like the standard ones built into MySQL. See this page of the 4.1 manual for more information: http://dev.mysql.com/doc/refman/4.1/en/create-function.html. Another example I have is to be able to store queries in a table and be able to call them in one call to the database rather than through the provider eg Select evaluate(queryText) from queryTable where queryId = x This is probably a bit more redundant now that 5 has stored procedures etc but still... I haven't seen the exact functionality you are describing in either DB2 or MySQL but what you are describing is not too different from stored procedures. A stored procedure is basically the name of some code that you can invoke, passing in parameters if you like, and that return
Re: what's the relationship among 4.1, 5.0 and 5.1?
MySQL is like most other software; the higher version numbers indicate later releases of the code and later releases have more features than earlier releases. Therefore, 5.1 has more features than 5.0 and 5.0 has more features than 4.1. To see the new features added in each of these versions, see the appropriate manuals: - New features in 4.1: http://dev.mysql.com/doc/refman/4.1/en/nutshell-4-1-features.html - New features in 5.0: http://dev.mysql.com/doc/refman/5.0/en/mysql-5-0-nutshell.html The developers at MySQL are currently working on 5.1; they know what features they plan to put in it but some of them are probably not there at all while others are still fairly buggy. You can download 5.1 and run it but you shouldn't be surprised if some things don't work or don't work correctly. This might be the thing to do if you want to "play" with the new features that are being added in 5.1 but you probably don't want to use 5.1 for a production system yet. 5.0 (5.0.15 to be exact) stable for production use. 4.1 is also suitable for production use but has fewer features than 5.0. For example, if you need Views, you won't find 4.1 acceptable because it doesn't support Views while 5.0 does. So, look at the features lists and figure out what you need. Then, decide if you want a version of MySQL that you can use in production or just one that will let you play with new features for now and choose the appropriate bundle to download. Rhino - Original Message - From: "Bing Du" <[EMAIL PROTECTED]> To: Sent: Monday, November 07, 2005 11:27 AM Subject: what's the relationship among 4.1, 5.0 and 5.1? Hello, Anybody have a good pointer that could help explain why is 4.1, 5.0 and 5.1 respectively? 5.0 had been in development state for a while, now it's finally released. I know 5.0 has added more features. Would 4.x eventually be obsolete and replaced by 5.x? I guess I need some help to understand how there are several versions for the same software and suggestion on which one should be used? Thanks in advance, Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/162 - Release Date: 05/11/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/162 - Release Date: 05/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what's the relationship among 4.1, 5.0 and 5.1?
Oops, I clicked "Send" too soon! I meant to include a link for the features that are new to 5.1: http://dev.mysql.com/doc/refman/5.1/en/roadmap.html Rhino - Original Message - From: "Bing Du" <[EMAIL PROTECTED]> To: Sent: Monday, November 07, 2005 11:27 AM Subject: what's the relationship among 4.1, 5.0 and 5.1? Hello, Anybody have a good pointer that could help explain why is 4.1, 5.0 and 5.1 respectively? 5.0 had been in development state for a while, now it's finally released. I know 5.0 has added more features. Would 4.x eventually be obsolete and replaced by 5.x? I guess I need some help to understand how there are several versions for the same software and suggestion on which one should be used? Thanks in advance, Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/162 - Release Date: 05/11/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/162 - Release Date: 05/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Evaluating text as an expression
See remarks intererspersed in question. Rhino - Original Message - From: "Duncan Miller" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Cc: Sent: Tuesday, November 08, 2005 12:42 AM Subject: Re: Evaluating text as an expression > Thanks again, I am certainly learning a lot more about MySQL. > > It not quite what I was looking for but it does give me another option > to use. > > I am not really a developer or anything so I keep to fairly simple > things and like to use SQL and a scripting language. I can do what I > want on the client script side. > > I was thinking of something like the Javascript Eval method (and the > similar VBScript Eval function) that allow you to construct a string by > whatever means and then execute it as a command. This gives the scripter > some flexibility that probably is a lazy way to a better solution but ... > > Probably the best example I can offer is > > Table = MathsOperations > Val1 = 5 > Val2 = 3 > Operation = 'Val1 * Val2' > > Select Val1,Val2,Operation, Eval(Operation) From MathsOperations > > Would return > 5,3,'Val1 * Val2',15 > I understand what you're looking for. Prepared statements/dynamic SQL are the closest thing you'll find in standard SQL to your Eval() function in terms of handling a text string of your choice, typically generated at runtime, that is interpreted and executed at runtime. But they aren't USED like your Eval() function is used in your example. A user-defined function LOOKS like your Eval() function - because it IS a function - but you can't simply hand it any old text string and expect it to be evaluated. By the same token, a stored procedure doesn't quite fit your requirements because it is called in a statement by itself, e.g. call storedProc01(1, 'ABC', '1988-01-01'). There simply isn't anything that is *PRECISELY* what you want in SQL. You'll just have to think of your question a little differently - in the form of a UDF or stored procedure or prepared statements - in order to take advantage of those features. > As I say I can do this at the client side but it occurred to me that it > would be useful to be able to do it within SQL. But I am happy with what > I have learned. > Nobody says you have to use UDFs, stored procedures or prepared statements but it's good to know that they're there when you do need to use them :-) Good luck! > > > Rhino wrote: > > The kind of thing you are talking about, where you execute a text > > string that is supplied at runtime, is called dynamic SQL. I've used > > it in the database which I use most of the time, DB2, but had never > > seen it in MySQL when I was making my initial reply to your question. > > I just had a look at the MySQL manual to see if it was there, since I > > was concerned that I might have simply overlooked it earlier. > > > > As it turns out, I *had* indeed missed the existence of dynamic SQL in > > MySQL! Mind you, the MySQL manual uses the term 'prepared statements' > > instead of 'dynamic SQL'. > > > > You didn't say what version of MySQL you are running but if you are > > using 5.0 or later, dynamic SQL is possible for you. Have a look at > > http://dev.mysql.com/doc/refman/5.0/en/sqlps.html. This would appear > > to be what you originally wanted. > > > > If you are on MySQL 4.1.x, prepared _SQL_ statements are, apparently, > > not available but you can accomplish the same effect with the C > > API. If you look at > > http://dev.mysql.com/doc/refman/4.1/en/c-api-prepared-statements.html and > > the subsequent parts of that chapter, you will see the API > > described. Now, I don't know how you feel about writing statements > > that aren't SQL but this may offer an acceptable way for you to do > > what you want to do. By the same token, Java offers ways to send text > > strings to a program at runtime and having them interpreted to return > > whatever you want. However, I know that some people want to be able to > > do everything in pure SQL without any application code in the picture; > > if that is your situation, you may want to upgrade to 5.0 or 5.1 so > > that you have the capabilities you need. > > > > Naturally, stored procedures and user defined functions are still > > valid ways of solving the problems you posed. > > > > I apologize profusely for giving you the impression that what you > > wanted was not available in MySQL; I simply had never looked for > > dynamic SQL capabilities in MySQL and had never stumbled across their > > existence in the manual when looking for other th
Re: Best Fieldtype to store IP address...
- Original Message - From: "Peter M. Groen" <[EMAIL PROTECTED]> To: Sent: Friday, November 11, 2005 3:37 PM Subject: Re: Best Fieldtype to store IP address... > On Friday 11 November 2005 21:33, Cory @ SkyVantage wrote: > > I'm using MySQL-Cluster 5.0, and we're doing some research. > > > > What is everyone's opinion as to what the best fieldtype to store an IP > > address in? > > > > varchar(16) ? because 16 is the max chars of an ip address... > > char(16) ? > > text(16) > > > > Not quite sure how to get the best memory utilization... > Isn't there a new way to express IP addresses called IPV6(?) which has a possibility of 6 distinct parts instead of the traditional 4? I haven't seen one of these new formats myself yet but for all I know, they will become soon in the near future. Maybe you'd better choose a field type that can accomodate those as well as the traditional 123.123.123.123 type Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/165 - Release Date: 09/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Let mysql calculate levenshtein-distance
If you are asking whether MySQL already has a function that will calculate a levenshtein-distance, I'm pretty sure that the answer is no. You can check the MySQL manual yourself to be sure. The (English language manuals are at http://dev.mysql.com/doc/ and other translations of the manuals can also be accessed from that same page. You'll need to choose the appropriate manual based on your version of MySQL; then look in the Functions and Operators chapter.) If you are asking whether MySQL supports user-defined functions (UDFs), which allow you to add your own functions to MySQL, the answer is yes, provided you are using Version 5 of MySQL. That means that if you can write the logic needed to calculate a levenshtein-distance in C or C++, you can create your own UDF and add it to MySQL, then invoke it via the function name in your SQL. Possible problem: I see from your example that you want to put the function in your ORDER BY clause; I'm not sure if you can do *that* in MySQL; I think functions have to be invoked from the SELECT clause or HAVING clause or maybe a few other places; I've never seen a function invoked in an ORDER BY in either MySQL or DB2, which I know considerably better than MySQL. You may want to try writing a trivial function and then seeing if you can invoke it from an ORDER BY before you spend much time on the levenshtein-distance function Of course, you could always rewrite your query so that the function is invoked from the SELECT, like this: select word1,word2, dist(word1, word2) from myTable order by 3 where the '3' in the ORDER BY clause indicates the third column of the SELECT clause. That should be just as good as your original query. Rhino - Original Message - From: "Horst Jäger" <[EMAIL PROTECTED]> To: Sent: Monday, November 14, 2005 11:02 AM Subject: Let mysql calculate levenshtein-distance > > Hi, > > I have a nice little code snippet in pseudocode, which calculates the > levenshtein-distance between 2 words. > > Is it possible to implement that code in mysql like in > > "select word1,word2 from myTable order by dist(word1,word2 )" > > where dist is the function mentionend above which takes 2 strings and > returns an int? > > Weird question??? :) > > Thanks > > Horst > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 > > -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Will SELECT COUNT(*) FROM TABLE ever be cheap on INNODB?
Just to contribute a different point of view, let me tell you what happens in DB2. DB2 has positions in its catalog, also known as meta data, to keep all kinds of statistics about the data, including the number of rows in each table. However, the designers made an executive decision in the early days of DB2 that those statistics would NOT be dynamically maintained. Apparently, they feared that they would add a great deal of overhead to DB2 if it constantly updated statistics like the number of rows in each table, the range of values in each column of an index, the number of pages of data in the tablespace, etc. etc. etc. Instead, the provided a utility called RUNSTATS that could be run whenever the user liked. When executed, RUNSTATS refreshes all of the desired statistics for a particular table or index and brings them right up to date. The DB2 Optimizer, the component that chooses access paths for the data, then uses those statistics. (Of course, if you don't run RUNSTATS, the Optimizer uses the existing statistics, whatever they may be. Those statistics may be wildly inaccurate and can skew the access path selection process.) Now, with respect to getting decent performance from a COUNT(*) query, DB2 is quite smart: if there is an index on any column of the table, DB2 can simply go to the index, which is organized as a b-tree, and count the number of index entries. That tends to involve minimum I/O and is therefore frequently very fast. If there are multiple indexes on the table, it may even be able to reason out which index has the fewest number of entries to minimize the amount of counting it has to do but I'm not sure about that. Maybe the MySQL developers will want to think about using techniques like those I've just mentioned for getting a COUNT(*) result faster? Rhino - Original Message - From: "sheeri kritzer" <[EMAIL PROTECTED]> To: "Kevin Burton" <[EMAIL PROTECTED]> Cc: Sent: Monday, November 14, 2005 5:10 PM Subject: Re: Will SELECT COUNT(*) FROM TABLE ever be cheap on INNODB? Hi all, I know I'm a bit late in coming to this discussion. Glad to see that this problem is on the InnoDB to-do list. I will put out that one thing you can do is utilize triggers. Make a separate table with one field, and put a trigger on the table you want counted so that every time there is an insert to the table, you increment the field in the 2nd table. Add a second trigger to decrement the 'counter table' field every time a delete statement is issued. This should work. It's a hack, but if you need that implementation quickly, there ya go. Granted, you need MySQL 5.0. -Sheeri On 11/1/05, Kevin Burton <[EMAIL PROTECTED]> wrote: > MyISAM has a cool feature where it keeps track of the internal row > count so that > > SELECT COUNT(*) FROM FOO executes in constant time. Usually 1ms or so. > > The same query on INNODB is O(logN) since it uses the btree to > satisfy the query. > > I believe that MyISAM just increments an internal count so that every > insert/delete changes the count. > > Are there plans to add this to INNODB? I was relying on this for my > database monitoring app and noticed that it was killing my > performance (I forgot about this problem...) > > Would be really nice to have. > > Kevin > > Kevin A. Burton, Location - San Francisco, CA >AIM/YIM - sfburtonator, Web - http://www.feedblog.org/ > GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 > > > > -- > 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] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sub Query
That's pretty hard to do; you haven't said what the query is trying to do and you haven't supplied definitions of the tables you are using or provided a few rows of sample data so that we could form a mental picture of what you are trying to accomplish. Also, you haven't stated what version of MySQL you are using; if you are using a version prior to 4.1.x, subqueries aren't possible at all. Nevertheless, assuming that subqueries *are* possible in your version of MySQL, I can make an educated GUESS. The WHERE clause of your subquery looks wrong, particularly "messages.id". I would expect the WHERE to be constructed with one of the following: - with a constant, e.g. WHERE message_id = 10 - in the case of a correlated subquery, with another column name that refers back to the outer query, e.g. "WHERE message_id = x.message_id" (in this case, the correlation name, 'x' in this case, would also need to appear in the FROM clause of the OUTER query.) - in the case of a join, with another column name, e.g. WHERE a.message_id = b.message_id Your WHERE clause doesn't follow any of those patterns. I assume that's what MySQL doesn't like. Rhino - Original Message - From: "Herman Scheepers" <[EMAIL PROTECTED]> To: Sent: Sunday, November 13, 2005 11:53 AM Subject: Sub Query > Hi > > Could anyone help perhaps tell me why the following > simple query containing a sub-query gives a syntax > error. > > select 1 from messages > where not exists ( select 1 from > message_push_notifications > where message_id = > messages.id) > > > Thanx > Herman > > > > > __ > Start your day with Yahoo! - Make it your home page! > http://www.yahoo.com/r/hs > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 > > -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can this SQL query be done with MySql?
See remarks interspersed below Rhino - Original Message - From: "WARVIN BARKER" <[EMAIL PROTECTED]> To: Sent: Monday, November 14, 2005 10:44 AM Subject: Can this SQL query be done with MySql? > Hi! > > I have a MySQL table with perid (person id), CaseId (the latter two fields are varchar). > > The persons (perid) can be on more than one case. They can get married and change caseids, or they can come of age and get their own caseid. So a given perid can be associated with multiple caseids. > > I'd like (using just sql) to create a list of those people (perids) who have changed cases. So the list would show a perid and then a bunch of cases that they have been on. > What you're describing is called a many-to-many relationship by database designers. The way that many-to-many relationships are normally implemented are as follows. (I'm guessing you are dealing with some sort of welfare administration system so that will be what I show in my examples). Person Table (primary key = PerID) PerIDFirstNameLastNameBirthdate... (other columns about the person) P001 MarySmith 1960-01-01 P002 FredSmith 1980-01-01 P003 Elaine Jones 1982-01-01 Case Table (primary key = CaseID) CaseID... (other columns about the case) C001 C002 Person_Case Table (primary key = PerID *and* CaseID) PerIDCaseID... (other columns about this particular person and case) P001 C001 P001 C002 P002 C001 P003 C002 If I were in your situation, I'd probably store the start date and end date for the person's association with the case (and maybe the reasons for starting and ending the association) in the Person_Case table. For example: PerIDCaseIDStartDateStartReason EndDateEndReason P001 C001 1978-01-01 Quit school, no work - P002 C001 1980-01-01 Child born 1998-01-01 legally adult P002 C002 1998-01-01No work -- P003 C002 2000-01-01Married, no work available 2003-01-01 got job In this example, we have 4 rows in the Person_Case table: 1. Mary Smith went on welfare when she turned 18, quit school, and couldn't find work. She has never found work and remain on welfare now (the '-' is a common notation for 'null', meaning 'unknown or not applicable'). 2. In 1980, when she was 20, Mary had a son, Fred. He was associated with her case until he turned 18, then he was detached from the case because he was now too old to be on his mother's case. 3. Fred Smith is now 18 and gets his own case number. (Let's assume he can't find work either). 4. Fred Smith marries Elaine Jones in 2000. She is added to case C002 but finds a job in 2003, ending her association with the case. Now, when you want to know what cases a given person has been associated with, you simply query the Person_Case table. If you need to determine more information about the person or case, just join those tables to Person_Case. > My second question is, in MySql can we query a field just created in the query? So can you do something like this? sele count(*) as cnt, lastname from tablename where cnt>1 Here we are using a created field name in the query itself. Is this possible? > Certainly! You wouldn't do it quite that way though; you are not allowed to have that formulate the query the way your example shows (at least not in DB2, the database I use most; I'm pretty sure the same rule applies to MySQL). To accomplish what you want, you would do something like this: select PerID, count(*) from Person_Case group by PerID having count(*) > 1 Explanation: For each different person in the Person_Case table, determine the number of rows for that case, which is the number of cases that are (or have ever been) associated with; only display that person's ID if he/she is associated with more than one case. [If you only want to show cases that the person is currently associated with, add WHERE conditions to ensure that the current date is between the start date and end date for that association. Something like this: select PerID, count(*) from Person_Case where current_date >= StartDate and current_date <= EndDate group by PerID having count(*) > 1] -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question
Just to amplify what Michael has said If you had a table with 50 columns and created a separate index for each of those columns, then you'd probably get some performance benefit from having those indexes. But you'd also have to absorb the following overhead: 1. For each new row that you created, you'd have to add an entry to each of the 50 indexes on the table. 2. For each row that you deleted, you'd have to remove an entry from each of the 50 indexes on the table. 3. For each row that you changed, you'd have to change the index entries on all columns that changed. 4. For each index you build, you'd have to pay for building that index and you'd have to pay for whatever storage that index used. That adds up to a lot of overhead, likely far more than the benefit you got by indexing the columns in the first place. Rather than putting indexes on every column database designers tend to put them in the following places: 1. A unique index on the primary key (which is required on every foreign key to enable Referential Integrity). 2. A unique or non-unique index (as appropriate) on each foreign key to help performance of Referential Integrity and joins, which are frequently on foreign keys. 3. A unique or non-unique index (as appropriate) on any other columns where query performance is critical. 4. A non-unique index on the column (or combination of columns) that you want as your clustering key. (The clustering key governs the physical sequence in which data is stored.) [NOTE: I mostly use DB2 and clustering is a big deal there but I'm not sure if it works the same way in MySQL.] Rhino - Original Message - From: "Michael J. Pawlowsky" <[EMAIL PROTECTED]> To: "Jeffrey G. Ubalde" <[EMAIL PROTECTED]> Cc: Sent: Monday, November 14, 2005 8:19 PM Subject: Re: Newbie Question > Jeffrey G. Ubalde wrote: > > > Good day list! > > > > I would just like to ask a somewhat not so intelligent question. What > > is the downside of indexing almost all of the fields in a table? Is it > > advisable? > > > > Indexes have to be built... so if you did that, for every query that > alters the data many indexes will have to be written. A lot of extra > overhead if they will never be used. > Look to see where indexes are needed by the types of queries you are > writing. Add a slow query log to my.cnf. > This will give you a very good idea of where you might need some indexes. > Then trace the queries to make sure the indexes you've created are being > used. > > > Cheers, > Mike > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 > > -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Anyone use Snort and Acid?
> . . . . . . . . . . . . . . . . . . > Randomly Generated Quote (125 of 1011): > "It must be a peace without victory Only a peace between equals > can last." --Woodroe Wilson > Whoever generated this quote needs to use a spellchecker; the correct spelling is WOODROW Wilson. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql docs
Actually, many people use the term "subselect" where you prefer "subquery"; I've heard "subselect" quite a bit in 20+ years of working with relational databases. Not the MySQL people perhaps but it is a reasonable term to use in a search Rhino - Original Message - From: "Jasper Bryant-Greene" <[EMAIL PROTECTED]> To: "Scott Haneda" <[EMAIL PROTECTED]> Cc: "MySql" Sent: Wednesday, November 16, 2005 10:06 PM Subject: Re: Mysql docs > Scott Haneda wrote: > > Google this: > > subselect site:dev.mysql.com > > And I get mostly non English stuff, limiting to english and I get a whopping > > 37 pages, none of which seem to help me much. > > Try googling for "subquery", considering that's what they're called... > > Jasper > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.1.362 / Virus Database: 267.13.3/173 - Release Date: 16/11/2005 > > -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.3/173 - Release Date: 16/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A bit of SQL help for a MySQL novice.
le' Your new function would give you a version of the phone number that had no punctuation, then the mid() function would find the area code for you. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.3/173 - Release Date: 16/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A bit of SQL help for a MySQL novice.
The fact that the data is coming from the text logs doesn't really change
anything; _something_ is generating the text logs so that something could be
changed to force users to supply phone numbers in whatever format you want.
Of course, that doesn't mean _you_ can force those changes to take place; if
the text logs are coming from a customer, you may not be able to persuade
them to change the way they generate the logs. Your boss may not even want
you to explore the possibility with the customer for fear of ruffling
feathers with the customer(s).
If that is the case, I'd suggest writing a UDF (user-defined function) to do
the stripping of the punctuation for you, unless you can finagle the
existing MySQL functions to do the work for you. I'm surprised by how few
string functions MySQL supports. I use DB2 most of the time and it has lots
and lots of built-in functions, many of which are dedicated to string
manipulation. If you can't keep the punctuation out of the data in the first
place and you can't figure out how strip the punctuation with the existing
MySQL functions, I'd say a UDF is pretty much the _only_ way to get that
area code.
Rhino
- Original Message -
From: "Rick Dwyer" <[EMAIL PROTECTED]>
To:
Sent: Thursday, November 17, 2005 11:53 AM
Subject: Re: A bit of SQL help for a MySQL novice.
Unfortunately, the phone numbers come from text logs that get imported
into mysql. Because the phone number is often displayed on a document
for the customer, they will dictate how they want it to appear (i.e. with
( ) etc.). The phone logs simply record those values as they are so
data will be entered in an unclean manner.
Therefore I must deal with it on the backend.
Thanks for the pointers.
Rick
On Nov 17, 2005, at 11:15 AM, Rhino wrote:
- Original Message - From: "Rick Dwyer" <[EMAIL PROTECTED] link.com>
To:
Sent: Thursday, November 17, 2005 10:28 AM
Subject: A bit of SQL help for a MySQL novice.
Hello All.
I am hoping for a bit of help with some code that has really given me
some trouble. If this is not he correct forum for this any help in
pointing me to a more suited list would be appreciated.
I have a MySQL 4.1.x database containing records with phone numbers.
Most of the phone numbers are enter in 12035551212 format, but some
are entered with spaces or "-" or "(" or other characters.
I need to read the first 4 positions in the phone number to determine
it's location.
My statement looks like this:
'Select mid(phone, 1,4) as phoneareacode from phonetable'
This works but if the number is entered as 1(203)-555-1212 the above
would return "1(20" which is not what I am looking for.
Is there a way to have the select statement examine only numeric
values in the phone number so it would disregard the other charcters?
In Lasso, you can use a Replace with a Regular Expression function to
have just the digits 0-9 examined but haven't been able find a way to
do this in SQL.
Any help is appreciated.
You have two basic options:
1. Make the data uniform in format so that it is easily accessed.
2. Let the users input the data in whatever format they like and then
try to deal with it.
It looks like you have opted for the second choice. If it were me, I'd
_strongly_ prefer the first choice. I would put edits on the forms or
applications that prompt the user for the phone number and force the
input to match one format. For instance, if you prefer to see the phone
number as one long string of numbers, e.g. 12025551212, either ignore
any characters they type that aren't digits or strip out the punctuation
characters afterwards.
By the way, I'm _not_ saying that you should store the numbers as one
long string; there are other options but I would choose the one that was
going to be most useful to you based on your business requirements. If
the area code is going to be important to you, as it appears from your
question, it might be a good idea to store it in a separate column. For
instance, you could put the country code (the '1') in a Country_Code
column, put the area code in an Area_Code column, put the 7 digit number
in its own column, and then put the extension (if applicable) in yet
another column if that would help you. Beware of foreign phone numbers
though because they don't look like US ones (and don't make the mistake
of thinking that the '1' at the beginning of the phone number
automatically means the US; I'm in Canada and our phone numbers also
start with 1, our area codes are also three digits, and the rest of the
number is also 7 digits. Phone numbers in Europe or Africa or Asia
follow rather different patterns that are shared by Canada and the US.)
Now, your input routines _could_ mimic the way
Re: what function could detect a row locked by other client ?
I know a lot more about DB2, my main database, than MySQL. However, MySQL frequently does the same things in the same ways as DB2. _If_ MySQL behaves the same way as DB2 in this regard - and I do not know if it does - there is no function to determine if a given resource is locked. Instead, the program which is trying to get a lock but fails because another program has the necessary lock simply waits for a set amount of time. If the resource (table, row, or whatever) gets unlocked before the clock runs out, the waiting programs are permitted to try to get their own locks on the resource, basically on a first-come, first-served manner. If the clock runs out before the resource has been released by the first program, the waiting program(s) get return codes and messages that indicate that they timed out and what resource was not available. At that point, the program can decide to try again, as many times as it likes, or to give up. The timeout interval can be set/changed by the system administrator. Again, let me stress that this is how DB2 behaves. MySQL may very well behave differently. However, I have noticed a great many similarities between DB2 and MySQL so they may behave the same in this regard too. I'm looking forward to seeing other answers to your question from people who know exactly how MySQL behaves in this regard so that I can learn more about MySQL. Rhino - Original Message - From: "wang shuming" <[EMAIL PROTECTED]> To: Sent: Friday, November 18, 2005 11:06 PM Subject: what function could detect a row locked by other client ? Hi, I know select .. update could lock selected rows, if the connection not release the lock, others always wait. If I use get_lock() to get a logical lock by key xxxtable+xxxrow , but others may not update other database on the same server . What function could detect a row locked (by select ... update) by other client ? Best regard! Shuming Wang No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.4/175 - Release Date: 18/11/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.4/175 - Release Date: 18/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Finding the country name from country prefix
- Original Message - From: "abhishek jain" <[EMAIL PROTECTED]> To: Sent: Saturday, November 19, 2005 8:10 AM Subject: Mysql Finding the country name from country prefix Hi Friends, I have a ticky mysql problem. I need to find the country name from the country prefix so eg. i have a mysql table with prefix and name as the coumns and with datas like : Prefix, Name 1 USA 11XYZ 44UK 91India 92Pakistan 123 ZXF and i have a number like 911122334455 and i need t match that to india. I cannt do that directly by this statement select name from country_table where prefix='91'; for the simple reason as i do not have the length or the no. of digits the prefix is beforehand. Pl. help me out. Quick help will be appreciated. You are not explaining your problem very clearly at all, particularly why you think this is a MySQL problem. It looks to me as if you are trying to parse a phone number and determine from the first few digits what country that phone number represents. You already have a table that gives you the country code for each country and it shows clearly that 91 is the country code for India. As for the length of the country code, surely you can easily calculate that 91 has two digits in it. If you are saying that you have only a string of digits and need to determine the country in which that phone number originates without any further information, all I can say is good luck. The fact is that people write their phone numbers in many different ways. Here in Canada, if I'm giving my number to someone who lives within my city or region, I'm likely to give them only the last seven digits, e.g. 5551212. If I wanted to give my number to someone farther away in Canada, the US, or the Caribbean, I'd give them 5195551212 since we all share the same country code, 1. If I wanted to give my number to someone in some other foreign country, I'd give them 15195551212. So, right away, you have three different ways to express the phone number all of which are accurate and complete in their own context. If you parsed the first example, you might assume that I am in Brazil, because '55' is the country code for Brazil. (Country codes '5' and '555' are not in use at present.) If you parsed 519-555-1212, you wouldn't find anything because there is no country code '5', '51', or '519' currently in use. If you tried to parse '15195551212', you'd think I was in the US, Canada, or one of the Caribbean countries since '1' is the code for those countries. (There is is no '15' or '151' country code at present.) Therefore, the phone number _by itself_ is next to useless to you unless you are absolutely certain that the phone number is complete and includes the country code, area/city code and local number. None of that is a MySQL problem. The problem lies in your data acquisition technique. If you have to parse phone numbers, the input forms you use have to ensure that the user supplies the entire phone number; ideally, that number would be supplied in different fields, one of which would be the country code. Then you would have no problem except making sure that the user has supplied their own phone number and not someone elses. (The number I used in my examples, 15195551212, is the directory assistance phone number for my area, not my own phone number.) So, unless I've misunderstood what you are asking, I don't think we can help you very much. There is no function in MySQL or any other database I have used that can calculate the country code accurately given only a phone number that may or may not be complete. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.4/175 - Release Date: 18/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BIIIIIIIIIIG query
I don't have time today to answer your question in detail but the basic technique you need to use is an _OUTER_ join. Those should be completely compatible for version 4 and above of MySQL. A regular join, which is usually called an "inner" or "natural" join, combines rows of tables where the corresponding keys have equal _NON-NULL_ keys. (A 'null' is a special value that means "unknown" or "not applicable"). An outer join does the same work as an inner join but also picks up rows whose keys don't match any of the rows in the other table. There are three types of outer joins: - right outer join - left outer join - full outer join [The last time I looked, MySQL didn't directly support the full outer join but that was a couple of years ago; it may be supported in version 4.1 and above. Check the manual for yourself to see.] The "right" and "left" in "right outer join" and "left outer join" refer to the tables that are on the right and left hand sides of the query. For example, given: select name, address, salary from foo f inner join bar b on f.id = b.idno "foo" is the left hand table in the join and "bar" is the right hand table in the join; "foo" appears to the left of "bar" in the FROM clause. A right outer join does an inner join between the two tables in the join and then picks up the "orphans" (unmatched rows) from the right hand table. A left outer join does an inner join between the two tables in the join and then picks up the orphans from the left hand table. A full outer join does an inner join between the two tables in the join and then picks up the orphans from _both_ tables. I'm sure you can find some tutorials with examples of how to write various outer joins if you Google on "SQL tutorial". The MySQL manual didn't have much on joins beyond the statement syntax the last time I looked - many months ago - but MySQL uses standard SQL so _any_ SQL tutorial should have some good examples for you, even if it is intended for DB2 or Oracle users. When you understand the concepts and syntax, you should be able to apply this information to your specific problem. Rhino - Original Message - From: "bruno b b magalhães" <[EMAIL PROTECTED]> To: "MySQL List" Sent: Saturday, November 19, 2005 9:51 AM Subject: BIIG query Hi guys, I need some help with an query that I know I must use joins, but I can't figure out how to. The problem is, when the contact has an address, phone and email it works just fine, but I want it to display those how doesnt also! Could someone with JOINs experience help me? And one more question, how compatible are joins? I mean, the environment I am developing in is MySQL 4.1, but some of my clients are using 4.0.25 and others are using 5.0. The query is this: SELECT SQL_CACHE /* CONTACTS FIELDS */ contacts.contact_id AS id, contacts.contact_code AS code, contacts.contact_name AS name, contacts.contact_tax_id AS tax_id, contacts.contact_birth AS birth, (YEAR(CURDATE())-YEAR(contacts.contact_birth))-(RIGHT(CURDATE(),5) contacts.contact_timezone AS timezone, /* CONTACTS RELATED FIELDS */ contact_entities.contact_entity_name AS entity_name, contact_genders.contact_gender_name AS gender_name, contact_types.contact_type_name AS type_name, contact_types.contact_type_level AS type_level, contact_statuses.contact_status_name AS status_name FROM /* CONTACTS TABLES */ flx_contacts AS contacts, flx_contacts_to_contacts AS contacts_to_contacts, /* CONTACTS RELATED TABLES */ flx_contact_entities AS contact_entities, flx_contact_genders AS contact_genders, flx_contact_types AS contact_types, flx_contact_statuses AS contact_statuses, /* ADDRESSES TABLES */ flx_contacts_addresses AS addresses, flx_'contacts_address_types AS address_types, flx_contacts_to_addresses AS contacts_to_addresses, /* PHONES TABLES */ flx_contacts_phones AS phones, flx_contacts_phone_types AS phone_types, 'flx_contacts_to_phones AS contacts_to_phones, /* EMAILS TABLES */ flx_contacts_emails AS emails, flx_contacts_email_types AS email_types, flx_'contacts_to_emails AS contacts_to_emails /* JOINING CONTACTS RELATED TABLES */ WHERE contacts.contact_id = contacts_to_contacts.contact_child_id AND contacts.contact_entity_id = contact_entities.contact_entity_id AND contacts.contact_gender_id = contact_genders.contact_gender_id AND contacts.contact_type_id = contact_types.contact_type_id AND contacts.contact_status_id = contact_statuses.contact_status_id AND contacts_to_contacts.contact_parent_id = 0 /* JOINING ADDRESSES TABLES */ AND addresses.address_id = contacts_to_addresses.address_id AND contacts_to_addresses.contact_id = contacts.co
Re: Mysql Finding the country name from country prefix
- Original Message - From: "Michael Stassen" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Cc: "abhishek jain" <[EMAIL PROTECTED]>; Sent: Saturday, November 19, 2005 1:11 PM Subject: Re: Mysql Finding the country name from country prefix Rhino wrote: - Original Message - From: "abhishek jain" <[EMAIL PROTECTED]> To: Sent: Saturday, November 19, 2005 8:10 AM Subject: Mysql Finding the country name from country prefix Hi Friends, I have a ticky mysql problem. I need to find the country name from the country prefix so eg. i have a mysql table with prefix and name as the coumns and with datas like : Prefix, Name 1 USA 11XYZ 44UK 91India 92Pakistan 123 ZXF and i have a number like 911122334455 and i need t match that to india. I cannt do that directly by this statement select name from country_table where prefix='91'; for the simple reason as i do not have the length or the no. of digits the prefix is beforehand. Pl. help me out. Quick help will be appreciated. You are not explaining your problem very clearly at all, particularly why you think this is a MySQL problem. It looks to me as if you are trying to parse a phone number and determine from the first few digits what country that phone number represents. You already have a table that gives you the country code for each country and it shows clearly that 91 is the country code for India. As for the length of the country code, surely you can easily calculate that 91 has two digits in it. If you are saying that you have only a string of digits and need to determine the country in which that phone number originates without any further information, all I can say is good luck. The fact is that people write their phone numbers in many different ways. Here in Canada, if I'm giving my number to someone who lives within my city or region, I'm likely to give them only the last seven digits, e.g. 5551212. If I wanted to give my number to someone farther away in Canada, the US, or the Caribbean, I'd give them 5195551212 since we all share the same country code, 1. If I wanted to give my number to someone in some other foreign country, I'd give them 15195551212. So, right away, you have three different ways to express the phone number all of which are accurate and complete in their own context. If you parsed the first example, you might assume that I am in Brazil, because '55' is the country code for Brazil. (Country codes '5' and '555' are not in use at present.) If you parsed 519-555-1212, you wouldn't find anything because there is no country code '5', '51', or '519' currently in use. If you tried to parse '15195551212', you'd think I was in the US, Canada, or one of the Caribbean countries since '1' is the code for those countries. (There is is no '15' or '151' country code at present.) Therefore, the phone number _by itself_ is next to useless to you unless you are absolutely certain that the phone number is complete and includes the country code, area/city code and local number. None of that is a MySQL problem. The problem lies in your data acquisition technique. If you have to parse phone numbers, the input forms you use have to ensure that the user supplies the entire phone number; ideally, that number would be supplied in different fields, one of which would be the country code. Then you would have no problem except making sure that the user has supplied their own phone number and not someone elses. (The number I used in my examples, 15195551212, is the directory assistance phone number for my area, not my own phone number.) So, unless I've misunderstood what you are asking, I don't think we can help you very much. There is no function in MySQL or any other database I have used that can calculate the country code accurately given only a phone number that may or may not be complete. Rhino Everything you say is true, if the list contains incomplete phone numbers, but why do you assume that is the case? The OP said no such thing. The question is, given a string such as '911122334455', how do you find rows in the country_table where the prefix column matches the beginning of the string? I think that amounts to, how do I do string comparisons in mysql? Actually, it is the _original poster_ that is assuming the phone number is complete; I'm just trying to warn him that the problem becomes nearly insoluble if the phone number _isn't_ complete. Even if the number is complete, if we don't know the country associated with the phone number, which is the whole problem, how many digits of the number are the country code if the country code can be 1 thru 4 digits? Rhino -- No virus
Re: BIIIIIIIIIIG query
Paul, Do you have any idea if MySQL plans to support full outer joins at some point in the future? I realize you can probably fake them without having the syntax available but it would be nice to be able to get them directly Rhino - Original Message - From: "Paul DuBois" <[EMAIL PROTECTED]> To: "bruno b b magalhães" <[EMAIL PROTECTED]>; "MySQL List" Sent: Saturday, November 19, 2005 12:05 PM Subject: Re: BIIG query At 12:51 -0200 11/19/05, bruno b b magalhães wrote: Hi guys, I need some help with an query that I know I must use joins, but I can't figure out how to. The problem is, when the contact has an address, phone and email it works just fine, but I want it to display those how doesnt also! Could someone with JOINs experience help me? And one more question, how compatible are joins? I mean, the environment I am developing in is MySQL 4.1, but some of my clients are using 4.0.25 and others are using 5.0. Re: compatibility, it would be a good idea to read this section of the Reference Manual with regard to the changes made to join processing in MySQL 5.0.12 for compliance with standard SQL: http://dev.mysql.com/doc/refman/5.0/en/join.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.4/175 - Release Date: 18/11/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.4/175 - Release Date: 18/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Finding the country name from country prefix
- Original Message - From: "Michael Stassen" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Cc: "abhishek jain" <[EMAIL PROTECTED]>; Sent: Saturday, November 19, 2005 3:55 PM Subject: Re: Mysql Finding the country name from country prefix Rhino wrote: - Original Message - From: "Michael Stassen" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Cc: "abhishek jain" <[EMAIL PROTECTED]>; Sent: Saturday, November 19, 2005 1:11 PM Subject: Re: Mysql Finding the country name from country prefix Rhino wrote: - Original Message - From: "abhishek jain" <[EMAIL PROTECTED]> To: Sent: Saturday, November 19, 2005 8:10 AM Subject: Mysql Finding the country name from country prefix Hi Friends, I have a ticky mysql problem. I need to find the country name from the country prefix so eg. i have a mysql table with prefix and name as the coumns and with datas like : Prefix, Name 1 USA 11XYZ 44UK 91India 92Pakistan 123 ZXF and i have a number like 911122334455 and i need t match that to india. I cannt do that directly by this statement select name from country_table where prefix='91'; for the simple reason as i do not have the length or the no. of digits the prefix is beforehand. Pl. help me out. Quick help will be appreciated. You are not explaining your problem very clearly at all, particularly why you think this is a MySQL problem. It looks to me as if you are trying to parse a phone number and determine from the first few digits what country that phone number represents. You already have a table that gives you the country code for each country and it shows clearly that 91 is the country code for India. As for the length of the country code, surely you can easily calculate that 91 has two digits in it. If you are saying that you have only a string of digits and need to determine the country in which that phone number originates without any further information, all I can say is good luck. The fact is that people write their phone numbers in many different ways. Here in Canada, if I'm giving my number to someone who lives within my city or region, I'm likely to give them only the last seven digits, e.g. 5551212. If I wanted to give my number to someone farther away in Canada, the US, or the Caribbean, I'd give them 5195551212 since we all share the same country code, 1. If I wanted to give my number to someone in some other foreign country, I'd give them 15195551212. So, right away, you have three different ways to express the phone number all of which are accurate and complete in their own context. If you parsed the first example, you might assume that I am in Brazil, because '55' is the country code for Brazil. (Country codes '5' and '555' are not in use at present.) If you parsed 519-555-1212, you wouldn't find anything because there is no country code '5', '51', or '519' currently in use. If you tried to parse '15195551212', you'd think I was in the US, Canada, or one of the Caribbean countries since '1' is the code for those countries. (There is is no '15' or '151' country code at present.) Therefore, the phone number _by itself_ is next to useless to you unless you are absolutely certain that the phone number is complete and includes the country code, area/city code and local number. None of that is a MySQL problem. The problem lies in your data acquisition technique. If you have to parse phone numbers, the input forms you use have to ensure that the user supplies the entire phone number; ideally, that number would be supplied in different fields, one of which would be the country code. Then you would have no problem except making sure that the user has supplied their own phone number and not someone elses. (The number I used in my examples, 15195551212, is the directory assistance phone number for my area, not my own phone number.) So, unless I've misunderstood what you are asking, I don't think we can help you very much. There is no function in MySQL or any other database I have used that can calculate the country code accurately given only a phone number that may or may not be complete. Rhino Everything you say is true, if the list contains incomplete phone numbers, but why do you assume that is the case? The OP said no such thing. The question is, given a string such as '911122334455', how do you find rows in the country_table where the prefix column matches the beginning of the string? I think that amounts to, how do I do string comparisons in mysql? Actually, it is the _original poster_ that is assuming the phone number is complete; I'm just trying to warn him that the
Re: Is Load Data Infile or Update is faster?
- Original Message - From: "mos" <[EMAIL PROTECTED]> To: Sent: Saturday, November 19, 2005 8:16 PM Subject: Is Load Data Infile or Update is faster? I am doing a balance line comparison between the rows of an existing table and a text file that has newer data in it. So I'm comparing the values field by field to the existing rows in the table. If any of the field values are different, I need to update the table with these new values. The table has around 25 million rows and usually only 1% of the table needs to be compared. I've found 2 ways to update the table with the new values: 1) I could write the new values to a text file and then use "Load Data InFile REPLACE ..." which will replace the existing rows for the rows that need changing. The problem of course the REPLACE option means it will look up the old row using the primary key/unique key, deletes the row, then adds the new row. This is disk intensive. 2) The other option would be to execute an Update for each row that needs changing and set the changed columns individually. This means the existing row will not have to be deleted and only some of the existing row value(s) are changed. The problem is there could be 10,000 to 100,000 rows that need changing. So which option is going to be faster? A Load Data Infile that deletes the old row and adds a new one, or thousands of Updates changing only 1 to 6 values at a time? Any answer we could give you on the basis of the information you have provided would be based largely on assumptions that may not be true in your particular case. You haven't said a word about your hardware or database design or whatever indexes are on your data, listing only three of the more obvious factors that you have omitted, any of which could have huge impacts on the answer. Even if you told us all of that, the performance experts could probably only ballpark the answer. Wouldn't it be much, _much_ better if you did your own benchmark, using real data, on your own hardware and with your own database design and indexes to see which alternative would really work faster? Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.4/175 - Release Date: 18/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Country codes
- Original Message - From: "bruno b b magalhães" <[EMAIL PROTECTED]> To: "MySQL List" Sent: Sunday, November 20, 2005 10:15 AM Subject: Country codes Hi guys, I know it's a little bit off-topic, but does some one have a list with all countries of the world and their respective ISO codes (Like BRA, US, UK, etc.) and also their international calling codes (Like +55, +1, +32)? The ISO standard that governs country codes is ISO-3166. The standard includes various abbreviations for each country, including two letter and three letter abbreviations, called the Alpha-2 Code and Alpha-3 code respectively. I *think* the Alpha-2 code is the preferred abbreviation. You can see a complete list of the Alpha-2 codes at: http://www.iso.org/iso/en/prods-services/iso3166ma/02iso-3166-code-lists/list-en1.html I don't see the Alpha-3 codes at the ISO site but I may just have missed them. Look for yourself and you may find them. Or Google on ISO-3166 and you may find a list that shows Alpha-2 and Alpha-3 codes in the same list. With respect to telephone country codes, I *think* the relevant internation standard is called E.164, administered by the ITU (International Telecommunication Union). I found an English language PDF showing the country codes for telephones at http://www.itu.int/itudoc/itu-t/ob-lists/icc/e164_763.pdf. The same list is available in other languages and formats at http://www.itu.int/itudoc/itu-t/ob-lists/icc/e164_763.html. The lists are current as of May 1, 2005 and presumably reflect the current situation with respect to countries and telephones. Be careful when reading those lists! When I looked at page 3 in the English PDF, it said it was in numeric order and the list showed all the countries that use country code 1, then country 20 (Egypt), then country 210 (spare) *without* showing 55 (Brazil). Page 6 shows countries 500 through 509, then 51 through 58, and so on. Therefore, Brazil doesn't appear until Page 6. They have obviously chosen to sort the list only on the FIRST DIGIT of the country code; that's a pretty odd form of numerical order, in my opinion! Also, if you do a Google search on "telephone country codes", you should get plenty of hits that show essentially the same information: I got 14,600,000! The problem with any of those lists is that it will be difficult to tell if they are up-to-date; of the handful of those pages which I checked, none of them indicated when they were last updated. With respect to both the ISO country codes and the ITU telephone codes, you should make sure you update any lookup tables for these values regularly. New countries emerge - or old countries change their names - from time to time and those geopolitical changes will change either or both tables too. For instance, when Czechoslovakia split into the Czech Republic and Slovakia, both tables must have changed and when Zaire changed it's name back to the Congo, the tables must have changed again. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.4/176 - Release Date: 20/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SSH tunnel for Mysql
I use PuTTY (and WinSCP3) to attach my Windows machine to the Linux server holding my MySQL database. PuTTY is used to get the command line and WinSCP3 is the GUI I can use to do file transfers between the two machines. In PuTTY, there is a place where you can create SSH tunnels; it is located in the Connection/SSH/Tunnels page of the settings tree. You just use the bottom half of the Port Forwarding section of that screen to "add" new forwarded ports, specifying the source port, the destination, and choosing one of local, remote, or dynamic; then click Add and you should be ready to go. WinSCP3 also has an SSH section in its settings tree but I've never touched the defaults or set up a tunnel for it and it works fine. Rhino - Original Message - From: "Jerry Swanson" <[EMAIL PROTECTED]> To: Sent: Sunday, November 20, 2005 10:00 AM Subject: SSH tunnel for Mysql How to create ssh tunnel for Mysql? TH No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.4/176 - Release Date: 20/11/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.4/176 - Release Date: 20/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Country codes
I'm not saying it was an inappropriate way to present the data but it certainly was unexpected and, in my opinion, counter-intuitive. I didn't want the original poster to see page 3, see that his own country, Brazil, was missing, and dismiss the PDF as garbage. Rhino P.S. Sorry for top-posting but I can't intersperse normally with your email. - Original Message - From: "Björn Persson" <[EMAIL PROTECTED]> To: Sent: Sunday, November 20, 2005 7:21 PM Subject: Re: Country codes Rhino wrote: Be careful when reading those lists! When I looked at page 3 in the English PDF, it said it was in numeric order and the list showed all the countries that use country code 1, then country 20 (Egypt), then country 210 (spare) *without* showing 55 (Brazil). Page 6 shows countries 500 through 509, then 51 through 58, and so on. Therefore, Brazil doesn't appear until Page 6. They have obviously chosen to sort the list only on the FIRST DIGIT of the country code; that's a pretty odd form of numerical order, in my opinion! That's not normal numerical order of course, but it's exactly the order you need if you're parsing a phone number where you don't know beforehand how many digits are the country code. I suppose you could call it alphabetical order, only it's applied to digits instead of letters. Björn Persson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.4/176 - Release Date: 20/11/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.4/176 - Release Date: 20/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting no of different rows by group by clause
This query should tell you how many occurrences of each value you have in the table: select name, count(*) as count from mytable group by name order by count desc Rhino - Original Message - From: <[EMAIL PROTECTED]> To: Sent: Tuesday, November 22, 2005 11:25 AM Subject: Getting no of different rows by group by clause Hi Friends, I have a table like name MAD LHR MAD LHR AKL AWL AKL LHR I want the output as: LHR 3 AKL 2 AWL 1 etc... ie the no of entires sorted by their no of appearences. I cannot do that by select name from tab_name group by name as it will not give me the no of rows. Pl. help me to find it. I know it would be simple but i think i am missing some basic of MySQL. Thanks -- Regards Abhishek Jain mail2web - Check your email from the web at http://mail2web.com/ . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.5/177 - Release Date: 21/11/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.5/177 - Release Date: 21/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select command
- Original Message - From: "asus77x" <[EMAIL PROTECTED]> To: Sent: Thursday, November 24, 2005 2:07 AM Subject: select command I have a command : SELECT * FROM reg_one WHERE 'varName' like 'varTmp' % This is made within dreamweaver mx. I think there must be a typo in the statement you have provided. This syntax is not correct; the % symbol should be inside the single quotes, not following them. Also, you normally don't have two strings in the same WHERE condition. Perhaps you mean this, or something like it? SELECT * FROM reg_one where varName like 'varTmp%' varName and varTmp is a textselect. Does anyone can help how to make "reg_one" Becomes to like varName or varTmp ? I'm sorry but I'm not sure if I understand your question, it sounds as if English is not your best language :-) Are you saying that you want the table name to be a variable in your query? Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.7/181 - Release Date: 24/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select command
You should not contact people on the MySQL list offline; all communication should be via the list so that everyone can benefit from the discussion. As I said earlier, your WHERE clause does not make sense the way you expressed it in your first email. I explained how it should look in my first reply. It is possible to have the table name be a variable in the query. However, this requires something that MySQL calls "prepared statements", also called "dynamic SQL" in some databases. You can find out how to work with prepared statements in the MySQL manual; just search on "prepared statement". You probably need Version 4.1.x in order to use prepared statements but you should check that in the manual to be sure. Rhino - Original Message - From: "asus77x" <[EMAIL PROTECTED]> To: "'Rhino'" <[EMAIL PROTECTED]> Sent: Thursday, November 24, 2005 9:55 PM Subject: RE: select command Hi rhino, Yes, I want the table name, and those two other variable are variety in the query command. Could you advise the correct command ? Ps: my English is not good enough, so hope you can understand my question. Thanks. -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Thursday, November 24, 2005 10:05 PM To: asus77x; mysql@lists.mysql.com Subject: Re: select command I have a command : SELECT * FROM reg_one WHERE 'varName' like 'varTmp' % This is made within dreamweaver mx. I think there must be a typo in the statement you have provided. This syntax is not correct; the % symbol should be inside the single quotes, not following them. Also, you normally don't have two strings in the same WHERE condition. Perhaps you mean this, or something like it? SELECT * FROM reg_one where varName like 'varTmp%' varName and varTmp is a textselect. Does anyone can help how to make "reg_one" Becomes to like varName or varTmp ? I'm sorry but I'm not sure if I understand your question, it sounds as if English is not your best language :-) Are you saying that you want the table name to be a variable in your query? Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.7/181 - Release Date: 24/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Send instant messages to your online friends http://asia.messenger.yahoo.com -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.7/181 - Release Date: 24/11/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.7/181 - Release Date: 24/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking Opinions
- Original Message - From: "Robb Kerr" <[EMAIL PROTECTED]> To: Sent: Friday, November 25, 2005 11:59 AM Subject: Seeking Opinions I'm building a new clipart site. I need to have keyword searching. I'm seeking opinions about table design. Here are my proposed options. If anyone has any other suggestions, please make them. Option One Related tables. Table one (clipart pieces) contains ClipartID and ClipartName fields. Table two (keywords) contains KeywordID, ClipartID and Keyword fields. This option will create an incredibly large related table (keywords) with each piece of clipart having tens of related fields in the keyword table. But, searching ought to be fast. Option Two Single table. Table one (clipart pieces) contains ClipartID, ClipartName and Keywords fields. The Keywords field would be a long text field that would be searched with a full-text search. Searching and maintenance would be easier but would searching be slowed down significantly? Please provide any input you have and make any alternate suggestions. I'm not sure if you'll gain or lose by putting the keywords in a separate table. Your description of the data is too vague. Could you possibly type an example of a few rows of each scenario so that we can see what will actually be in the Keywords columns in each scenario? It would also be VERY useful to know what the primary and foreign keys of each table are going to be. There is one major performance issue that you don't appear to have considered yet: how will the clipart images themselves be stored? Are you going to store each one as a blob in the data row itself? Or are you going to store a URL or other URL-like description of where the clipart image is found? The latter approach keeps the MySQL tables very small and may give you performance advantages but also make your job a bit more complicated: you have to maintain some kind of directory structure for your clipart files and keep them consistent with the URL that you store in the database. I've barely touched blobs in MySQL so I don't feel qualified to recommend either approach to you from my own experience but I *think* the consensus among people with more blob experience is that the second approach I mentioned is better. However, it would be very wise of you to check the archives for this mailing list - search on 'blob' - to be sure I am getting that right. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.7/182 - Release Date: 24/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL HAVING statement ?
ization and that you don't need to use HAVING (or GROUP BY) in the first place. Therefore, file your new knowledge about GROUP BY and HAVING away for future reference - you'll need them again some day - and let's fix your original query. You are trying to do some kind of search on a concatenation of first name and last name. You obviously know about the concat() function so let's use it correctly. Assuming you need the concatenated name to be displayed in the result set, you need something like this: SELECT CONCAT(people2.First_Name, ' ', people2.Last_Name) AS zNAME, events.name_short, events.date_start FROM people2 INNER JOIN event_people ON people2.ID = event_people.peopleID INNER JOIN events ON event_people.eventID = events.ID WHERE event_people.people_role = 'FACULTY' AND zNAME LIKE '%' ORDER BY events.date_start DESC Note that I've used single quotes where you used double quotes; your approach may work okay in MySQL but I believe the SQL standard is to use single quotes. The other thing I've done is to add a wild card character to the expression you use in your LIKE clause; a LIKE clause implies that you are searching for something based on a pattern and a pattern always involves wild cards. (If you don't want a pattern, then just use an = operator instead of LIKE.) The main change I made was to replace 'HAVING' with 'AND'; the revised query simply has two search conditions, connected by 'AND' in its WHERE clause. Try this and see what happens. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 25/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL HAVING statement ?
- Original Message - From: "Brett Harvey" <[EMAIL PROTECTED]> To: Sent: Saturday, November 26, 2005 9:45 PM Subject: Re: SQL HAVING statement ? "Rhino" <[EMAIL PROTECTED]> wrote: Second, you're using the HAVING clause incorrectly in your query. HAVING only works with GROUP BY, i.e. you MUST have a GROUP BY to be able to use HAVING. (Note: You can have a GROUP BY without using HAVING but you cannot use HAVING unless a GROUP BY is present.) Since you have no GROUP BY, there is no way that this query will ever work. I disagree. SELECT CONCAT(people2.First_Name, ' ', people2.Last_Name) AS zNAME, events.name_short, events.date_start FROM people2 INNER JOIN event_people ON people2.ID = event_people.peopleID INNER JOIN events ON event_people.eventID = events.ID WHERE event_people.people_role = 'FACULTY' AND zNAME LIKE '%' ORDER BY events.date_start DESC This wouldn't work. select userfirstnm, userlastnm, concat(userfirstnm, ' ',userlastnm) as zname from FMS.WebUsers_sql where zname Like "%brett%" results in Unknown column 'zname' in 'where clause'. There "where" must be on the real column name. "Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This is because when the WHERE code is executed, the column value may not yet be determined. " http://dev.mysql.com/doc/refman/4.1/en/problems-with-alias.html Okay, I admit I was crossing my fingers a bit with that answer: I assumed that the alias would work in the WHERE; apparently, I was overly optimistic. However, select userfirstnm, userlastnm, concat(userfirstnm,' ',userlastnm) as zname from FMS.WebUsers_sql having zname Like "%brett%" works. Having must come after any grouping, but a group by is not required. Per the documentation "A HAVING clause can refer to any column or alias named in a select_expr in the SELECT list or in outer subqueries, and to aggregate functions. (Standard SQL requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions.)" The concat is the aggregate function. However, it works on just aliases also. SELECT userfirstnm as first, userlastnm, from FMS.WebUsers_sql having first Like "%brett%" Okay, once again I stand corrected. In my own defense, I should say that I am primarily a DB2 user and only use MySQL sporadically. In 20 years of working with DB2, I have never seen a query with a HAVING but no GROUP BY work. Since MySQL behaves like DB2 in virtually every case I've seen, I just assumed that rule also applied to MySQL. Apparently, I'm wrong in this case. What made this work was simply using the % he had forgotten SELECT CONCAT(people2.First_Name, " ", people2.Last_Name) AS zNAME, events.name_short, events.date_start FROM people2 INNER JOIN event_people ON people2.ID = event_people.peopleID INNER JOIN events ON event_people.eventID = events.ID WHERE event_people.people_role = "FACULTY" HAVING zNAME LIKE "%%" ORDER BY events.date_start DESC Well, at least I got _that_ right ;-) Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 25/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select returning more than I want
- Original Message -
From: "Scott Haneda" <[EMAIL PROTECTED]>
To: "MySql"
Sent: Sunday, November 27, 2005 6:03 AM
Subject: Select returning more than I want
I am not sure why this is, perhaps some join I am doing wrong. I am
getting
back 2 results, or records, identical to each other, where I only want one
record.
SELECT o.id, o.s_first_name, o.s_last_name, u.email
FROM orders AS o
INNER JOIN users AS u
ON (o.user_id = u.id)
INNER JOIN order_items AS i
ON (i.order_id = o.id)
INNER JOIN products AS p
ON (i.product_id = p.id)
WHERE o.status IN ('pre-order', 'delayed')
AND (o.delayed_ship_date <= DATE_ADD(NOW(), INTERVAL 1 DAY)
OR o.delayed_ship_date = '-00-00')
AND o.authnet_failures < 5
AND o.id NOT IN (0)
AND o.authnet_status IN ('empty', 'failed')
Is it possible that one of your tables does not have a primary key defined
on it? That would be the most likely cause of the problem in my experience.
Check your table definitions and if any of the them is missing a PRIMARY KEY
clause, that's your likely culprit.
The other possibility that comes to mind is that one or more of your tables
has a multi-column key but that you are only joining on part of the key. In
that case, you could also get duplicate rows. Review each and every join and
make sure you are joining on all of the appropriate columns.
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 25/11/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY two clauses
Your PDF is not very clear at all to me. Is the first part trying to describe the original table and identify the columns? Or is is pseudo code of some kind? Is the table you present the table that the query will read or is it the expected result? Your example query has a WHERE clause that says "selected = 1" but I don't see a column named "Selected". What do you mean by "positioning"? If you provide a definition of the original table and ideally a few sample rows from it, and describe what you want the query to do a lot more clearly, someone might be able to help you but right now, I have no idea what you are trying to do. Rhino - Original Message - From: "Jad Madi" <[EMAIL PROTECTED]> To: Sent: Sunday, November 27, 2005 3:49 AM Subject: ORDER BY two clauses guys, I have little question, but to make it more clear I wrote it with example of what i want on this pdf, http://www.easyhttp.net/files/query.pdf please advise. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 25/11/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 25/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL database design documentation
- Original Message - From: "Maurice van Peursem" <[EMAIL PROTECTED]> To: Sent: Sunday, November 27, 2005 6:33 PM Subject: MySQL database design documentation Hi, I'm relatively new to the database-scene. I've installed MySQL on Mac OSX 10.3, which was easy. I've installed Perl support for MySQL, which was suprisingly difficult. I've installed CocoaMySQL (http://cocoamysql.sourceforge.net/) to create, inspect and backup databases. And now I'm building my first database, and that is not as easy as I had hoped. I know that use of the 'JOIN' keyword can save me pages of Perl code, but how it works exactly is not yet clear to me. Therefore I'm looking for a book, or maybe other documentation (on the web?), that can point me in the right direction. More specifically, I'm looking for a book that explains how to design and build databases, with examples of the queries in MySQL. Most books describe how you install MySQL, and list the SQL commands, but this information I already have. Can any of you suggest to me some helpful learning material? For the most part, _any_ good database design book for _any_ decent relational database should do the job for you. That's because all (?) of the professional grade databases use the same SQL and the same normalization techniques to decide what columns belong in what tables and what primary and foreign keys should be used. Therefore, a good design book for DB2 or Oracle or Sybase would probably tell you almost exactly the same things as a good design book specifically intended for MySQL. You will still need to use the MySQL reference to help you with places where the MySQL syntax is slightly different than the syntax used by the other database but this really shouldn't happen too often. However, if you want a design book specifically written for MySQL, you may want to look at http://www.informit.com/articles/article.asp?p=30885&rl=1. I should stress that I don't have this book, nor have I read it cover to cover. But the sample chapter on database design is pretty good, so, if the rest of the book is as good, you should come out okay. In fact, you may find that the sample chapter alone, which you can read online for free, may tell you everything you really want to know and save you the cost of the book. No guarantees on that but it's a starting point anyway. By the way, I have not seen any other MySQL Design books so there may be others that are better. The URL I've given you actually mentions some other books specifically for MySQL that may suit your personal learning style better. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 25/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert query problem
- Original Message - From: "Jeff" <[EMAIL PROTECTED]> To: Sent: Tuesday, November 29, 2005 11:42 AM Subject: Insert query problem All, I can't get this query to run, it keeps compaining that there is a problem: The Query: insert into tickets(id,from,department,subject,body,lastaction,lastpost,priority,sta tus,created,fromname,lastpostname,attach,ct,uniq,notify,replyto) values(null,'jmckeon','1','test','test test',unix_timestamp(now()),'"[EMAIL PROTECTED]" <[EMAIL PROTECTED]>','Medium','Open',unix_timestamp(now()),'Jeff McKeon','Jeff McKeon','','','ks5hslajdfasd','1','[EMAIL PROTECTED]') The error: You have an error in your SQL syntax near 'from,department,subject,body,lastaction,lastpost,priority,status,create d,fromnam' at line 1 I suspect it doesn't like the fact that I have a field named "from" but I KNOW it's possible to write to this table and that field, I just can't figure out the correct syntax to get it to accept the fieldname "from". One of my least favourite aspects of MySQL is the vague error messages, like the one you quote in your post. I keep hoping that they will make them much clearer but it doesn't seem to have happened yet In any case, you may well be right that MySQL doesn't like you having a column named 'from'. Most dialects of SQL tolerate that kind of thing but usually require escape characters of some kind around words like 'from', which are keywords, when they are used outside of their normal purpose. I've never had the desire to call a column 'from' so I've never learned the escape character technique for MySQL so I would suggest the following possibilities: - change your column name so that _isn't_ a keyword. That would be my first choice if it was my database. - search the MySQL manual for 'escape character' and/or 'keyword'; with a bit of luck, it is documented there somewhere - search the MySQL mailing list archives. I'm virtually positive I've seen the matter come up in the list but I just don't remember the escape syntax at the moment. If you escape the column named 'from' and the query still doesn't work, please repost so that we can investigate other possibilities. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.10/186 - Release Date: 29/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW commands.
- Original Message - From: "Michael Williams" <[EMAIL PROTECTED]> To: Sent: Tuesday, November 29, 2005 11:30 PM Subject: SHOW commands. Hi all, Is there a command similar to "SHOW CREATE TABLE. . ." that will output the commands to fully duplicate a table; data and all? I want to retrieve the command and write it to a text file. Basically what I need is a "SHOW" on "CREATE TABLE copy SELECT * FROM original", but SHOW doesn't seem to work here. I need a copy of this command so that I can then replicate that table as often as desired in the future on whatever system is in place. I could obviously dump the entire db, but i only want this on a per table basis, as I deem necessary, whenever I deem it so. Any help would be greatly appreciated. I think there is an option of mysqldump that lets you dump just a single, specified table. Maybe that would do the trick for you? If not, and assuming you are running an appropriate version of MySQL, would a query on INFORMATION_SCHEMA.TABLES get you what you want? I'm still on V4.0.x so I'm not completely clear on what is available in the INFORMATION_SCHEMA. If all else fails and you have a Java developer handy, you can write Java code to determine the composition of tables, even in MySQL 4.0.x; I know because I've done it. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.10/186 - Release Date: 29/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Clear Screen in MySQL?
- Original Message - From: "untz" <[EMAIL PROTECTED]> To: Sent: Friday, December 02, 2005 10:47 PM Subject: Clear Screen in MySQL? Hello there, Is there a way to clear the screen in MySQL's command line prompt (mysql>)? I am using OS X Tiger with MySQL 5 I don't have a Mac and I'm not running MySQL 5 but when I was at the MySQL prompt in Linux running MySQL Version 4.0.15 just now, the following worked fine: \! clear Since 'clear' is a Linux operating system command and Linux and Mac OS X are both forms of Unix, this command might very well work fine for you too. Please note that the '\! ' in front of the clear command is used to tell MySQL to pass the command back to the OS to handle. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.10/189 - Release Date: 30/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select questions
- Original Message - From: "Kevin Fricke" <[EMAIL PROTECTED]> To: Sent: Monday, December 05, 2005 3:14 PM Subject: Select questions Hello allnew to the list...having a bit of an issue here. I have a reservations table that is linked to three separate tables, food, packages and options. A reservation can have multiple food options, packages and options attached to it. I am trying to run a query that will pull all of these out in the query without all of the duplicate records. Is this possible? Can I pull a select list into one query result field. For example a reservation may look like this: Reservation ID: 1 Client Name: Kevin Food -- Nachos Tacos Ice Cream Packages -- Live Music Casino Options -- Margarita Machine Bartender Do I have to run three queries to get the food, packages and options? I was hoping that this could be consolidated into one query. Thanks for the help!! You should be able to get the data you want in a single result set by using an SQL technique called "joining", assuming the tables have columns in common. The syntax for doing a join is explained in the MySQL manual for your particular version of MySQL. The manuals for each version can be found at this link: http://dev.mysql.com/doc/ However, the manuals don't do a great job of explaining the concept of the join. I just Googled on SQL Tutorials to see if I could find a decent tutorial that would show joining. Sadly, I did not find a really good tutorial that showed all of the join types and also included a three table join. However, this one - http://www.w3schools.com/sql/sql_join.asp - does a pretty decent job of showing some of the main two table joins. It's also nice and short. I suggest you read this page as carefully as you can and see if you can get the concept of joining from it. You may even want to create the two tiny tables they use for their examples and try the actual joins with those tables. As you will see, this short tutorial shows you inner joins, left joins and right joins, all of which are supported in MySQL. However, it doesn't show you a few other join types which are supported in MySQL, like the self-join. Unfortunately, I didn't see a tutorial that showed all of the join types supported by MySQL. Perhaps someone else can suggest a tutorial like that. The good news is that some of the more obscure join types like self-joins aren't used a lot. (They can be very handy in some situations but you won't come across those situations too often.) A three table join is really not much harder than a two table join; the concept remains the same. The exact syntax depends on which join type you use. The syntax for a three table inner join (i.e. Table A is inner-joined to Table B and the result of that join is inner-joined to Table C) follows this example: --- Select a.col4, a.col2, a.col3, b.col6, c.col1, c.col9 from Table_A a join Table_B b on a.col1 = b.col2 join Table_C c on c.col5 = a.col1 --- As you can see, you need to know what each table has in common with the other table(s) participating in the join and then include those conditions in the ON subclause of the FROM clause. Hmm, I've just found another short tutorial - http://www.techbookreport.com/sql-tut3.html - that actually shows a three table inner join. You might want to have a look at this one after you look at the first tutorial I suggested. I wouldn't be surprised if some of what I've just told you is not very clear to you because you may lack the concepts to follow the rather rushed and superficial explanations. Unfortunately, I don't have the time to sit down and write a really good SQL tutorial right now that would explain everything in adequate detail. I'm going to leave it at this for the moment in the hope that it helps give you an idea how to proceed. If you want any further explanation, post back to this mailing list with specific questions and someone will probably be able to help you further. Please don't be intimidated by this note: joins are actually pretty easy to do. Most people understand them pretty quickly given a few examples. Unfortunately, I just don't have the time to explain the concepts and work through a few good examples right now. But I think you'll see that you understand joins pretty quickly once you see a decent tutorial. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.12/192 - Release Date: 05/12/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select questions
I'm copying the list on this reply so that everyone can benefit from the discussion Thanks for clarifying that you understand joining. The way your question was worded, I thought perhaps you were a newbie who had never heard the concept before; my apologies for misunderstanding. The most common cause of duplicate rows in queries is that you have omitted one or more join conditions. However, before we can be sure that this is the cause of your particular problem, I'd like to get a few pieces of information from you: 1. What version of MySQL are you using, e.g. 4.0.15? 5.0.16? 2. What are the definitions of the three tables that you are using in your queries? I'm especially interested in the primary keys of those tables. 3. What are the queries that are returning the duplicate rows? 4. If possible, could you show us a few sample rows of each table? It really helps me visualize the data better. Please don't include hundreds, thousands or millions of rows! Just a handful of typical rows for each table should be plenty. 5. What expected result did you want for the query that is giving you trouble? 6. What is the actual result that you are getting? You just haven't given enough information in your question so far for me to diagnose your problem with any certainty or give you a solution. If you answer my questions, anyone here with a bit of SQL background should be able to help you. Rhino - Original Message - From: <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Sent: Monday, December 05, 2005 5:58 PM Subject: Re: Select questions I understand joins. However, when I join the three tables, it will contain duplicate reservation id's. If a reservation has three food options, then the reservation will be duplicated in the result set three times. Kevin -Original Message- From: "Rhino" <[EMAIL PROTECTED]> Subj: Re: Select questions Date: Mon Dec 5, 2005 4:00 pm Size: 2K To: <[EMAIL PROTECTED]>, - Original Message - From: "Kevin Fricke" <[EMAIL PROTECTED]> To: Sent: Monday, December 05, 2005 3:14 PM Subject: Select questions Hello allnew to the list...having a bit of an issue here. I have a reservations table that is linked to three separate tables, food, packages and options. A reservation can have multiple food options, packages and options attached to it. I am trying to run a query that will pull all of these out in the query without all of the duplicate records. Is this possible? Can I pull a select list into one query result field. For example a reservation may look like this: Reservation ID: 1 Client Name: Kevin Food -- Nachos Tacos Ice Cream Packages -- Live Music Casino Options -- Margarita Machine Bartender Do I have to run three queries to get the food, packages and options? I was hoping that this could be consolidated into one query. Thanks for the help!! You should be able to get the data you want in a single result set by using an SQL technique called "joining", assuming the tables have columns in common. The syntax for doing a join is explained in the MySQL manual for your particular version of MySQL. The manuals for each version can be found at this link: http://dev.mysql.com/doc/ However, the manuals don't do a great job of explaining the concept of the join. I just Googled on SQL Tutorials to see if I could find a decent tutorial that would show joining. Sadly, I did not find a really good tutorial that showed all of the join types and also included a three table join. However, this one - http://www.w3schools.com/sql/sql_join.asp - does a pretty decent job of showing some of the main two table joins. It's also nice and short. I suggest you read this page as carefully as you can and see if you can get the concept of joining from it. You may even want to create the two tiny tables they use for their examples and try the actual joins with those tables. As you will see, this short tutorial shows you inner joins, left joins and right joins, all of which are supported in MySQL. However, it doesn't show you a few other join types which are supported in MySQL, like the self-join. Unfortunately, I didn't see a tutorial that showed all of the join types supported by MySQL. Perhaps someone else can suggest a tutorial like that. The good news is that some of the more obscure join types like self-joins aren't used a lot. (They can be very handy in some situations but you won't come across those situations too often.) A three table join is really not much harder than a two table join; the concept remains the same. The exact syntax depends on which join type you use. The syntax for a three table inner join (i.e. Table A is inner-joined to Table B and the result of that join is inner-joined to Table C) follows this example: --- --- message trun
Re: Select questions
Once again, I'm copying the mailing list so that others can contribute to - and benefit from - the discussion. It's past my bedtime so I'm going to leave you in the capable hands of Michael and Shawn :-) Rhino - Original Message - From: "Kevin Fricke" <[EMAIL PROTECTED]> To: "'Rhino'" <[EMAIL PROTECTED]> Sent: Monday, December 05, 2005 9:47 PM Subject: RE: Select questions Forgot to include the query. select r.id, r.reservation_date, f.name as food_name, p.name as package_name, e.name as extra_name from reservations r left join reservation_food_details fd on r.id = fd.reservation_id left join food f on fd.food_id = f.id left join reservation_package_details pd on r.id = pd.reservation_id left join packages p on pd.package_id = p.id left join reservation_extra_details ed on r.id = ed.reservation_id left join extra_options e on ed.extra_id = e.id order by id desc -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Monday, December 05, 2005 5:19 PM To: mysql; [EMAIL PROTECTED] Subject: Re: Select questions I'm copying the list on this reply so that everyone can benefit from the discussion Thanks for clarifying that you understand joining. The way your question was worded, I thought perhaps you were a newbie who had never heard the concept before; my apologies for misunderstanding. The most common cause of duplicate rows in queries is that you have omitted one or more join conditions. However, before we can be sure that this is the cause of your particular problem, I'd like to get a few pieces of information from you: 1. What version of MySQL are you using, e.g. 4.0.15? 5.0.16? 2. What are the definitions of the three tables that you are using in your queries? I'm especially interested in the primary keys of those tables. 3. What are the queries that are returning the duplicate rows? 4. If possible, could you show us a few sample rows of each table? It really helps me visualize the data better. Please don't include hundreds, thousands or millions of rows! Just a handful of typical rows for each table should be plenty. 5. What expected result did you want for the query that is giving you trouble? 6. What is the actual result that you are getting? You just haven't given enough information in your question so far for me to diagnose your problem with any certainty or give you a solution. If you answer my questions, anyone here with a bit of SQL background should be able to help you. Rhino - Original Message - From: <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Sent: Monday, December 05, 2005 5:58 PM Subject: Re: Select questions I understand joins. However, when I join the three tables, it will contain duplicate reservation id's. If a reservation has three food options, then the reservation will be duplicated in the result set three times. Kevin -Original Message- From: "Rhino" <[EMAIL PROTECTED]> Subj: Re: Select questions Date: Mon Dec 5, 2005 4:00 pm Size: 2K To: <[EMAIL PROTECTED]>, - Original Message - From: "Kevin Fricke" <[EMAIL PROTECTED]> To: Sent: Monday, December 05, 2005 3:14 PM Subject: Select questions Hello allnew to the list...having a bit of an issue here. I have a reservations table that is linked to three separate tables, food, packages and options. A reservation can have multiple food options, packages and options attached to it. I am trying to run a query that will pull all of these out in the query without all of the duplicate records. Is this possible? Can I pull a select list into one query result field. For example a reservation may look like this: Reservation ID: 1 Client Name: Kevin Food -- Nachos Tacos Ice Cream Packages -- Live Music Casino Options -- Margarita Machine Bartender Do I have to run three queries to get the food, packages and options? I was hoping that this could be consolidated into one query. Thanks for the help!! You should be able to get the data you want in a single result set by using an SQL technique called "joining", assuming the tables have columns in common. The syntax for doing a join is explained in the MySQL manual for your particular version of MySQL. The manuals for each version can be found at this link: http://dev.mysql.com/doc/ However, the manuals don't do a great job of explaining the concept of the join. I just Googled on SQL Tutorials to see if I could find a decent tutorial that would show joining. Sadly, I did not find a really good tutorial that showed all of the join types and also included a three table join. However, this one - http://www.w3schools.com/sql/sql_join.asp - does a pretty decent job of showing some of the main two table joins. It's also nice and short. I suggest you read this page as carefully
Re: Select questions
- Original Message - From: "Michael Stassen" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; ; "'Rhino'" <[EMAIL PROTECTED]> Sent: Monday, December 05, 2005 10:01 PM Subject: Re: Select questions [EMAIL PROTECTED] wrote: Thank you for the table structures (I prefer the output from SHOW CREATE TABLE..) Now, would you mind also posting the actual query you used to produce what you are calling "duplicated" results? Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine I would think that would be obvious from his sample output: SELECT r.id, r.reservation_date, f.food_name, p.Product_Name AS package_name, e.extra_name FROM Reservations r JOIN Food_Details fd ON r.ID = fd.Reservation_ID JOIN Food f ON f.ID = fd.Food_ID JOIN Product_Details pd ON r.ID = pd.Reservation_ID JOIN Products p ON p.ID = pd.Product_ID JOIN Extra_Details ed ON r.ID = ed.Reservation_ID JOIN Extra_Options e ON e.ID = ed.Extra_ID WHERE r.ID = 425; Now, what is not obvious to me is why you and Rhino think the solution will be a JOIN. That's a fair question. I'm really not certain that the original poster needs a join. That is simply the initial impression I formed from the wording of his question, which I think we can agree was somewhat vague. He wanted to get information from three differently-organized tables into a single result set: that feels like a join to me. It also sounded like he had never considered the possibility of a join, perhaps because he was a newbie who'd never heard of the concept of a join before. That got me into explaining the concepts and looking for tutorials that covered joins. As his first reply to the thread showed though, he was already familiar with joins and I'd misunderstood where he was coming from. There are 3 separate lists. How will a single query join 3 lists without producing a cross product of the 3 lists? If he's really determined to do this in a single query, isn't a UNION required, as I suggested earlier? Something like (SELECT r.id, r.reservation_date, 'food ' AS item, f.food_name AS detail FROM Reservations r JOIN Food_Details fd ON r.ID = fd.Reservation_ID JOIN Food f ON f.ID = fd.Food_ID WHERE r.ID = 425) UNION (SELECT r.id, r.reservation_date, 'package' AS item, p.Product_Name AS detail FROM Reservations r JOIN Product_Details pd ON r.ID = pd.Reservation_ID JOIN Products p ON p.ID = pd.Product_ID WHERE r.ID = 425) UNION (SELECT r.id, r.reservation_date, 'extra ' AS item, e.extra_name AS detail FROM Reservations r JOIN Extra_Details ed ON r.ID = ed.Reservation_ID JOIN Extra_Options e ON e.ID = ed.Extra_ID WHERE r.ID = 425); What am I missing? That said, I don't see any reason to do this in one query. I think that's just confusing the sql query with the desired format of the app's output. After already finding the reservation id and date with a previous query, I would simply query each list separately: SELECT f.Food_Name FROM Reservations r JOIN Food_Details fd ON r.ID = fd.Reservation_ID JOIN Food f ON f.ID = fd.Food_ID WHERE r.ID = 425; SELECT p.Product_Name FROM Reservations r JOIN Product_Details pd ON r.ID = pd.Reservation_ID JOIN Products p ON p.ID = pd.Product_ID WHERE r.ID = 425; SELECT e.Extra_Name FROM Reservations r JOIN Extra_Details ed ON r.ID = ed.Reservation_ID JOIN Extra_Options e ON e.ID = ed.Extra_ID WHERE r.ID = 425; It really should be trivial to use the results of those three queries to produce the desired output from the app. What is the advantage of a single-query solution? What you're saying all seems quite reasonable but I really can't judge yet since I'm still not very clear on what he is really trying to accomplish. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.12/192 - Release Date: 05/12/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: SELECT all except ... ?
Oops, I meant to send this to the original poster _and_ the list :-) Rhino - Original Message - From: "Rhino" <[EMAIL PROTECTED]> To: "Frank Rust" <[EMAIL PROTECTED]> Sent: Friday, December 09, 2005 9:02 AM Subject: Re: SELECT all except ... ? - Original Message - From: "Frank Rust" <[EMAIL PROTECTED]> To: Sent: Friday, December 09, 2005 1:59 AM Subject: SELECT all except ... ? Is there a possibility to select all columns from a table except one or two columns? For example I have a table with 30 columns and want all columns but one column *not*. Do I have to write a very long select statement with 29 column names that i want to get? I've heard requests for "Select all except" for years now but have never seen anyone implement it. Mind you, I've only ever used two SQL databases seriously, DB2 and MySQL, so I can't be sure that Oracle or one of the others doesn't have this ability. The only thing that I've seen which comes close to what you describe is that DB2 has a set of dialogs that can be used to generate SQL. These dialogs let you select your table name(s) from a list, then select your column name(s) from a list, etc. When you select column names, there is a button for selecting all columns in the table(s), which is the equivalent of "Select *" if you were coding your own SQL. Once you've clicked that button, you can select one, several or all of the columns that were chosen for the query and de-select them again. Therefore, if you clicked "Select all", then de-selected one or two of the columns, it would have the same effect as you want. Aside from that, I'm not sure why the syntax you want couldn't be added to the SQL language so maybe you should ask for it via a feature request. I could easily imagine a change to SQL that would allow something like this: select * except e.salary, d.deptname from employee e inner join department d on e.workdept = d.deptno Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 09/12/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
