Re: Multiple primary keys

[Gordon]

MySQL will not use the primary key unless you use the left most columns. For 
a 1 column primary key then it's easy. For a 2 column primary key you can 
either use the 1st column in the primary key or both columns. If you only 
reference the 2nd column the query will not use the primary key and will do 
a full table scan.


In your case you are referencing "classb" which is not the left most collumn 
in the primary key set. Put the word "explain" preceding  the statement and 
execute the query. it will show you what keys are used in the query and in 
your case it is none. You either need to define another KEY with classb as 
the 1st column in the definition or if all of your queries at least 
reference classb then you could rebuild the primary key and put classb as 
the 1st entry in the definition.
- Original Message - 
From: "nngau" <[EMAIL PROTECTED]>

To: "'Kishore Jalleda'" <[EMAIL PROTECTED]>
Cc: 
Sent: Thursday, April 27, 2006 9:11 AM
Subject: RE: Multiple primary keys



Thanks all. The query I run is a subquery.

I noticed joined query run a lot faster than the sub.

This is the subquery:
select * from class_c where detail_id in (select classC from
item_classification where classb="216") order by detail;

This query takes nearly 3 minutes, before it did not take that long. I 
guess

I should use a primary key As an index.

I want to be able to add items that I can classify into different classa,
classb or classc.

Example:

Itemid 1025 ClassA: 101 classB: 218 classC: 356

Same item can be put into another class.

Itemid 105 ClassA: 101 classb: 218 classC: 357

So not having a primary key/index will slow my queries?


-Original Message-
From: Kishore Jalleda [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 27, 2006 10:49 AM
To: nngau
Cc: mysql@lists.mysql.com
Subject: Re: Multiple primary keys

On 4/27/06, nngau <[EMAIL PROTECTED]> wrote:


Can someone figure out what's going on. This is the only change I made
to this table. Basically I don't want any duplicate rows, so I setup 4
fields to be my primary key.

When I do a simple select query it takes nearly 30 seconds to complete.
This is affecting my websites and taking a very long time to query the
Products. Have I setup this table right? Thank You!!

+-+-+--+-+-+---+
| Field   | Type| Null | Key | Default | Extra |
+-+-+--+-+-+---+
| itemID  | int(6)  |  | PRI | 0   |   |
| classA  | int(3)  |  | PRI | 0   |   |
| classB  | int(3)  |  | PRI | 0   |   |
| classC  | int(3)  |  | PRI | 0   |   |
| picture | varchar(10) | YES  | | NULL|   |
| sex | char(2) | YES  | | NULL|   |
+-+-+--+-+-+---+


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




You can avoid duplicate rows with only one primary key, unless you have a
specific reason for having your primary key span on four columns.
Your query being slower depends on how you have indexed your columns
relating to your queries. Please give us a sample query which is running
slowly..

Kishore Jalleda
http://kjalleda.googlepages.com/projects


--
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]

RE: Baffled by error

[Gordon]

Try this 

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
GROUP BY a.account_id
HAVING   SUM(c.agent_product_time) >= '500'
ORDER BY mins

You have to do the SUM with the GROUP BY before you can test the
criteria

-Original Message-
From: Mike Blezien [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 16, 2006 10:30 AM
To: MySQL List
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...

Mike(mickalo)Blezien
===
Thunder Rain Internet Publishing
Providing Internet Solution that Work
http://www.thunder-rain.com
===

-- 
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]

RE: Accountability with MySQL

[Gordon]

And now we are down to reality. This is a MySQL list. Views are a wonderful
thing for creating an isolation layer between the application and the
database. However, MySQL's current implementation makes it extremely
difficult in many cases to avoid full table scans when you define the
logical view. {Not poking at the development team I think 5.x is a huge step
forward}. 

So you can't hide the underlying structure currently in MySQL. Like all
other things we have to commingle best practices with pragmatism. In other
words do what makes sense while at the same time acquiring a good knowledge
of fundamentals. {Not just slap name address phone1  phone n in some
table with an auto-increment ID field and say your done with the design.}

By the way, Date and Codd {both way above me in math and theory} had this
discussion ~20 years ago over a 6 month period in pages and pages of
Database magazine. In the end I believe they agreed to disagree.

-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 16, 2006 5:33 AM
To: mysql@lists.mysql.com
Subject: Re: Accountability with MySQL

From: <[EMAIL PROTECTED]>
> "Martijn Tonies" <[EMAIL PROTECTED]> wrote on 16/03/2006 11:02:32:
>
> > Well, the question still is if you should store "unknown" at all ;)
> >
> > Not according to Date: you should store what is known. See the remarks
> > about the "true propositions", from which relational databases are
> derived
> > (but you probably know that).
>
> As someone totally unread in the theory of databases, that seems unduly
> puritanical. I assume that what Date would propose is that you have
> another table (related by master key) in which, if you do not know
> something, you do not enter it. But this means that if you have 10
> different pieces of potentially but not necessarily available information
> about a single master record (e.g. a person), you have to do a 10-way join
> in order to retrieve all the information about them. Replacing a
> theoretically ugly null flag with a 10 way join strikes me, as an engineer
> rather than a theoretician, the wrong side of the elegance/practicality
> trade-off.

Using NULLs as well as de-normalization brings the risk of
integrity problems to your storage, storing what is right is only
a good thing.

And when it comes to having to writing JOINs for all your queries,
lo and behold, I bring you the wonder of the VIEW.

;-)



Martijn Tonies
Upscene Productions
http://www.upscene.com


-- 
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]

RE: getting table metadata

[Gordon]

What you are looking for is the INFORMATION_SCHEMA views, but they are not
available before 5.0. Until then you have to parse the "show create table"
or "DESCRIBE tablename" may be easier to parse 

mysql> describe organizations;
+---+---
+--+-+-+---+
| Field | Type
| Null | Key | Default | Extra |
+---+---
+--+-+-+---+
| orgn_ID   | char(4)
| NO   | PRI | |   |
| orgn_Name | char(50)
| YES  | | NULL|   |
| orgn_Billing_Type | enum('Bank Transfer','Credit
Card','Invoice','none','Purchase Order') | YES  | | NULL
|   |
| orgn_Internal_ID  | char(15)
| YES  | | NULL|   |
| orgn_Active   | enum('Yes','No')
| NO   | | Yes |   |
| orgn_Who  | char(4)
| NO   | | |   |
| orgn_Timestamp| timestamp
| YES  | | CURRENT_TIMESTAMP   |   |
| orgn_Create   | datetime
| NO   | | 2000-01-01 00:00:00 |   |
+---+---
+--+-+-+---+
8 rows in set (0.27 sec) 

-Original Message-
From: Yves Glodt [mailto:[EMAIL PROTECTED] 
Sent: Friday, March 17, 2006 9:22 AM
To: mysql@lists.mysql.com
Subject: Re: getting table metadata

On Friday 17 March 2006 15:52, Martijn Tonies wrote:
> Hello Yves,

Hello Martijn,

> > is it possible to get information about tables by doing queries on some
>
> system
>
> > tables? I am using mysql version 4.1.11 on debian sarge.
> >
> > In my case I need to know which columns (names and types) a table has,
> > and
>
> how
>
> > the primary key is defined.
> >
> > How can I get this information out of mysql by only using sql ?
>
> Have a look at the SHOW commands in the documentation.

I know about the "show create table ..." but it doesn't really satisfy my 
needs... Is there really no other way apart of parsing "create table" 
statements?

(I needed to this with firebird, and found all I could dream of in the RDB$ 
tables)

Best regards,
Yves

> As for system tables, MySQL 4.1 hardly has any.
>
> Martijn Tonies
> Database Workbench - development tool for MySQL, and more!
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com

-- 
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]

RE: Some queries use 100% CPU after restore

[Gordon]

Have you tried Repair table or if InnoDB 
ALTER TABLE ENGINE=InnoDB;

Sometimes I've noticed after a restore or after adding lots of rows
performance is slow. REPAIR or the ALTER TABLE fixes it.


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, March 17, 2006 2:37 PM
To: mysql@lists.mysql.com
Subject: Some queries use 100% CPU after restore
Importance: High

I'm moving a database to a new server.  I'm using MySQL v5.0.16 
on 'Windows.  I used the MySQL Administrator to backup on the old 
system and restore on the new one.  Everything is fine _except_ on the 
new server, some queries take 2-3 minutes with MySQL using 100% of the 
CPU.  I've dropped unneeded views, done maintenance, verfied that all 
the indices I expect are there, etc.  What's very, very strange is 
that it seems that the long queries are for old records and records 
created since the move are OK.  I realize this is a vague request but 
I've been poking at this for a long time without getting any good 
clues or making any real headway.  Any kind of brainstorming on things 
to check would be very welcome.  Thanks.

   Chris

-- 
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]

RE: Easy regex replace?

[Gordon]

If "%20" are the actual characters in the varchar column you shuld be able
to do 
UPDATE table 
SETcolumn_name =REPLACE(column_name,'%20',' ');

You might have to use REPLACE(column_name,'\%20',' '); 
to force MySQL to treat "%" as an actual value instead of a wild card.

-Original Message-
From: Pooly [mailto:[EMAIL PROTECTED] 
Sent: Monday, March 20, 2006 5:11 AM
To: MySQL General
Subject: Re: Easy regex replace?

2006/3/19, Adam i Agnieszka Gąsiorowski FNORD <[EMAIL PROTECTED]>:
>
> On 2006-03-18, at 00:59, Yani Copas wrote:
>
> >
> > Is there a quick and dirty way to update such that I can only
> > affect the portion
> > of a string (varchar column) that matches a regexp?
> > (e.g. replace all '%20' with ' ' leaving the rest untouched?)
>
> You know that proverb - "For a man in possession of a hammer,
> everything looks like a nail".
>   Don't do that. MySQL is *really slow* with Regular Expressions. It
> will be much easier to SELECT
>   all records you want to change, storing their IDs in a list (or
> array) construct, then tell your favourite
>   script program to construct an REPLACE query out of these chosen
> few, after it does whatever you want it to do
>   with the records' data.


Yeah, but sometimes beoing able to do such things on the mysql 
command line would be very helpful ! (Instead of having a script for
such simple things which would be like having a jack hammer for a
nail.. )

--
Pooly
Webzine Rock : http://www.w-fenec.org/


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

InnDB disabbled on 5.1.7

[Gordon]

We are running 2.6.15-gentoo Linux and downloaded the max binaries for
5.1.7. With the following my.cnf I thought we should have InnoDB. All of the
InnoDB files got created but show variables like 'have%'; displays "
have_innodb  DISABLED". Exactly the same my.cnf {except the skip bdb is not
commented out} has InnoDB enabled.

Any ideas on what we have to do to enable InnoDB.

my.cnf

# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/var) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
 
# The following options will be passed to all MySQL clients
[client]
#password   = your_password
port    = 3306
socket  = /tmp/mysql.sock
 
# Here follows entries for some specific programs
 
# The MySQL server
[mysqld]
port    = 3306
socket  = /tmp/mysql.sock
max_connections = 100
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
thread_cache_size = 8
query_cache_size= 2M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
 
 
 
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
 
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id   = 1
 
 
 
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
 
skip-bdb
 
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/var/
innodb_data_file_path = ibd1:2000M;ibd2:2000M;ibd3:2000;ibd4:10M:autoextend
#innodb_log_group_home_dir = /usr/local/var/
#innodb_log_arch_dir = /usr/local/var/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50
 
[mysqldump]
quick
max_allowed_packet = 16M
 
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
 

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
 
[mysqlhotcopy]
interactive-timeout

mysql> select version();
++
| version()  |
++
| 5.1.7-beta-max-log |
++
1 row in set (1.73 sec)

Linux zeus 2.6.15-gentoo-r1 #10 SMP PREEMPT Tue Mar 7 15:36:28 MST 2006 i686
Intel(R) Xeon(TM) CPU 3.80GHz GenuineIntel GNU/Linux

mysql> show variables like 'have%';
++--+
| Variable_name  | Value|
++--+
| have_archive   | YES  |
| have_bdb   | DISABLED |
| have_blackhole_engine  | YES  |
| have_compress  | YES  |
| have_crypt | YES  |
| have_csv   | YES  |
| have_example_engine| NO   |
| have_federated_engine  | YES  |
| have_geometry  | YES  |
| have_innodb| DISABLED |
| have_ndbcluster| DISABLED |
| have_openssl   | NO   |
| have_partitioning  | YES  |
| have_query_cache   | YES  |
| have_row_based_replication | YES  |
| have_rtree_keys| YES  |
| have_symlink   | YES  |
++--+
17 rows in set (0.10 sec)

mysql> show variables like 'inno%';
+-+-
+
| Variable_name   | Value
|
+-+-
+
| innodb_additional_mem_pool_size | 20971520
|
| innodb_autoextend_increment | 8
|
| innodb_buffer_pool_awe_mem_mb   | 0
|
| innodb_buffer_pool_size | 536870912
|
| innodb_checksums| ON
|
| innodb_commit_concurrency   | 0
|
| innodb_concurrency_tickets  | 500
|
| innodb_data_file_path   |
ibd1:2000M;ibd2:2000M;ibd3:2000;ibd4:10M:autoextend |
| innodb_data_home_dir|
|
| innodb_doublewrite  | ON
|
| innodb_fast_shutdown| 1
|
| innodb_file_io_threads  | 4
|
| innodb_file_per_table   | OFF
|
| innodb_flush_log_at_trx_commit  | 0
|
| innodb_flush_method |
|
| innodb_force_recovery   | 0
|
| innodb_lock_wait_timeout| 50
|
| innodb_locks_unsafe_for_binlog  | OFF
|
| innodb_log_arch_dir |
|
| innodb_log_

RE: Stored procedures and views

[Gordon]

If the features available in stored procedures {i.e. looping etc.} are
required, have you tried having the procedure 1st write/edit the data in a
temporary table and at the end select the values you want from that table. 

I think I might write 2 procedures. One that does the data manipulation and
the other that selects the result. 
something like this  

DELIMITER //
DROP PROCEDURE IF EXITS manipulate//

CREATE PROCEDURE manipulate(
CREATE TEMPORARY TABLE data_result

...
...
END// 

DROP PROCEDURE IF EXITS result//

CREATE PROCEDURE result(
...
  CALL manipulate (
...
  SELECT ... FROM data_result
END//

DELIMITER ;
> -Original Message-
> From: Martijn Tonies [mailto:[EMAIL PROTECTED]
> Sent: Friday, March 24, 2006 1:04 PM
> To: Chris Carrier; mysql@lists.mysql.com
> Subject: Re: Stored procedures and views
> 
> 
> 
> > I just mean is it possible to hook a set of stored procedures to a view
> as
> > opposed to a temporary table that we'd have to explicitly update?
> 
> "hook"?
> 
> If you mean: using the views in select statements inside a procedure:
> sure it. Did you try?
> 
> Martijn Tonies
> Database Workbench - development tool for MySQL, and more!
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
> 
> > Chris
> >
> > -Original Message-
> > From: Martijn Tonies [mailto:[EMAIL PROTECTED]
> > Sent: Friday, March 24, 2006 10:26 AM
> > To: mysql@lists.mysql.com
> > Subject: Re: Stored procedures and views
> >
> > Chriss,
> >
> > > I'm trying to create a flatfile dump from our database which requires
> some
> > > functionality that's not possible with raw sql.  Up to now we've been
> > using
> > > PHP tied to mySql to do all the work and get the data in the correct
> > format.
> > > We would like to switch this functionality over to stored procedures
> in
> > > mySql.  My question is this: Is it possible to hook a series of stored
> > > procedures to a view so that the data is live and current?
> >
> > What do you mean by that?
> >
> > A view is always current.
> >
> > Can you explain it a bit better?
> >
> > >We could set it
> > > up to run our procedures on some regular interval and dump the result
> into
> > a
> > > temporary table but having up-to-date data would be ideal.
> >
> > Martijn Tonies
> > Database Workbench - development tool for MySQL, and more!
> > Upscene Productions
> > http://www.upscene.com
> > My thoughts:
> > http://blog.upscene.com/martijn/
> > Database development questions? Check the forum!
> > http://www.databasedevelopmentforum.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.385 / Virus Database: 268.3.1/291 - Release Date: 3/24/2006
> >
> >
> > --
> > No virus found in this outgoing message.
> > Checked by AVG Free Edition.
> > Version: 7.1.385 / Virus Database: 268.3.1/291 - Release Date: 3/24/2006
> >
> >
> >
> > --
> > 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]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Reserevd Error -7776 -- Urgent

[Gordon]

I have been linking MySQL tables into access for versions 3.23 thorough
5.17. The only way I have been able to deal with insert/update tables with a
timestamp field is to build a query which includes all of the fields except
the timestamp field. You can then do your insert or update against the
query. ACCESS/ODBC does not know about the timestamp field so they are
happy. If you specify CURRENT TIMESTAMP on the timestamp field in the MySQL
definition, MySQL will populate the field every time the row is changed or a
new row added.

> -Original Message-
> From: C K [mailto:[EMAIL PROTECTED]
> Sent: Friday, April 07, 2006 1:50 PM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com; [EMAIL PROTECTED]
> Subject: Re: Reserevd Error -7776 -- Urgent
> 
> I also tried folloing and got results as below:
> 1) I updated the default for timestamp filed as CURRENT TIMESTAMP as
> mentioned in MySQL 5.1 manual.
> But this not worked.
> 2) I dropped timestamp fiield from that table and refreshed link, then
> I can ork properly. Now I can insert and update reocrds without any
> problem.  But this against the comment and responce from MySQL
> community that it is necessary to have a timestamp field to properly
> view and edit data in linked MysQL table. Else it will give he error
> as #deleted, But isn't it strange that now it is not giving me any
> such error. ? Why?
> Thanks.
> 
> --
> 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]

RE: FW: New to TRIGGER and CALL. Example gives errors. (repost)

[Gordon]

REMOVE the semicolon ";" from " END;//"

SQLyog has a problem with all of the procedures, functions and triggers RE
the DELIMITER syntax.

> -Original Message-
> From: Daevid Vincent [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, April 11, 2006 10:00 PM
> To: mysql@lists.mysql.com
> Cc: 'Shawn Green'
> Subject: RE: FW: New to TRIGGER and CALL. Example gives errors. (repost)
> 
> I was using SQLYog 5.03 RC1.
> 
> vmware ~ # mysql --version
> mysql  Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) using readline
> 5.1
> 
> But just to sanity check. I ssh'd in and tried this at the mysql command
> line utility:
> 
> vmware ~ # mysql somedatabase
> Reading table information for completion of table and column names
> You can turn off this feature to get a quicker startup with -A
> 
> Welcome to the MySQL monitor.  Commands end with ; or \g.
> Your MySQL connection id is 415 to server version: 5.0.19-log
> 
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
> 
> mysql> delimiter //
> mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON starkeys
> -> FOR EACH ROW
> -> BEGIN
> -> IF NEW.skey < 1 THEN
> ->
> Display all 187 possibilities? (y or n)
> -> EW.skey = 1;
> -> ELSEIF NEW.skey > 9 THEN
> ->
> Display all 187 possibilities? (y or n)
> -> EW.skey = 9;
> -> END IF;
> -> END;//
> delimiter ;
> ERROR 1064 (42000): 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 '.skey = 1;
> ELSEIF NEW.skey > 9 THEN
> EW.skey = 9;
> END IF;
> END' at line 5
> mysql> delimiter ;
> mysql>
> 
> 
> > -Original Message-
> > From: Shawn Green [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, April 11, 2006 12:41 PM
> > To: Daevid Vincent; mysql@lists.mysql.com
> > Subject: Re: FW: New to TRIGGER and CALL. Example gives
> > errors. (repost)
> >
> >
> >
> > --- Daevid Vincent <[EMAIL PROTECTED]> wrote:
> >
> > > This may have been lost, so I'm reposting hoping for a clue
> > as to why
> > > the
> > > mySQL example onlie gives me errors...
> > > -Original Message-
> > > Sent: Sunday, April 09, 2006 7:41 PM
> > >
> > > I'm trying to follow the example in the manual to create a trigger:
> > > http://dev.mysql.com/doc/refman/5.0/en/using-triggers.html
> > >
> > > #DROP TRIGGER upd_check;
> > > delimiter //
> > > CREATE TRIGGER upd_check BEFORE UPDATE ON starkeys
> > > FOR EACH ROW
> > > BEGIN
> > >   IF NEW.skey < 1 THEN
> > >   SET NEW.skey = 1;
> > >   ELSEIF NEW.skey > 9 THEN
> > >   SET NEW.skey = 9;
> > >   END IF;
> > > END;//
> > > delimiter ;
> > >
> > > All I'm trying to do is enforce that my starkeys.skey column is
> > > always in
> > > the range of 1 through 9. I was planning to start with this example
> > > and work
> > > my way up. Ideally it should check on UPDATE or INSERT. The manual
> > > recommended:
> > >
> > > "It can be easier to define a stored procedure separately and then
> > > invoke it
> > > from the trigger using a simple CALL statement. This is also
> > > advantageous if
> > > you want to invoke the same routine from within several triggers."
> > >
> > > But I don't know how to do that yet.
> > >
> > > vmware public_html # mysql --version
> > > mysql  Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) using
> > > readline 5.1
> > >
> > > But I just get these errors:
> > >
> > > Error Code : 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
> > 'delimiter
> > > //
> > > CREATE TRIGGER upd_check BEFORE UPDATE ON starkeys
> > > FOR EACH ROW
> > > BEG' at line 2
> > > (0 ms taken)
> > >
> > > Error Code : 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 'ELSEIF
> > > NEW.skey
> > > > 9 THEN
> > >   SET NEW.skey = 9' at line 1
> > > (0 ms taken)
> > >
> > > Error Code : 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 'END IF'
> > > at line
> > > 1
> > > (0 ms taken)
> > >
> > > Error Code : 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 'END' at
> > > line 1
> > > (0 ms taken)
> > >
> > > Error Code : 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 '//
> > > delimiter' at line 1
> > > (0 ms taken)
> > >
> >
> > This looks suspiciously like an interface issue, not a coding issue.
> > How are you delivering these commands to your MySQL server and is it
> > v5.0 or newer?
> >
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> 
> 
> --
> MySQL General Mailing List
> For list archives

RE: ORDER BY question

[Gordon]

I think you can also do 
SELECT *, 
   DATE_FORMAT(deadline, '%d-%m-%Y') AS deadline_f,
   Status + 0 AS sorted_grade
FROM v_issue_project_task
ORDER BY sorted_grade

That way you do not have to change the code if you add a value to the
enum list via ALTER TABLE.
-Original Message-
From: Mike van Hoof [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, March 21, 2007 3:19 AM
To: Christophe Gregoir
Cc: mysql
Subject: Re: ORDER BY question

Thanks, that is also a solution.

Friend of mine pointed me to the following:

SELECT *, DATE_FORMAT(deadline, '%d-%m-%Y') as deadline_f,
CASE `status`
WHEN 'not yet started' then 1
WHEN 'in progress' then 4
WHEN 'finished' then 5
WHEN 'now hiring' then 3
WHEN 'waiting' then 2
WHEN 'closed' then 6
END AS sorted_grade
FROM v_issue_project_task
ORDER BY sorted_grade

- Mike

Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024  
fax: 040-29 63 567
url: www.medusa.nl
mail: [EMAIL PROTECTED]
 
Uw bedrijf voor Multimedia op Maat



Christophe Gregoir schreef:
> Hey Mike,
>
> Sounds like you would be better of with an ENUM of integers, e.g. 
> ENUM(-1,1,2,3) where -1 stands for to be started, 1 for started and so

> on.
> To answer your question:
> ORDER BY `status` = 'to be started', `status` = 'started', `status` = 
> 'finished', `status` = 'canceled'
>
> Mike van Hoof wrote:
>> Hello everybody,
>>
>> I got a small problem with ordering on en ENUM field. The values in 
>> this field are:
>> - to be started
>> - started
>> - finished
>> - canceled
>>
>> And i want to order on this field, but in the direction the are above

>> here (and not alpabetically).
>> Is that possible?
>>
>> - 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.5.446 / Virus Database: 268.18.15/728 - Release Date:
3/20/2007 8:07 AM
 

-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.15/728 - Release Date:
3/20/2007 8:07 AM
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Giving Back...Well, Maybe

[Gordon]

Just one suggestion re the behavior of special characters between file
input and command line input. Try using char(10) {I think that is new
line} instead of \n. That should work in both scenerios.

-Original Message-
From: John Kebbel [mailto:[EMAIL PROTECTED] 
Sent: Saturday, April 28, 2007 7:19 AM
To: mysql@lists.mysql.com
Subject: Giving Back...Well, Maybe

I don't have enough MySQL knowledge to contribute much to this
mailing
list, but as a 23 year veteran teacher, I have some expertise when it
comes to developing methods of instruction (especially
self-instruction). While studying for the MySQl CMDEV exam, I created a
method for MySQL skill-building that may help others trying to master
MySQL systematically.

I had been building my expertise with queries by redirecting
commands
from a text file into MySQL. When I discovered MySQL comments through
this mailing list, I realized I could store my learning by putting an
active command I was testing at the top line of my text document, and my
previously tested commands below them inside a commented out area. This
morning I went a step further when I realized I could embed XHTML coding
inside MySQL comments and have a dual-purpose page, a page that (1) lets
me test queries by redirecting them to MySQL on my local computer, and
then (2) stores the tested queries inside commented-out areas in a
format I can post on the web.

The web page format allows me to review what I've done and lets
me
continue my research at home or work by downloading, testing and
expanding, then uploading my new research.

I hope someone finds the concept useful. What I've accomplished
so far
is at

 http://scripting-solutions.com/certifications/mysql/dothis.html

Thanks to everyone who has been of help to me here now and in the
future.  


-- 
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]

RE: Order By and Ignore Punctuation

[Gordon]

Try something like this. If there are multiple punctuation values you
want to ignore you can nest multiple REPLACE functions.

mysql> create table names (name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into  names values
('Osbourn'),("O'shea"),("O'Malley"),('Olathe'),('Ottawa');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT name 
 > FROM   names 
 > ORDER BY REPLACE(name,"'",'');
+--+
| name |
+--+
| Olathe   |
| O'Malley |
| Osbourn  |
| O'shea   |
| Ottawa   |
+--+
5 rows in set (0.00 sec)

-Original Message-
From: Andreas Iwanowski [mailto:[EMAIL PROTECTED] 
Sent: Thursday, May 03, 2007 7:48 PM
To: Bill Guion
Cc: mysql@lists.mysql.com
Subject: RE: Order By and Ignore Punctuation

I would suggest you order by something that includes a fulltext index on
the specific column.
Maybe check out the documentation on the MATCH()AGAINST() systax as well
as fulltext searches in general.

For example:
SELECT Col1, Col2, Score AS MATCH(TextCol) AGAINST ("") WHERE ... ORDER
BY Score;

Hope to help,
   -Andy

-Original Message-
From: Bill Guion [mailto:[EMAIL PROTECTED] 
Sent: Thursday, May 03, 2007 8:33 PM
To: mysql@lists.mysql.com
Subject: Order By and Ignore Punctuation

I would like to perform a query of a personnel database with an ORDER BY
clause that ignores punctuation. For example, O'shea would sort after
Osbourne, not to the beginning of the Os.

Is this doable in the query?

  -= Bill =-
-- 

You can tell a lot about a man by the way he handles these three
things: a rainy day, lost luggage, and tangled Christmas tree lights.



--
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]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

used command isn not allowed

[Gordon]

I tried running this load data command on Server version: 5.0.27 and get the
1148 error. I'm not sure if it is referring tho the comand client or the
server.

I also do not understand why the command {or which part} it is complaining
about. Probably something obvious, but I just can't see it.

 

Any suggestion will bew greatly appreciated.

 

mysql> load data local infile "c:\\losalamos.asc" into table losalamos_new

->fields terminated by ',' optionally enclosed by '"' lines
terminated by '\r\n' ignore 1 lines

->(`FIRSTOWNER`, `OWNER2`, `NFUL_ADDR`, `NFUL_ADDR2`, `CITY`,
`STATE`, `ZIPCODE`, `FIRST_NAME`,

-> `LOT`, `BLCK`, `TRACT`, `SUBD`, `SECTION`, `TOWNSHIP`,
`RANGE`, `DESCRIPT`, `LAST_NAME`, `TAXAMT`,

-> `MAPCODE`, `NUMBER`, `STREET`, `YBLT`, `MAIN`, `SECOND`,
`THIRD`, `DOWNSTAIRS`, `TOTAL`, `DEAR`,

-> `TRUST`, `CTYSTZIP`, `ACCT`, `ACRE`, `DEEDBOOK`, `DEEDPAGE`,
`TAXDUE`, `TAXPAID`, `PUR_DATE`,

-> `PROP_ZIP`, `COMPANY`, `SALUTATION`, `CONTACT`, `BEDROOMS`,
`BATHS`, `TAXYEAR`, `UNITS`, `SALEDATE`,

-> `ACCTTYPE`, `LANDASD`, `IMPASD`, `cntyname`, `SALEMONTH`,
`SALEYR`);

ERROR 1148 (42000): The used command is not allowed with this MySQL version

RE: select where multiple joined records match

[Gordon]

Try this 

Select * 
from   resources, goals 
where  resources.ID = goals.RESOURCE_ID
   and (SUBJECT="English"
and GRADE="1") 
OR
   (SUBJECT="English"
and GRADE="2");

-Original Message-
From: AM Thomas [mailto:[EMAIL PROTECTED] 
Sent: Sunday, February 13, 2005 7:23 AM
To: mysql@lists.mysql.com
Subject: select where multiple joined records match

I'm trying to figure out how to select all the records in one table
which have multiple specified records in a second table.  My MySQL is  
version 4.0.23a, if that makes a difference.

Here's a simplified version of my problem.

I have two tables, resources and goals.

resources table:

ID  TITLE
1   civil war women
2   bunnies on the plain
3   North Carolina and WWII
4   geodesic domes


goals table:

ID RESOURCE_ID  GRADE  SUBJECT
1  11  English
2  11  Soc
3  12  English
4  21  English
5  23  Soc
6  32  English
7  41  English

Now, how do I select all the resources which have 1st and 2nd grade
English goals?  If I just do:

Select * from resources, goals where ((resources.ID =
goals.RESOURCE_ID) and (SUBJECT="English") and ((GRADE="1") and
(GRADE="2")));

I'll get no results, since no record of the joined set will have more
than one grade.  I can't just put 'or' between the Grade
conditions; that would give resources 1, 2, 3, and 4, when only 1
really should match.

My real problem is slightly more complex, as the 'goals' table also
contains an additional field which might be searched on.

I'm thinking it's time for me to go into the deep end of SQL (MySQL,
actually), and my old O'Reilly MySQL & mSQL book isn't doing the
trick.

Surely this has come up before - thanks for any guidance.

- AM Thomas
-- 
Virtue of the Small / (919) 929-8687

-- 
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]

RE: Query problem

[Gordon]

Did you want 
WHERE  Name LIKE 'sandy' 
   OR (main_data.Display_In_Search = 1 
   AND main_data.Expiry_Date >= CurDate())


OR 


WHERE  main_data.Expiry_Date >= CurDate()
   AND (Name LIKE 'sandy' 
OR main_data.Display_In_Search = 1 )
   

-Original Message-
From: Richard Duke [mailto:[EMAIL PROTECTED] 
Sent: Thursday, February 17, 2005 4:03 PM
To: mysql@lists.mysql.com
Subject: Query problem

Hi

I have a problem with a query that has many joined tables. The query brings 
back 80 records instead of just one. Any suggestions on how I can overcome 
this?

Many thanks

Richard

Query below:-

SELECT *
FROM (main_data INNER JOIN main_data_facilities ON
main_data_facilities.RecNo = main_data.RecNo) INNER JOIN main_data_meals ON
main_data_meals.RecNo = main_data.RecNo) INNER JOIN main_data_non_smoking ON
main_data_non_smoking.RecNo = main_data.RecNo) INNER JOIN 
main_data_payment_types ON
main_data_payment_types.RecNo = main_data.RecNo) INNER JOIN 
main_data_pets_welcome ON
main_data_pets_welcome.RecNo = main_data.RecNo) INNER JOIN 
main_data_special_dietary_requirements ON
main_data_special_dietary_requirements.RecNo = main_data.RecNo) INNER JOIN 
counties ON
counties.ID = main_data.County) INNER JOIN countries ON
countries.ID = main_data.Country) INNER JOIN facilities ON
facilities.ID = main_data_facilities.ID) INNER JOIN meals ON
meals.ID = main_data_meals.ID) INNER JOIN non_smoking ON
non_smoking.ID = main_data_non_smoking.ID) INNER JOIN payment_types ON
payment_types.ID = main_data_payment_types.ID) INNER JOIN pets_welcome ON
pets_welcome.ID = main_data_pets_welcome.ID) INNER JOIN 
special_dietary_requirements ON
special_dietary_requirements.ID = main_data_special_dietary_requirements.ID 
AND main_data_meals.RecNo = main_data_facilities.RecNo
WHERE Name LIKE 'sandy'
OR ( main_data.Display_In_Search = 1 )
AND ( main_data.Expiry_Date >= CurDate() )


-- 
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]

RE: how to write this query?

[Gordon]

This works if you don't care about holidays. 
If you do the only solution that I have seen that works is to create a
business day table. Ours is shown below. You have to hand construct the
calendar by removing weekends and holidays for the specific entity. This
calendar forces a non business day DATE to the next business day. Date
arithmetic then becomes simple including FIRST MONDAY OF THE MONTH in the US
where many holidays fall on Monday.

clnd_Day is the relative business day since 2000-01-01 
clnd_Week is the relative business week since 2000-01-01 


mysql> show create table calendar;
+--+-
-
| Table| Create Table

+--+-
-
| calendar | CREATE TABLE `calendar` (
  `clnd_Day` smallint(5) unsigned NOT NULL default '0',
  `clnd_Date` date NOT NULL default '-00-00',
  `clnd_Week_Day_Txt` char(9) default NULL,
  `clnd_Week_Day_Num` tinyint(3) unsigned default NULL,
  `clnd_Char_Date` char(12) default NULL,
  `clnd_Week` smallint(8) unsigned default NULL,
  `clnd_Real_Date` char(10) default NULL,
  PRIMARY KEY  (`clnd_Date`),
  UNIQUE KEY `clnd_Real_Date_IDX` (`clnd_Real_Date`),
  KEY `clnd_Day_IDX` (`clnd_Day`),
  KEY `clnd_Char_Date` (`clnd_Char_Date`)
) TYPE=InnoDB |

mysql> select * from calendar limit 10;
+--++---+---+---
-+---++
| clnd_Day | clnd_Date  | clnd_Week_Day_Txt | clnd_Week_Day_Num |
clnd_Char_Date | clnd_Week | clnd_Real_Date |
+--++---+---+---
-+---++
|1 | 2000-01-01 | Monday| 2 | 01/03/2000
| 1 | 01/01/2000 |
|1 | 2000-01-02 | Monday| 2 | 01/03/2000
| 1 | 01/02/2000 |
|1 | 2000-01-03 | Monday| 2 | 01/03/2000
| 1 | 01/03/2000 |
|2 | 2000-01-04 | Tuesday   | 3 | 01/04/2000
| 1 | 01/04/2000 |
|3 | 2000-01-05 | Wednesday | 4 | 01/05/2000
| 1 | 01/05/2000 |
|4 | 2000-01-06 | Thursday  | 5 | 01/06/2000
| 1 | 01/06/2000 |
|5 | 2000-01-07 | Friday| 6 | 01/07/2000
| 1 | 01/07/2000 |
|6 | 2000-01-08 | Monday| 2 | 01/10/2000
| 2 | 01/08/2000 |
|6 | 2000-01-09 | Monday| 2 | 01/10/2000
| 2 | 01/09/2000 |
|6 | 2000-01-10 | Monday| 2 | 01/10/2000
| 2 | 01/10/2000 |
+--++---+---+---
-+---++
10 rows in set (0.00 sec)

-Original Message-
From: Mike Rains [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 21, 2005 9:33 AM
To: mysql@lists.mysql.com
Subject: Re: how to write this query?

SELECT
   start_date,
   end_date,
   DATEDIFF(end_date, start_date) -
   (WEEK(end_date) - WEEK(start_date)) * 2
   AS business_days
FROM DateDiffs
ORDER BY start_date;

+-+-+---+
| start_date  | end_date| business_days |
+-+-+---+
| 2005-01-04 16:44:57 | 2005-01-10 17:53:33 | 4 |
| 2005-01-17 00:00:00 | 2005-02-18 00:00:00 |24 |
| 2005-01-21 00:00:00 | 2005-02-18 00:00:00 |20 |
| 2005-01-24 00:00:00 | 2005-02-18 00:00:00 |19 |
| 2005-01-28 00:00:00 | 2005-02-18 00:00:00 |15 |
| 2005-01-31 00:00:00 | 2005-02-18 00:00:00 |14 |
| 2005-02-04 00:00:00 | 2005-02-18 00:00:00 |10 |
| 2005-02-07 00:00:00 | 2005-02-18 00:00:00 | 9 |
| 2005-02-14 00:00:00 | 2005-02-18 00:00:00 | 4 |
+-+-+---+

-- 
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]

RE: Odd rounding errors with 4.1

[Gordon]

This reminded me of one more difference between Windows and Linux/Unix.
MySQL use the round function out of the host libraries. If you are on a
Windows box the rule for rounding is if the column immediately to the right
of the column you are rounding to is a 5 then round up 
   i.e. make 2.485 >>> 2.49
make 2.595 >>> 2.60
If you are on a Linux/Unix box the rule for rounding is if the column
immediately to the right of the column you are rounding to is a 5 then round
up if the column you are rounding to is odd and round down if the column you
are rounding to is even
   i.e. make 2.485 >>> 2.49
make 2.595 >>> 2.59

Windows Linux/Unix
2.4850  2.492.48
2.5950  2.602.60
2.7700  2.772.77
7.8500  7.867.85

This was run on a RedHat server

mysql> select round(2.4850,2), round(2.5950,2), round(2.7700,2),
round(2.4850,2)+round(2.5950,2)+round(2.7700,2);
+-+-+-+-
+
| round(2.4850,2) | round(2.5950,2) | round(2.7700,2) |
round(2.4850,2)+round(2.5950,2)+round(2.7700,2) |
+-+-+-+-
+
|2.48 |2.60 |2.77 |
7.85 |
+-+-+-+-
+
1 row in set (0.00 sec)

-Original Message-
From: Martin [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 21, 2005 7:49 PM
To: Hassan Schroeder; mysql@lists.mysql.com
Subject: Re: Odd rounding errors with 4.1

Huh, you know.  Now that I'm not at work and therefore don't have my 
numbers to check against, you're right.

Man, I must need more coffee.

Never mind me. :)

May be back tomorrow, though, when I have the numbers in front of me.  I 
know they didn't add up earlier...

Martin

Hassan Schroeder wrote:
> Martin wrote:
> 
>> My recent test involved the following three values from the column:
>> 2.4950
>> 2.5950
>> 2.7700
> 
> 
>> When I use a SUM() on these I get: 7.860
> 
> 
> Sounds good to me...
> 
>> If I switch the column over to a FLOAT, then the SUM() becomes 
>> 7.858950958
>>
>> Using Excel to test the numbers, or hand-calculating, I get:
>> 7.8550.
> 
> 
> Time for a hand upgrade, I think :-)  5 + 5 = 5???  I don't even
> want to think about how Excel would come up with this...
> 
>> Shouldn't the SUM() remain with the precision of the DECIMAL type and 
>> not try to round to 2 decimal places?
> 
> 
> My own, possibly suspect, hand calculations show that SUM() is right;
> and it's common knowledge that floating point isn't the right thing to 
> use for situations like this -- that's why there *is* a DECIMAL type.
> 
> FWIW!

-- 
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]

RE: select date_format('2004-10-03 15:06:14','%m/%d/%y %T');

[Gordon]

I have > 200 tables with regular datetime fields. I link these tables
through ODBC to an ACCESS database where I can run standard ACCESS append
queries or even copy/paste append into the linked table. ODBC handles the
conversion just fine. 

One caution, MySQL timestamp maps to ACCESS datetime but ACCESS datetime
does not map to MySQL timestamp. 

We are on ODBC 3.51.nn and MySQL 4.0.20, but we have been doing this across
several versions for 3 years.

-Original Message-
From: Scott Hamm [mailto:[EMAIL PROTECTED] 
Sent: Friday, March 04, 2005 9:08 AM
To: Mysql
Subject: select date_format('2004-10-03 15:06:14','%m/%d/%y %T');

How do I create a table using:

date_format('2004-10-03 15:06:14','%m/%d/%y %T');

That way I can import Access Data?
-- 
Power to people, Linux is here.

-- 
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]

RE: Use MySQL with Microsoft Office

[Gordon]

If Publisher XP's mail merge can find data in ACCESS try linking the tables
into an ACCESS database and then tying Publisher to the ACCESS database.

Sounds klunky but it might work.

-Original Message-
From: GH [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, March 08, 2005 9:37 AM
To: J.R. Bullington
Cc: mysql@lists.mysql.com
Subject: Re: Use MySQL with Microsoft Office

Thank you all for your responses. 

The reason that I am currently asking about Publisher is that i do
certificates for programs that my organization runs and I am able to
in publisher mail merge the Proper Names of both the recipients and
signatories onto the certificate plus print the wording, graphics and
other content all at once.

with out having to do 2-3 passes through the printer ... a great time
saver when doing 200 certificates

I have the myODBC installed but can not get Publisher XP's mail merge
to get the data.



On Tue, 08 Mar 2005 10:11:03 -0500, J.R. Bullington
<[EMAIL PROTECTED]> wrote:
> I know that this is a little off topic, but if you want a "real" Desktop
Publishing Suite, try Adobe
> (InDesign, specifically). They cannot do MailMerges like in OpenOffice or
M$, but it's much easier
> to use than M$ and looks a lot more professional.
> 
> As a side note, OpenOffice, as of 1.1.2, does not have a Publisher type
equivalent. It also does not
> have an Access equivalent, hence MySQL interoperability.
> 
> J.R.
> 
> -Original Message-
> From: Jigal van Hemert [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, March 08, 2005 9:59 AM
> To: GH
> Cc: mysql@lists.mysql.com
> Subject: Re: Use MySQL with Microsoft Office
> 
> From: "GH"
> > Does Open Office have a MSPublisher like program?
> 
> I'm not familiar with Publisher, but I gues that it is supposed to help
you make publications in a
> kind of desktop pulishing way.
> In the article at http://www.newsforge.com/article.pl?sid=04/10/04/150207
> the author compares Writer (the wordprocessing part of OpenOffice.org)
with Adobe's Framemaker.
> Maybe that will help you a bit.
> 
> It's worth mentioning that the current version OpenOffice.org 1.1.4 will
soon be replaced by a major
> upgrade as the 2.0 beta has recently become available. On
http://www.openoffice.org you can read all
> about the new features in this release.
> 
> I use both OpenOffice.org and MS Office and both have their pros and cons.
> 
> Regards, Jigal.
> 
> --
> 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]
> 
>

-- 
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]

RE: Slow queries only the first time

[Gordon]

The key to your question is InnoDB. InnoDB keeps data and indexes in its
buffer using LRU to flush. So the 2nd time your data was already in memory.
Depending on your buffer size and database size you have all of the
advantages of a memory resident database for frequently used data 
without the disadvantages of losing the data on system shutdown.

-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 10, 2005 1:43 PM
To: Bob O'Neill
Cc: mysql@lists.mysql.com
Subject: Re: Slow queries only the first time


On Mar 10, 2005, at 11:20 AM, Bob O'Neill wrote:

> Hello.  I am wondering why some of my queries are slow on the first  
> run, but
> speedy on subsequent runs.  They are not being query cached, as I have
> query_cache_type set to DEMAND.  Is it something as simple as pulling  
> the
> data into RAM from disk, or is there something else going on?  Here's a
> simple example:
>
> mysql> select count(*) from foo;
> +--+
> | count(*) |
> +--+
> |  1374817 |
> +--+
> 1 row in set (3.60 sec)

A table scan.

> mysql> select count(*) from foo;
> +--+
> | count(*) |
> +--+
> |  1374817 |
> +--+
> 1 row in set (0.92 sec)

Another table scan, but now some or all of the table is cached in RAM  
(disk cache) by the OS (not by MySQL).

> mysql> show variables like 'query_cache_type';
> +--++
> | Variable_name| Value  |
> +--++
> | query_cache_type | DEMAND |
> +--++
> 1 row in set (0.00 sec)

You could probably verify that the query cache wasn't used by  
monitoring the query cache stats  
.

> I am running MySQL 4.1.10 with InnoDB on RHEL 2.1 (kernel
> 2.4.9-e.49enterprise). Binaries are Linux x86 glibc static gcc RPMs  
> from
> mysql.com.
>
> Thanks,
> -Bob

Michael


-- 
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]

RE: Trouble performing an update

[Gordon]

You can do left joins in an update. 
mysql> show create table t;
+---+---
--
| Table | Create Table
+---+---
--
| t | CREATE TABLE `t` (
  `key1` int(11) NOT NULL default '0',
  `key2` int(11) NOT NULL default '0',
  PRIMARY KEY  (`key1`,`key2`)
) TYPE=MyISAM |
+---+---
--
1 row in set (0.01 sec)

mysql> insert into t values
(5,5),(20,25),(10,15),(10,20),(10,30),(15,20),(20,30);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from t;
+--+--+
| key1 | key2 |
+--+--+
|5 |5 |
|   10 |   15 |
|   10 |   20 |
|   10 |   30 |
|   15 |   20 |
|   20 |   25 |
|   20 |   30 |
+--+--+
7 rows in set (0.00 sec)

mysql> update t as t1
->left join t as t2
->on (t1.key2 = t2.key2
->and t2.key1 = 20)
-> sett1.key1 = 20
-> where  t2.key1 IS NULL
->AND t1.key1 = 10;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from t;
+--+--+
| key1 | key2 |
+--+--+
|5 |5 |
|   10 |   30 |
|   15 |   20 |
|   20 |   15 |
|   20 |   20 |
|   20 |   25 |
|   20 |   30 |
+--+--+
-Original Message-
From: Bob Dankert [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, March 16, 2005 2:27 PM
To: mysql@lists.mysql.com
Subject: Trouble performing an update

I am trying to update a link table (table with two primary keys) where I
want to update all rows where the first primary key is a set value (for
example, change key1 from 10 to 20), but I only want to update these
where the resulting primary key does not already exist in the table
(otherwise an error is thrown on a duplicate key and the remaining rows
are not updated).  Using other databases, I am able to perform a
subquery in the filter for the update such as the following:

 

UPDATE mytable SET key1 = 20 WHERE key2 NOT IN (SELECT key2 FROM mytable
WHERE key1 = 20)

 

Unfortunately, MySQL does not allow you to use a table in a subquery
which is being updated.  If anyone can offer any assistance with this, I
would greatly appreciate it.

 

Thanks,

 

Bob Dankert

 

Envision Information Technologies

Associate

[EMAIL PROTECTED]

v. 608.256.5680

f. 608.256.3780

 

 




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: design: table depending on a column

[Gordon]

As long as articles.annotationID can be made distinct from
names.annotationID why not use 2 left joins.

You may have to test annotationType in the select section to map the fields.

Something like the following.


SELECT elements.annotationID, 
   CASE annotationType 
 WHEN 'names' THEN names.name 
 WHEN 'articles' THEN articles.title 
 ELSE '' 
   END AS FIELD1,
   CASE annotationType 
 WHEN 'names' THEN '' 
 WHEN 'articles' THEN articles.author 
 ELSE '' 
   END AS FIELD2
FROM elements 
 LEFT JOIN articles 
 USING (annotationID) 
 LEFT JOIN names 
 USING (annotationID)


-Original Message-
From: mel list_php [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, April 12, 2005 8:59 AM
To: mysql@lists.mysql.com
Subject: design: table depending on a column




Hi list,

I have a design problem, I'd like to know if there is a nice way to solve 
it

I have elements that can be annotated, an annotation is basic info and a 
link on an other database.
For example: my element id 3, called testElement, is annotated.
the annotation depends on the foreign database, sometimes it's articles so 
i'd like to have id, title, author,abstract, sometimes it's just a name so 
in that case I would have id and name.In both id is the id  required to find

the information in the "foreign" db.

The goal is to search for a string in these annotations and retrieve the 
element id.
At the beginning we will know in which foreign database we want to search 
(articles or name) but these could be extended later on.

So my ideas:

-the trivial approach having everything in one table is not realistic 
because I have other attributes (elementName,elementOrigin) for each 
elementID that I don't want to repeat.

- having a table with elementID,annotationID and an other table with 
annotationID, title, author,name
what I don't like here is having only one table for all the annotations in 
all the databases, if I know in which db to search merging everythin will 
slow down a string search

-having a table with elementID,annotationID,annotationType, and depending on

the annotationType searching in the right table: table articles 
(annotationID,title, author) or table names (annotationID,name).
what I don't like in that case is that I have to retrieve the value of the 
attribute annotationType and then do the search depending on that value. (is

there a way to join with a table which name would be retrieved?something 
like select * from elements left join (select annotationType from elements) 
on annotationID?)


I think the second solution is much slower, but it seems more clear for me.
The right way (one of the irght way!) is probably intermediate between both,

but I can't see it.
I have to be careful about the design because the searches will be a lot of 
text, so I'd like to optimize it.

Thanks for any help,

Melanie

_
It's fast, it's easy and it's free. Get MSN Messenger today! 
http://www.msn.co.uk/messenger


-- 
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]

RE: Help with a tricky/impossible query...

[Gordon]

One way would be to build a "count" table with one column starting with
value 1 and incrementing by 1 up to say 500 rows or how many your max y
value is. Then just 
   select seq,val from wibble,count where val between x and y

create table count (val INT unsigned default '0' not null primary key)

-Original Message-
From: Andrew Braithwaite [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, April 13, 2005 7:33 PM
To: MySQL
Subject: Re: Help with a tricky/impossible query...

I should mention that I'm constrained to version 4.0.n so no sub queries for
me!

Andrew


On 14/4/05 1:11 am, "Andrew Braithwaite" <[EMAIL PROTECTED]> wrote:

> Hi,
> 
> I need some help with a tricky query.  Before anyone asks, I cannot bring
> this functionality back to the application layer (as much as I'd like to).
> 
> Here's what I need to do...
> 
> create table wibble(
> seq int(3) auto_increment primary key,
> x int(5),
> y int(5)
> );
> 
> insert into wibble set x=5, y=10;
> insert into wibble set x=1, y=3;
> insert into wibble set x=17, y=22;
> 
> mysql> select * from wibble;
> +-+--+--+
> | seq | x| y|
> +-+--+--+
> |   1 |5 |   10 |
> |   2 |1 |3 |
> |   3 |   17 |   22 |
> +-+--+--+
> 3 rows in set (0.09 sec)
> 
> So I want to run a query to explode the x/y ranges by seq.
> 
> The required output is:
> 
> mysql> select some clever things from wibble where some clever stuff
happens
> here;
> +-+--+
> | seq | z|
> +-+--+
> |   1 |1 |
> |   1 |2 |
> |   1 |3 |
> |   1 |4 |
> |   1 |5 |
> |   2 |1 |
> |   2 |2 |
> |   2 |3 |
> |   3 |   17 |
> |   3 |   18 |
> |   3 |   19 |
> |   3 |   20 |
> |   3 |   21 |
> |   3 |   22 |
> +-+--+
> 14 rows in set (0.17 sec)
> 
> Can anyone help me to achieve this result?
> 
> Thanks,
> 
> Andrew
> 
> SQL, Query
> 
> 
> 



-- 
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]

ACCESS ODBC Interface whit 5.0.4

[Gordon]

I have been using ACCESS to do simpe data editing on our MySQL tables for 3
years.

 

I recently installed 5.0.4 on my machine to evaluate it. I linked the tables
into ACCESS through my old ODBC driver and  got 

 

ODBC-update on a linked table 'product_order_choice' failed

[Microsoft][ODBC Driver Manager] SQL data type out of rance (#0)

 

I then downloaded and installed the current ODBC connector [3.51.11]
thinking maybe it was my old ODBC copy, but get the same result.

 

Has anyone else seen this or have any ideas?

RE: ACCESS ODBC Interface whit 5.0.4

[Gordon]

Here is the table structure.
In this case I tried to change the 4 characters in prod_ID when I get the
message, but I get the same message when I try to make any changes.

Doing the exact same activity works fine on all of the 3.23/4.0/4.1 versions
I have installed previouly. Tables with unsigned integer fields work just
fine through this interface on previous versions. The only anomaly up to
this point is that I can't change timestamp fields through the odbc
connection. They display as dates in access and I can change other fields in
the table correctly, just can't change the timestamp fields.

mysql> show create table product_order_choice;
---+
| Table| Create Table
 
|
---+
| product_order_choice | CREATE TABLE `product_order_choice` (
  `cpny_ID` varchar(4) NOT NULL default '',
  `prod_ID` varchar(4) NOT NULL default '',
  `porc_Look_Up_Type` varchar(25) NOT NULL default '',
  `prft_Sub_Month` char(2) NOT NULL default '00',
  `prft_Sub_Item` char(2) NOT NULL default '0',
  `poch_Name` varchar(100) NOT NULL default '',
  `prct_ID` varchar(4) default NULL,
  `poch_Value` text,
  `poch_Image_Path` varchar(255) default NULL,
  `poch_Link` varchar(255) default NULL,
  `poch_Link_2` varchar(255) default NULL,
  `poch_Active` enum('Yes','No','Hidden') NOT NULL default 'Yes',
  `poch_Timestamp` timestamp(14) NOT NULL,
  `poch_Create` datetime NOT NULL default '-00-00 00:00:00',
  PRIMARY KEY
(`cpny_ID`,`prod_ID`,`porc_Look_Up_Type`,`prft_Sub_Month`,`prft_Sub_Item`,`p
och_Name`)
) TYPE=InnoDB |
---+
1 row in set (0.00 sec)

mysql>

-Original Message-
From: Daniel Kasak [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 04, 2005 5:16 PM
To: Gordon; 'MySQL'
Subject: Re: ACCESS ODBC Interface whit 5.0.4

Gordon wrote:

>I have been using ACCESS to do simpe data editing on our MySQL tables for 3
>years.
>
> 
>
>I recently installed 5.0.4 on my machine to evaluate it. I linked the
tables
>into ACCESS through my old ODBC driver and  got 
>
> 
>
>ODBC-update on a linked table 'product_order_choice' failed
>
>[Microsoft][ODBC Driver Manager] SQL data type out of rance (#0)
>
> 
>
>I then downloaded and installed the current ODBC connector [3.51.11]
>thinking maybe it was my old ODBC copy, but get the same result.
>
> 
>
>Has anyone else seen this or have any ideas?
>
>
>  
>
It would help if you posted details of the table / data you're working with.
'Data type out of range' usually means you've tried to put a numerical
value in a field which is too small. For example, you may be trying to
put an int value in a mediumint field. Or it could be that you're using
a field type not supported by MS Access, such as an unsigned int or a
bigint.

-- 
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: ACCESS ODBC Interface whit 5.0.4

[Gordon]

Thanks, that looks like my problem. I 'll wait until the fix percolates into
the 5. stream.

-Original Message-
From: Gleb Paharenko [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 04, 2005 10:00 AM
To: mysql@lists.mysql.com
Subject: Re: ACCESS ODBC Interface whit 5.0.4

Hello.

Have you been here?

  http://bugs.mysql.com/bug.php?id=9211



>I have been using ACCESS to do simpe data editing on our MySQL tables
>for 3
>years.
> I recently installed 5.0.4 on my machine to evaluate it. I linked the
> tables
> into ACCESS through my old ODBC driver and  got 
>  ODBC-update on a linked table 'product_order_choice' failed
>  [Microsoft][ODBC Driver Manager] SQL data type out of rance (#0)
>   I then downloaded and installed the current ODBC connector
>   [3.51.11]
>   thinking maybe it was my old ODBC copy, but get the same result.
>    Has anyone else seen this or have any ideas?
>
"Gordon" <[EMAIL PROTECTED]> wrote:


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
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]

RE: Read past Equivalent in MySQL

[Gordon]

If you can add a table structure why not create a SELECTED table with REPORT
ID and PERSON ID as the 2 field PRIMARY KEY.

Then you could INSERT IGNORE into this table [with no BEGN/COMMIT] and the
IGNORE would throw away those already selected.

-Original Message-
From: Duncan Hill [mailto:[EMAIL PROTECTED] 
Sent: Friday, May 13, 2005 10:25 AM
To: mysql@lists.mysql.com
Subject: Re: Read past Equivalent in MySQL

On Friday 13 May 2005 16:19, Eric Bergen typed:
> I agree. It sounds like you could use plain repeatable read isolation
> transactions.  If someone else is modifying those rows you get an older
> version from when your transaction was started. No need for skipping
> anything.

In the case of what I'm programming, I need to be able to skip records that 
have been selected by another instance of the program (don't want to send
the 
same person 40 reports with the same content).  Hence why I use flags on the

table to say 'in progress, don't read me'.

-- 
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]

RE: varchar(10) to decimal

[Gordon]

I was all set to tell you why 16.125 became 16.12 when I ran the test on our
production box. It looks like some where between 4.0.20 and 5.0.4 the ALTER
TABLE to a decimal data type changed from truncation to rounding.

Redhat MySQL 4.0.20 truncates all 
Windows XP MySQL 5.0.4 Rounds with Windows algorithm 
I suspect a MySQL 5.0.4 on Linux would round with Linux alorithm but I don't
have one to test.

See following:

The decimal(6,2) tells MySQL to "round" all values to 2 places and store the
results in a decimal field. MySQL uses the Round routines of the host. 
On a Windows box 
16.125 = 16.13
16.135 = 16.14

On a Linux/Unix box
16.125 = 16.12
16.135 = 16.14

Here it is on windows

mysql> select * from dcml;
++
| a  |
++
| 16.00  |
| 16.25  |
| 16.125 |
| 16.135 |
++
4 rows in set (0.00 sec)

mysql> alter table dcml modify a decimal(6,2);
Query OK, 4 rows affected, 2 warnings (0.28 sec)
Records: 4  Duplicates: 0  Warnings: 2

mysql> show warnings;
+---+--++
| Level | Code | Message|
+---+--++
| Note  | 1265 | Data truncated for column 'a' at row 3 |
| Note  | 1265 | Data truncated for column 'a' at row 4 |
+---+--++
2 rows in set (0.00 sec)

mysql> select * from dcml;
+---+
| a |
+---+
| 16.00 |
| 16.25 |
| 16.13 |
| 16.14 |
+---+
4 rows in set (0.00 sec)

_

And on Linux
_

mysql> create table dcml (a varchar(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into dcml values('16.00'),('16.25'),('16.125'),('16.135');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from dcml;
++
| a  |
++
| 16.00  |
| 16.25  |
| 16.125 |
| 16.135 |
++
4 rows in set (0.00 sec)

mysql> alter table dcml modify a decimal(6,2);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 2

mysql> show warnings;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that
corre

Our production system is on 4.0.20 which does not support SHOW WARNINGS
and apparently the ALTER truncates always

mysql> select * from dcml;
+---+
| a |
+---+
| 16.00 |
| 16.25 |
| 16.12 |
| 16.13 |
+---+
4 rows in set (0.00 sec)
_

Linux rule paraphrased: If the value to the right of the rounding column is
a 5 then if the rounding column is even round down if the rounding column is
odd round up.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 18, 2005 10:00 AM
To: Jerry Swanson
Cc: mysql@lists.mysql.com 
Subject: Re: varchar(10) to decimal

Hi,
if varchar represents decimal(6,x) where x>2, it's truncated. Else, it's
converted :


mysql> create table dcml (a varchar(10));
Query OK, 0 rows affected (0.24 sec)

mysql> insert into dcml values('16.00'),('16.25'),('16.125'),('16.135');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from dcml;
++
| a  |
++
| 16.00  |
| 16.25  |
| 16.125 |
++
3 rows in set (0.03 sec)

mysql> alter table dcml modify a decimal(6,2);
Query OK, 3 rows affected, 1 warning (0.24 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql> show warnings;
+-+--++
| Level   | Code | Message|
+-+--++
| Warning | 1265 | Data truncated for column 'a' at row 3 |
+-+--++
1 row in set (0.00 sec)

mysql> select * from dcml;
+---+
| a |
+---+
| 16.00 |
| 16.25 |
| 16.12 |
+---+
3 rows in set (0.00 sec)

Here, only row 3 is truncated !

Mathias



Selon Jerry Swanson <[EMAIL PROTECTED]>:

> decimal(6,2)
>
> On 5/18/05, Philippe Poelvoorde <[EMAIL PROTECTED]> wrote:
> > Jerry Swanson wrote:
> >
> > > I need to change format from varchar(10) to decimal.
> > > When I alter the table the data is trimmed.
> > >
> > > What I'm doing wrrong?
> > >
> > > TH
> > >
> >
> > ALTER TABLE your_table MODIFY your_column double NOT NULL DEFAULT '0.0';
> > should normally work. What is the command you are doing and have you
> > example results ?
> > How did you declare your decimal column ?
> >
> > --
> > Philippe Poelvoorde
> > COS Trading Ltd.
> >
> > --
> > 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

RE: DB design question

[Gordon]

You probably want to add type to both the address and phone tables. Then you
can be selective in your reporting and still get 1 row per student in your
result set. Just remember if your data has the possibility of not having the
information for a student you want to use LEFT JOIN's vs INNER JOIN's or the
student with no "primary" phone [in the following statement] will not be
included in the result set.

SELECT student_id, 
   name, 
   age,  
   h.street_name AS home_address, 
   s.street name AS school_address,
   n.num AS primary_phone
FROM   student s
   LEFT JOIN address s 
   USING (student_id) 
   LEFT JOIN address h 
   USING (student_id)
   INNER JOIN phone_num n
   USING (student_id)
WHERE  h.type = 'Home' 
   AND s.type = 'School'
   AND n.type = 'Primary'

-Original Message-
From: Koon Yue Lam [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 24, 2005 12:34 PM
To: mysql@lists.mysql.com
Subject: DB design question

Hi, here is the case:

one student may have more than one address, and one student may have more 
than one phone number

so the db would be:

student

student_id
name
age

address
---
address_id
student_id
street_name

phone_num
--
student_id
num
extension

the key of 3 tables are student_id

the problems is, when I want to query both student, address and phone num, 
the sql will be

select * from student s, address a, phone_num n
where s.student_id = a.sudent_id
and s.student_id = n.student_id

it won't provide a nice result as data of student are repeated in every row,

address and phone_num's data are repeated in certain rows
The output is not suitable for reporting and may I ask what is the better 
way of design to handle the above case ?

any help would be apreciated

Regards



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: LOAD DATA and skip columns in text file...

[Gordon]

The folowing is out of the current MySQL manual. It looks like you could
create an intermediate table with the fields you are interested in the front
and "garbage" fields on the end. Then build a specific LOAD DATA INFILE with
correct mapping for each file type [assuming you can tell this in your
automated process]. Then after loading all 200 of your files run 1
INSERT...SELECT... with just the pertinent FIELDS from the intermediate
table to your final table and TRUNCATE the intermediate table. 

It probably makes sense for the intermediate table to have minimal indexing
so the initial loads will run faster.
_
mysql> LOAD DATA INFILE 'persondata.txt'
->   INTO TABLE persondata (col1,col2,...);

You must also specify a column list if the order of the fields in the input
file differs from the order of the columns in the table. Otherwise, MySQL
cannot tell how to match up input fields with table columns. 

If an input line has too many fields, the extra fields are ignored and the
number of warnings is incremented. 

If an input line has too few fields, the table columns for which input
fields are missing are set to their default values. Default value assignment
is described in section 13.2.6 CREATE TABLE Syntax. 


-Original Message-
From: Jessica Svensson [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 25, 2005 12:28 PM
To: mysql@lists.mysql.com
Subject: Re: LOAD DATA and skip columns in text file...

That just complicates things alot since i get around 200 files, 6 times a 
day via an automated process and every textfile looks different from the 
other. To just have different load data would make it much easier.

I have read alot of questions about just this and many people is asking for 
this feature. Thats why i'm woundering if it really havent been impemented 
in these 5 years that have passed.

>From: Harald Fuchs <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: mysql@lists.mysql.com
>Subject: Re: LOAD DATA and skip columns in text file...
>Date: 25 May 2005 13:24:55 +0200
>
>In article <[EMAIL PROTECTED]>,
>"Jessica Svensson" <[EMAIL PROTECTED]> writes:
>
> > LOAD DATA and skip columns in text file...
> > What i have found out is that this is not possible in any existing
> > version of mysql, correct?
>
>
> > I found a message from Sinisa Milivojevic @ MySQL AB dated 06/29/2000
> > while searching on google.
>
> > It said "We have entered this on our TODO list one month ago."
>
> > So that would be almost exactly 5 years ago... is it really that this
> > function has not been implemented during these 5 years? If so, then i
> > guess it could be 5 more years before its impelemented and that i
> > should maybe look for other solutions.
>
>Maybe they didn't implement it yet because there's an easy workaround:
>create a temporary table including the columns to be skipped, LOAD it,
>and then use INSERT..SELECT to copy over only the columns you're
>interested in.
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:
>http://lists.mysql.com/[EMAIL PROTECTED]
>

_
Lättare att hitta drömresan med MSN Resor http://www.msn.se/resor/


-- 
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]

RE: Database design query

[Gordon]

IF GroupID, HostID and UserID are unique between the three sets then your
GroupMember  table will work although I would still be tempted to add a
MemberType in the GroupMember table.

Isn't MemberID the Foreign Key to UserID/HostID/GroupID althugh I don't know
if you can have three different foreign key definitions on one field. If not
I think you are stuck with 3 tables instead of trying to do it in one.

-Original Message-
From: rtroiana [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 31, 2005 12:23 PM
To: mysql@lists.mysql.com
Subject: Database design query

Hi All,

 

I'm trying to get data from Active Directory and storing in database. So I
have the following tables with their corresponding primary keys:

 

Group   (GroupID)

Host (HostID)

User (UserID)

GroupMember(GroupID, MemberID)

 

The relations between them according to Active Directory should be as
follows:

 

1)   Host and user can be in one or more groups

2)   Groups can be in one or more groups

 

I was trying to implement these relations through Db constraints. I wanted
HostID and UserID should refer to MemberID as Primary Key.

 

My problems is a foreign key field cannot refer to a part of primary key, so

1)   I should add GroupID in Host and User table, which will be
redundancy of data, or

2)   Instead of adding a new field, I should not have any relations in
the database and just implement it in code.

 

I have tried with three different designs, but all of them have some issues.
I tied to add a new table just for Member that would store unique memberID.
Does it seem like an overhead? I don't if I can just do with existing table
or not. 

 

I read some articles online, some of them say it's good to implement
relations from DB and some say to reduce overhead, relations can be
implemented from code. What would the best database practice that you would
suggest?

 

I'll appreciate any help

 

Thanks,

Reema Duggal Troiana
Senior Software Developer
BitArmor Systems, Inc.
357 North Craig Street
Ground Floor
Pittsburgh, PA 15213
[TEL] 412-682-2200 Ext 314
[FAX] 412-682-2201

 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Mysqldump

[Gordon]

If you just dump the structure with mysqldump and then build 
SELECT INTO OUTFILE  and LOAD DATA INFILE 
statements for each table, the process will run faster than even the
"extended insert" option of mysqldump.

-Original Message-
From: ManojW [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 01, 2005 7:01 PM
To: mySQL list
Subject: Mysqldump

Greetings,
I took a dump of (pretty chunk) database, the output is close to 45G. I
am trying to reload this dump file onto a development  server but it's
taking long time to load the database. Is their a faster way to load the
data in? I am using plain and simple  mysql < dump.sql syntax on a Mysql
4.0.24 server.

Thanks in advance!

Cheers

Manoj


-- 
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]

RE: copy from table to it again

[Gordon]

If you want to change the value in qid to 3 when it is 0 
You really need an update

update answer 
setqid = 3 
where  qid = 0

-Original Message-
From: ÇÈæ ÇÈÑÇåíã [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, December 10, 2003 8:14 AM
To: [EMAIL PROTECTED]
Subject: copy from table to it again

i want to ask you a question 

i have a data in a table `answer` and i want to copy the data which
have a specific value in a filed and inserted into it again but i
will change the value 
   
i try to use this but it's give me an error 
 === 
 INSERT INTO `answer` ( `answer` , `correct`,`qid` ) 
 SELECT a2.`answer` , a2.`correct` , 3 
 FROM `answer` as a2 where a2.qid=0 
===




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Excluding Tables from mysqldump

[Gordon]

We have 2 tables which are roughly half the size of the entire database
{ ~1.5GB}. These 2 tables are rarely changed {1-2 times a quarter}.

Is there a way in mysqldump to exclude these two tables without
specifically naming all of the tables { > 100} we want to include? 

 

 

 

non unique index on MySQL 5.0

[Gordon]

I just installed MySQL 5.0 on my windows machine and was trying to load
the output from a MySQLFront export from our 4.0.16 production system

when I got the following error..


C:\>mysql -u root mailprint < e:\mp20040205.sql

ERROR 1062 at line 7893168: Duplicate entry 'Name-4928 N Brooklyn
Kansas City' for key 3


I'm not sure why I would get a "Duplicate entry" error on an index that
was not defined as Unique. 


Have the rules for indexes changed somewhere between 4.0.16 and 5.0 ?


Here is the table definition from the 4.0.16 machine.

| product_order_features | CREATE TABLE `product_order_features` (
  `ordr_ID` int(11) unsigned NOT NULL default '0',
  `poft_Sub_Month` char(3) NOT NULL default '0',
  `poft_ID` tinyint(3) unsigned NOT NULL auto_increment,
  `poft_Sub_Item` char(1) NOT NULL default '0',
  `poft_Feature` varchar(40) NOT NULL default '',
  `poft_Value` text NOT NULL,
  `poft_Contents` text,
  `poft_Active` enum('Yes','No','Mail Sent','Deleted','PKOR','PK Hold')
NOT NULL default 'Yes',
  `poft_Timestamp` timestamp(14) NOT NULL,
  `poft_Create` datetime NOT NULL default '-00-00 00:00:00',
  PRIMARY KEY  (`ordr_ID`,`poft_Sub_Month`,`poft_Feature`,`poft_ID`),
  KEY `Feature_Value_IDX` (`poft_Feature`,`poft_Value`(20)),
  K
) TYPE=MyISAM |
C:\>mysql -u root mailprint

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 7 to server version: 5.0.0-alpha-nt

 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: non unique index on MySQL 5.0

[Gordon]

Sorry for the repost, but I just realized that the index information for
key 3 did not get copied into the 1st email.

-Original Message-
From: Gordon [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 09, 2004 8:48 AM
To: [EMAIL PROTECTED]
Subject: non unique index on MySQL 5.0

I just installed MySQL 5.0 on my windows machine and was trying to load
the output from a MySQLFront export from our 4.0.16 production system

when I got the following error..


C:\>mysql -u root mailprint < e:\mp20040205.sql

ERROR 1062 at line 7893168: Duplicate entry 'Name-4928 N Brooklyn
Kansas City' for key 3


I'm not sure why I would get a "Duplicate entry" error on an index that
was not defined as Unique. 


Have the rules for indexes changed somewhere between 4.0.16 and 5.0 ?


Here is the table definition from the 4.0.16 machine.

| product_order_features | CREATE TABLE `product_order_features` (
  `ordr_ID` int(11) unsigned NOT NULL default '0',
  `poft_Sub_Month` char(3) NOT NULL default '0',
  `poft_ID` tinyint(3) unsigned NOT NULL auto_increment,
  `poft_Sub_Item` char(1) NOT NULL default '0',
  `poft_Feature` varchar(40) NOT NULL default '',
  `poft_Value` text NOT NULL,
  `poft_Contents` text,
  `poft_Active` enum('Yes','No','Mail Sent','Deleted','PKOR','PK Hold')
NOT NULL default 'Yes',
  `poft_Timestamp` timestamp(14) NOT NULL,
  `poft_Create` datetime NOT NULL default '-00-00 00:00:00',
  PRIMARY KEY  (`ordr_ID`,`poft_Sub_Month`,`poft_Feature`,`poft_ID`),
  KEY `Feature_Value_IDX` (`poft_Feature`,`poft_Value`(20)),
  KEY `Feature_Contents_IDX` (`poft_Feature`,`poft_Contents`(20))
) TYPE=MyISAM |
C:\>mysql -u root mailprint

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 7 to server version: 5.0.0-alpha-nt

 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.



-- 
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]

RE: SELECTing last occurences from a table

[Gordon]

It is possible if you make the number of digits in day all the same by
using something like lpad. 
The secret is to tie all of the values you want together with something
like CONCAT and making sure that
the resulting string sorts in the order you want with its alphabetic
sort sequence. Then MAX gives you
the largest {most recent} value and then substring_index breaks it back
apart. The other bit of magic is
to remember that with MySQL the fields you use to GROUP BY {food.name in
this case} does not have to be 
included in the SELECT. In this case name is not the same as food.name
and probably should be aliased 
something like 'recent_name' to avoid confusion.

select
substring_index(max(concat(lpad(day,4,'0'),'|',name,'|',id,'|',price)),'
|',1) + 0 as day,
 
substring_index(substring_index(max(concat(lpad(day,4,'0'),'|',name,'|',
id,'|',price)),'|',2),'|',-1) as name,
 
substring_index(substring_index(max(concat(lpad(day,4,'0'),'|',name,'|',
id,'|',price)),'|',3),'|',-1) as id,
 
substring_index(substring_index(max(concat(lpad(day,4,'0'),'|',name,'|',
id,'|',price)),'|',4),'|',-1) as price 
from   food 
group by food.name;

mysql> create table food (id int(11) not null primary key, name
char(20), day int(11), price decimal(10,2));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into food
-> values
-> (1, 'tomatoes', 1, 25),
-> (2, 'onions', 1, 13),
-> (3, 'onions', 2, 16),
-> (4, 'tomatoes', 2, 30);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from food;
++--+--+---+
| id | name | day  | price |
++--+--+---+
|  1 | tomatoes |1 | 25.00 |
|  2 | onions   |1 | 13.00 |
|  3 | onions   |2 | 16.00 |
|  4 | tomatoes |2 | 30.00 |
++--+--+---+
4 rows in set (0.00 sec)

mysql> select
substring_index(max(concat(lpad(day,4,'0'),'|',name,'|',id,'|',price)),'
|',1) + 0 as day,
->
substring_index(substring_index(max(concat(lpad(day,4,'0'),'|',name,'|',
id,'|',price)),'|',2),'|',-1) as name,
->
substring_index(substring_index(max(concat(lpad(day,4,'0'),'|',name,'|',
id,'|',price)),'|',3),'|',-1) as id,
->
substring_index(substring_index(max(concat(lpad(day,4,'0'),'|',name,'|',
id,'|',price)),'|',4),'|',-1) as price
-> from   food
-> group by food.name;
+--+--+--+---+
| day  | name | id   | price |
+--+--+--+---+
|2 | onions   | 3| 16.00 |
|2 | tomatoes | 4| 30.00 |
+--+--+--+---+
2 rows in set (0.00 sec)

Also works when foods are last updated on different days

mysql> update food set day = 3 where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select
substring_index(max(concat(lpad(day,4,'0'),'|',name,'|',id,'|',price)),'
|',1) + 0 as day,
->
substring_index(substring_index(max(concat(lpad(day,4,'0'),'|',name,'|',
id,'|',price)),'|',2),'|',-1) as name,
->
substring_index(substring_index(max(concat(lpad(day,4,'0'),'|',name,'|',
id,'|',price)),'|',3),'|',-1) as id,
->
substring_index(substring_index(max(concat(lpad(day,4,'0'),'|',name,'|',
id,'|',price)),'|',4),'|',-1) as price
-> from   food
-> group by food.name;
+--+--+--+---+
| day  | name | id   | price |
+--+--+--+---+
|2 | onions   | 3| 16.00 |
|3 | tomatoes | 4| 30.00 |
+--+--+--+---+
2 rows in set (0.00 sec)

> -Original Message-
> From: Becoming Digital [mailto:[EMAIL PROTECTED] 
> Sent: Sunday, June 01, 2003 5:29 PM
> To: [EMAIL PROTECTED]
> Subject: Re: SELECTing last occurences from a table
> 
> 
> Do you have daily prices for a set number number of foods?  If so, try
> 
> SELECT * FROM food
> LIMIT n,-1;
> 
> where n is the number of items.  There's probably a better 
> way to do it in SQL, I just don't know how.  If you are using 
> PHP, that's another story.
> 
> Edward Dudlik
> Becoming Digital
> www.becomingdigital.com
> 
> 
> - Original Message -
> From: "Mateusz" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Sunday, 01 June, 2003 15:38
> Subject: SELECTing last occurences from a table
> 
> 
> Hi!
> I couldn't find anything in archives, maybe I search for 
> wrong phrase. I'm running MySQL v.3.23.49 (if it's not 
> possible under this version, I may upgrade it). What I need 
> to accomplish is to find only last occurence of all possible 
> items from given column.
> 
> Supposing:
> 
> idnamedayprice
> ---
> 1tomatoes125
> 2onions113
> 3onions216
> 4tomatoes230
> 
> I want to obtain only:
> 
> 3onions216(latest price of onions - 
> from day 2nd)
> 4tomatoes230(latest price of tomatoes - 
> from day 2nd)
> 
> How to do that? (in as little selects as possible)
> Thanks for any help!
> 
> Mateu

MySQL Multi Table Delete

[Gordon]

I have 2 tables t and t1. In this case, t1 is a copy of t. I want to
delete rows from t1 based on criteria on the t table and a relationship
between t ad t1 (in this case the id column). In the results below, I
would think that the delete should have deleted row 1 {1  5  me) and not
row 3 (1  5  they) when I run this statement
 
   delete t1 from t, t1 where t.id = t1.id and t.id=1 and t.name = 'me';

Any ideas on why row 2 is deleted?

Same results on Your MySQL connection id is 38495 to server version:
4.0.10-gamma-nt Windows 2000
or  Your MySQL connection id is 221 to server version:
4.0.13-Max Linux 8.0

This is the contents of t & t1 to start
mysql> select * from t1;
+--+--+--+
| id   | no   | name |
+--+--+--+
|1 |5 | me   |
|2 |7 | you  |
|1 |5 | they |
|2 |5 | me   |
|3 |7 | you  |
|3 |7 | they |
|3 |5 | we   |
+--+--+--+
7 rows in set (0.00 sec)

This is the delete statement

 mysql> delete t1 from t, t1 where t.id = t1.id and t.id=1 and
t.name = 'me';
 Query OK, 2 rows affected (0.00 sec)

This is the result
mysql> select * from t1;
+--+--+--+
| id   | no   | name |
+--+--+--+
|2 |7 | you  |
|2 |5 | me   |
|3 |7 | you  |
|3 |7 | they |
|3 |5 | we   |
+--+--+--+
5 rows in set (0.01 sec)



mysql> show create table t;
+---+---
+
| Table | Create Table
|
+---+---
+
| t | CREATE TABLE `t` (
  `id` int(11) default NULL,
  `no` int(11) default NULL,
  `name` char(20) default NULL
) TYPE=MyISAM |
+---+---
+
1 row in set (0.00 sec)

mysql> show create table t1;
+---+---
-+
| Table | Create Table
|
+---+---
-+
| t1| CREATE TABLE `t1` (
  `id` int(11) default NULL,
  `no` int(11) default NULL,
  `name` char(20) default NULL
) TYPE=MyISAM |
+---+---
-+
1 row in set (0.00 sec)

mysql> truncate t1;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 select * from t;
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+--+--+--+
| id   | no   | name |
+--+--+--+
|1 |5 | me   |
|2 |7 | you  |
|1 |5 | they |
|2 |5 | me   |
|3 |7 | you  |
|3 |7 | they |
|3 |5 | we   |
+--+--+--+
7 rows in set (0.00 sec)

mysql> delete t1 from t, t1 where t.id = t1.id and t.id=1 and t.name =
'me';
Query OK, 2 rows affected (0.00 sec)

mysql> select * from t1;
+--+--+--+
| id   | no   | name |
+--+--+--+
|2 |7 | you  |
|2 |5 | me   |
|3 |7 | you  |
|3 |7 | they |
|3 |5 | we   |
+--+--+--+
5 rows in set (0.01 sec)



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Any Issues with 3.23.56 and 4.0.13 using mysqld_multi on one server

[Gordon]

Is it possible to run 3.23.43 and 4.0.13 on the same server? 

I don't see anything in the documentation that should prohibit this as
long as the installations are in different directories, use different
sockets/ports and data directories.

Can this be done utilizing mysqld_multi and if so are there any special
considerations?



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: weird kind of join

[Gordon]

You might also try  
FROM table_a
INNER JOIN table_b
ON table_b.code = substring_index(table_a.code,';',1)

SUBSTRING_INDEX(str,delim,count) 
Returns the substring from string str before count occurrences of the
delimiter delim. If count is positive, everything to the left of the final
delimiter (counting from the left) is returned. If count is negative,
everything to the right of the final delimiter (counting from the right) is
returned. 
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'

This function is multi-byte safe.


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, September 29, 2004 12:35 PM
To: Diana Castillo
Cc: [EMAIL PROTECTED]
Subject: Re: weird kind of join

try this (not tested):

FROM table_a
INNER JOIN table_b
ON table_b.code LIKE concat(table_a.code,';%')

or this:

FROM table_a
INNER JOIN table_b
ON table_b.code RLIKE concat('^',table_a.code,';')


http://dev.mysql.com/doc/mysql/en/String_comparison_functions.html
http://dev.mysql.com/doc/mysql/en/Regexp.html

It's not going to be as quick as a direct lookup because of the CONCAT() 
but at least we preserve the possibility of using an index for 
table_b.code.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


"Diana Castillo" <[EMAIL PROTECTED]> wrote on 09/29/2004 12:39:40 PM:

> is there anyway to do a joint between a table that has codes like 
> this 10004;XXX  or DE;YYY
> with a table that has just the first part e.g 10004 or DE as the code
> There is no set length to the code , all I know is that it is the 
> part before the semicolon.
> so, I can't say 
> FROM table_a  INNER JOIN table_b ON (table_a_code = 
left(table_b.code,2))
> because I will only match the ones that have 2 character codes.
> 
> 
> Diana Castillo
> Global Reservas, S.L.
> C/Granvia 22 dcdo 4-dcha
> 28013 Madrid-Spain
> Tel : 00-34-913604039 Ext 216
> Fax : 00-34-915228673
> email: [EMAIL PROTECTED]
> Web : http://www.hotelkey.com
>   http://www.destinia.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: matching people with projects via resources

[Gordon]

This may not be elegant, but why not define a 3rd table  proj_c containing
proj and project_rsrc. This assumes that when you define a project you know
how many resources are required. 
CREATE TABLE proj_c (
   proj varchar(11) default NULL, 
   project_rsrc INT default 0);

INSERT INTO proj_c 
VALUES
  ('ark',2),('cabin',1),('monalisa',2),('jeans',2);

Then the sql becomes 

mysql> SELECT name, count(people.rsrc) AS person_rsrc, project_rsrc,
project.proj
-> FROM   people
->LEFT JOIN project
->USING (rsrc)
->LEFT JOIN proj_c
->ON (project.proj = proj_c.proj)
-> GROUP BY name, project.proj
-> HAVING person_rsrc = project_rsrc
-> ;
+-+-+--+--+
| name| person_rsrc | project_rsrc | proj |
+-+-+--+--+
| davinci |   2 |2 | monalisa |
| lincoln |   1 |1 | cabin|
| noah|   2 |2 | ark  |
| noah|   1 |1 | cabin|
+-+-+--+--+
4 rows in set (0.00 sec)
-Original Message-
From: Laszlo Thoth [mailto:[EMAIL PROTECTED] 
Sent: Friday, October 01, 2004 10:53 AM
To: [EMAIL PROTECTED]
Subject: matching people with projects via resources

I'm having difficulty constructing a query.  I've got two kinds of
information:
a table of resources that various people have, and a table of resources that
various projects need.

===
CREATE TABLE `people` (
  `name` varchar(11) default NULL,
  `rsrc` varchar(15) default NULL
);

INSERT INTO `people` VALUES
('noah','wood'),('noah','canvas'),('lincoln','wood'),('davinci','canvas'),('
davinci','paint');

CREATE TABLE `project` (
  `proj` varchar(11) default NULL,
  `rsrc` varchar(15) default NULL
);

INSERT INTO `project` VALUES
('ark','wood'),('ark','canvas'),('cabin','wood'),('monalisa','canvas'),('mon
alisa','paint'),('jeans','canvas'),('jeans','sewingmachine');
===

I need a query that will tell me which people have the resources required to
complete a given project.  Unfortunately all I can get are incomplete
matches:
I'm not sure how to express the concept of "fully satisfying the
requirements"
to MySQL.

Restructuring the tables is allowed: I'm not tied to the current schema, I
just
need to solve the problem.  The only limit is that resources must be
arbitrary:
I can't use a SET to define resources because I might want to insert a new
resource at some future point without redefining the column type.

I'm pretty sure this is a good starting point, but that's just matching
resource
to resource without excluding Lincoln from building an Ark (no canvas).

mysql> SELECT project.proj,project.rsrc,people.name FROM project LEFT JOIN
people ON project.rsrc=people.rsrc;
+--+-+-+
| proj | rsrc| name|
+--+-+-+
| ark  | wood| noah|
| ark  | wood| lincoln |
| ark  | canvas  | noah|
| ark  | canvas  | davinci |
| cabin| wood| noah|
| cabin| wood| lincoln |
| monalisa | canvas  | noah|
| monalisa | canvas  | davinci |
| monalisa | paint   | davinci |
| jeans| canvas  | noah|
| jeans| canvas  | davinci |
| jeans| sewingmachi | NULL|
+--+-+-+

It would also be sufficient but less optimal to solve a subset of this
problem,
where I only determine "who could complete this project" for a single
project
rather than trying to match all projects to all people in one query.

-- 
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]

RE: Yet another LEFT JOIN question

[Gordon]

Try something like this 

SELECT A1.ID, 
   SUM(IF(ISNULL(C.AdID),0,1)) AS Clicks, 
   SUM(IF(ISNULL(V.AdID),0,1))  AS Views
FROM   Ads A1 
   LEFT JOIN Clicks C 
   ON A1.ID = C.AdID
   LEFT JOIN Views V 
   ON A1.ID = V.AdID
GROUP BY A1.ID

-Original Message-
From: Ron Gilbert [mailto:[EMAIL PROTECTED] 
Sent: Saturday, December 04, 2004 2:09 PM
To: <[EMAIL PROTECTED]> <[EMAIL PROTECTED]>
Subject: Yet another LEFT JOIN question

I have three tables, 'Ads' is a list of ads, 'Clicks' is a simple list 
of every time a Ad was clicked on with the Ads ID, and 'Views' is a 
simple list of views that ad got, with the Ads ID.

I am trying to SELECT a list of all the ads, with a count for clicks 
and a count for views, but my LEFT JOIN is not producing what I 
thought.

If the click count is 0, then the view count is OK, but if not, then 
the Click count and view count are equal, but a much too large number.

If I just SELECT for views or clicks, then it works OK, it's when they 
are combined that it falls apart.

SELECT A1.ID, Count(C.AdID) AS Clicks, Count(V.AdID) AS Views
FROM Ads A1 LEFT JOIN Clicks C ON A1.ID = C.AdID
 LEFT JOIN Views V ON A1.ID = V.AdID
group by A1.ID

CREATE TABLE `Clicks` (
   `AdID` int(10) NOT NULL default '0'
   [snip]
)
CREATE TABLE `Views` (
   `AdID` int(10) NOT NULL default '0'
   [snip]
)
CREATE TABLE `Ads` (
   `ID` int(10) NOT NULL default '0'
   [snip]
)

I have tried a lot of combinations for LEFT JOIN with no luck.  I've 
read all the posts on this list and they don't seem to be doing what I 
am, or else I'm not seeing it.

Thanks, Ron


-- 
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]

RE: How to select every second record

[Gordon]

Try this.
The second set ... select gives you what you want. 
However, the group by may interfere with the rest of your logic. You also
don't really need the mod(@a,2) in the result set, just in the having.
mysql> set @a:=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @a:[EMAIL PROTECTED],mod(@a,2),ordr_ID, poft_Sub_Month from 
er_poft limit
10;
+--+---+-++
| @a:[EMAIL PROTECTED] | mod(@a,2) | ordr_ID | poft_Sub_Month |
+--+---+-++
|1 | 1 |4245 | 01 |
|2 | 0 |4323 | 01 |
|3 | 1 |4328 | 01 |
|4 | 0 |4329 | 01 |
|5 | 1 |4331 | 01 |
|6 | 0 |4332 | 01 |
|7 | 1 |4333 | 01 |
|8 | 0 |4335 | 01 |
|9 | 1 |4343 | 01 |
|   10 | 0 |4344 | 01 |
+--+---+-++
10 rows in set (0.00 sec)

mysql> set @a:=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @a:[EMAIL PROTECTED],mod(@a,2),ordr_ID, poft_Sub_Month from 
er_poft 
group by 3,4 having mod(@a,2) = 0 limit 5;
+--+---+-++
| @a:[EMAIL PROTECTED] | mod(@a,2) | ordr_ID | poft_Sub_Month |
+--+---+-++
|2 | 0 |4323 | 01 |
|4 | 0 |4329 | 01 |
|6 | 0 |4332 | 01 |
|8 | 0 |4335 | 01 |
|   10 | 0 |4344 | 01 |
+--+---+-++
5 rows in set (0.00 sec)
-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 27, 2005 8:50 AM
To: Alessandro Sappia; mysql@lists.mysql.com
Subject: RE: How to select every second record

[snip]
Jay Blanchard wrote:
> [snip]
> Is it possible to select only every second record from a record set?
>  
> I should select the record-number 1, 3, 5, 7, 9, ... or record-number
2,
> 4,
> 6, 8, ...
>  
> Can this be done with LIMIT?
> [/snip]
> 
> Not LIMIT, but you can use MOD, especially with an auto-increment
field
> (id in this case is the auto-increment field)
> 
> select * from table where mod(id, 2) <> '0' returns odd rows
> select * from table where mod(id, 2) <> '1' returns even rows
> 
You have to do it with LIMIT
beacuse id may not help you...
so
select * from table where  [group by ]
  [order by  [desc]] LIMIT 2,1

this select just second resultrow from any kind of resultset made using 
every thing you like in where/order by/groub by and not being limited by

  using IDs (auto_increment)
[/snip]

The problem is that this only returns ONE record, the OP wanted every
other record

-- 
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]

RE: Syntax Failures with SELECT SUBSTRING - Help!

[Gordon]

Try SELECT SUBSTRING(AnimalName, 1, 1)

MySQL wants the "(" to immediately follow the function i.e. no spaces.

-Original Message-
From: Sue Cram [mailto:[EMAIL PROTECTED] 
Sent: Thursday, February 03, 2005 9:28 AM
To: mysql@lists.mysql.com
Subject: Syntax Failures with SELECT SUBSTRING - Help!

Neither my developer nor I can figure out this one!  The package I'm using
is "Animal Shelter Manager" and is written in SQL.  Every other installation
of the product can use the SELECT SUBSTRING command except mine!  I use the
following code:

SELECT SUBSTRING (AnimalName, 1, 1)
FROM Animal

and I get "Syntax error or access violation near "(AnimalName, 1,1) FROM
Animal" at Line 1."   I can use the following with no error, so I know it
has to be in the SUBSTRING option:

SELECT AnimalName
FROM Animal

I also get a syntax error when I use an "IF" statement.  I get the same
error on my home computer (PC) and the PC's at our shelter office.  I also
get a message at startup that says:  "mmtask.exe Unable to locate component.
Application has failed to start because mmvcp70.dll was not found.
Reinstalling application may fix problem."  

Don't know if this is related to the substring error or not.   I have
reinstalled my application and it doesn't help.  I think I"m using the
current release of SQL but don't know how to check for sure.  As you can
probably tell, I'm new to SQL.  Can anyone help me with this problem?  

Thanks,
Sue in Sequim WA



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

FW: Backup / Restore

[Gordon]

 

We have built an alternative save restore process. First we take all of the
.frm files  and build

 

 "select * into outfile '/path/tablename.txt' from tablename" 

 

statements. We also dump the structure only and put it in the same
directory. This runs much faster than myysqldump and every table is in its
own file. The real advantage for us is that for many user errors we can
quickly load the few tables into a 2nd database on the server and then
reconstruct. On our 2 processer Pentium server a 650,000 row table takes 7
seconds to save or load. We have a mixture of MyISAM and INNODB tables and
this process works for both. The primary reason we went to it was we were
using mysqldump and encounterd a case where the output file grew to > 2GB.
Restores worked fine until one day we had to do a restore and for some
reason the file was corupted about half way through. The tables we were
trying to get back happened to be at the end of the file and we could not
find a tool to bypass the corupted data. 

 

Our database is ~ 3 GB  with data and indexes and the backup takes a few
minutes to run in total. 

 

I would be iterested if anyone on the list sees any issues with this as a
backup/restore methodology assuming we still do lock tables and use the
binary log. This approach does take some additional admin effort if we add
or drop tables.

 

Autoincrement in MYISAM vs INNODB

[Gordon]

It is my understanding that at least through 4.0.14, INNODB does not
support using autoincrement on the last field in a multi field primary
key.
i.e. if a table has a primary key of three fields like 
cpny_ID, acct_ID, list_ID 
in MYISAM you can add the autoincrement attribute to list_ID and it will
sequence within the cpny_ID, acct_ID group.

Are there any plans to support this in INNODB?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

System time vs MySQL time

[Gordon]

We are running MySQL 4.0.14 on  redhat 8.0.

The server and MySQL have been running fine for over 60 days.

Sometime yesterday afternoon the time reported in mysql  using 

mysql> select now();

+-+

| now()   |

+-+

| 2003-10-31 20:22:36 |

+-+

1 row in set (0.01 sec) 

suddenly advanced by 12 hours [It is 8:22 AM here]. 

Timestamp fields were correct at 2:00 PM  and by 5:00 PM the shift
occurred.

 

When you create a file on the server, the file create date/time is
correct.

I know this may be a redhat issue, but we don't know where to look. 

Does MySQL use some other system clock then the one redhat uses to set
file create time? 

 

Does anyone have any thoughts.

 

The website running on the database collects orders and schedules the
orders for production. Obviously the date/time are critical.

FW: Strategies for optimizing a read-only table

[Gordon]

What about making the table INNODB? If you make INNODB's buffer large
enough, the high activity data/index blocks are retained in memory.
INNODB's non blocking read and single statement transaction default
should make the "transaction overhead" minimal and INNODB doesn't have
any of the HEAP table restrictions. Caching the data closer to the
application layer normally gives better performance than OS level cache
or Ram disk. 

-Original Message-
From: Matt W [mailto:[EMAIL PROTECTED] 
Sent: Sunday, November 09, 2003 4:55 PM
To: [EMAIL PROTECTED]
Subject: Re: Strategies for optimizing a read-only table

Hi Jeremy,

Sorry, it seems like I'm saying this a lot lately. Is it not true that
if the whole table will fit in [free] RAM, that the OS will cache the
file data and there is no need for a RAM disk. I don't really see how
performance would be any different than using a RAM disk. Either way,
you will still have the overhead of the filesystem calls, even if data
isn't actually read from disk, unlike with a HEAP table.

Jonathan, I think a packed table would actually make things slower once
the data is cached (overhead of unpacking the data). It's just faster
when the data has to be *physically* read from disk, since the data is
smaller.

If disk space isn't an issue and the table doesn't have any TEXT/BLOB
columns, make the rows fixed length if they aren't (change VARCHAR >
CHAR). This will give a small performance improvement.

Also, if you don't have any TEXT/BLOB columns, you could load the data
into a HEAP table, which *may* make reads *slightly* faster -- depending
on your queries. Remember, with HEAP tables, indexes can't be used in
all cases that MyISAM can. You can only search on indexes with =, <=>,
IS NULL, and IN (); no range searches with <, >, BETWEEN, etc.; and you
can only use the full index length, no prefixes. However, none of this
is true in 4.1+, since you can have BTREE indexes with HEAP tables, not
just HASH. :-)

But you know what should actually be the best thing for your read-only
table? MySQL 4's query cache! :-) Have you thought about this? Or do
your queries differ too much that the cache can't be used?

Hope this helps.


Matt


- Original Message -
From: "Jeremy Zawodny"
Sent: Saturday, November 08, 2003 11:48 PM
Subject: Re: Strategies for optimizing a read-only table


> On Tue, Nov 04, 2003 at 08:45:08PM -0500, Jonathan Terhorst wrote:
> >
> > I could have sworn I posted this once before, but apparently it got
> > lost somewhere. Apologies if you're seeing this twice:
> >
> > I'm wondering what I can do with MySQL to optimize reads (SELECTs)
> > on a read-only table where data will never be INSERTed or
> > UPDATEd. Okay, that's not entirely correct--the database will be
> > rebuilt every night but it's small (~20,000 rows) and all the
> > writing will take place at once, when the DB is offline to users. In
> > contrast we anticipate read activity on the DB to be high, making it
> > worth putting some thought into this. So far my only thoughts have
> > been a) myisampack and b) to index every single column that our
> > application searches on, since the calculations needed to build said
> > indices can be performed once and forgotten. (Disk space isn't
> > really an issue but myisampack is said to speed up individual row
> > retrieval.)
> >
> > Any other ideas? I've searched for a way to manually mark MySQL
> > tables read-only, but to no avail. Thanks,
>
> Will the whole table fit comfortable in RAM?  If so, you could store
> it in a ram disk to prevent disk I/O from ever getting in the way.
>
> Jeremy


-- 
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]

Interfaces to 5.0.0

[Gordon]

A few weeks ago I download the windows version for 5.0.0 and installed
it on my machine. After successfully installing the new version I was
able to connect with all of my old tools including MyCC 0.9.3,
MySQLFront, SQLyog, cmd and MySQL Administrator. 

Yesterday I downloaded the 5.0.0 RPM's for linux and only cmd and the
9.0.4 version of MyCC. 

Is the interface different on the linux verion vs the windows version?
Is there some setting I can use to allow the old {and current MySQL
Administrator} to connect?



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySQL function

[Gordon]

Has anyone out there written a Credit Card Validation routine as a
user-definable function (UDF)?

We now have a requirement to collect credit card data through our Web
Site.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

DELETE with no WHERE clause

[Gordon]

DID DELETE FROM XXX with no where clause stop acting like truncate in
4.0.5?

In 3.23.51 and earlier versions of 4.0.x 

mysql> delete from product_order_main;
GIVES THIS
Query OK, 0 rows affected (0.10 sec)

AND RESETS THE AUTOINCREMENT VALUE

mysql> insert into product_order_main values
(null,'rcl1','hh','ab','pending','yes','20021223',now());
Query OK, 1 row affected (0.10 sec)

mysql> select * from product_order_main;
+-+-+-+-+-+-+---
-+-+
| ordr_ID | cpny_ID | prod_ID | acct_ID | pord_Status | pord_Active |
pord_Timestamp | pord_Create |
+-+-+-+-+-+-+---
-+-+
|   1 | rcl1| hh  | ab  | Pending | Yes |
2002122300 | 2002-12-23 11:14:18 |
+-+-+-+-+-+-+---
-+-+
1 row in set (0.10 sec)




USING 4.0.5 I GET

mysql> delete from product_order_main;
Query OK, 1 row affected (0.00 sec)

mysql> insert into product_order_main values
(null,'rcl1','hh','ab','pending','yes','20021223',now());
Query OK, 1 row affected (0.00 sec)

mysql> select * from product_order_main;
+-+-+-+-+-+-+---
-+-+
| ordr_ID | cpny_ID | prod_ID | acct_ID | pord_Status | pord_Active |
pord_Timestamp | pord_Create |
+-+-+-+-+-+-+---
-+-+
| 225 | rcl1| hh  | ab  | Pending | Yes |
2002122300 | 2002-12-23 11:19:10 |
+-+-+-+-+-+-+---
-+-+
1 row in set (0.00 sec)



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Left join returns records it shouldn't

[gordon]

bcd38d80575ab790f0e86c92a7af23','gordonb','2003-01-20
 17:12:25',106,'Y');
INSERT INTO dns_rec VALUES 
('6f065a6b44888e4f43f2ec31411b2f49','pppte04-422','IN',NULL,'A',NULL,'4133269be2eb34a5c82d964a64ddbefb','gordonb','2003-01-20
 17:12:25',285,'Y');
INSERT INTO dns_rec VALUES 
('b36b250e8c66b2b3b133219b80dbe079','','IN',NULL,'MX',10,'d59ceba889986c4fdfbe27c3c8a919d6','gordonb','2003-01-20
 17:12:25',201,'Y');
INSERT INTO dns_rec VALUES 
('e2777c044daf17127184f8033c751249','28','IN',NULL,'A',NULL,'bc35afd157ffa6c8729557a6fb62810d','gordonb','2003-01-20
 17:12:25',234,'Y');
INSERT INTO dns_rec VALUES 
('00105a3bcac286c9f9487dfe896ad220','0b','IN',NULL,'A',NULL,'d982c79ede90bc888c2ec9757f143e9d','gordonb','2003-01-20
 17:12:25',290,'Y');
INSERT INTO dns_rec VALUES 
('eb74f350c3ac84314caca073ec778f2a','54','IN',NULL,'A',NULL,'72ccd8a4558c99fa55fca52f96585d51','gordonb','2003-01-20
 17:12:25',297,'Y');
INSERT INTO dns_rec VALUES 
('9eb0667cd82601f86b1e5c1bfac272b5','3b','IN',NULL,'A',NULL,'072dda1bea720f07d2a262ed7a976ff6','gordonb','2003-01-20
 17:12:25',298,'Y');
INSERT INTO dns_rec VALUES 
('355d0d6199452a39e70d44353337a777','d8','IN',NULL,'A',NULL,'df835ba0b73348a43db8cf1845bdb431','gordonb','2003-01-20
 17:12:25',302,'Y');
INSERT INTO dns_rec VALUES 
('3aceea99953c7feadfb076ad1f69f401','82','IN',NULL,'A',NULL,'7ae650b7b7483c4377ca0f48f28e821d','gordonb','2003-01-20
 17:12:25',303,'Y');
INSERT INTO dns_rec VALUES 
('f65782e0966b230cac422fdb73d377cd','2a','IN',NULL,'A',NULL,'89977656fc335b5d3cdb3387a3ce9d23','gordonb','2003-01-20
 17:12:25',304,'Y');

>Fix:

Dropping right_idx fixes the query for the test data but it makes
it impossibly slow for the full database.

>Submitter-Id:  
>Originator:Gordon Burditt
>Organization:
 
Internet America
>MySQL support: none [none | licence | email support | extended email support ]
>Synopsis:  Left join returns records it shouldn't 
>Severity:  serious 
>Priority:  medium 
>Category:  mysql
>Class: sw-bug 
>Release:   mysql-3.23.54 (FreeBSD port: mysql-server-3.23.54)
>Server: /usr/local/bin/mysqladmin  Ver 8.23 Distrib 3.23.54, for portbld-freebsd4.7 
>on i386
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version  3.23.54-log
Protocol version10
Connection  mysql.burditt.org via TCP/IP
TCP port3306
Uptime: 35 days 23 hours 2 min 29 sec

Threads: 1  Questions: 1736390  Slow queries: 1517  Opens: 445  Flush tables: 1  Open 
tables: 64 Queries per second avg: 0.559
>Environment:

System: FreeBSD hammy.lonestar.org 4.7-RELEASE FreeBSD 4.7-RELEASE #0: Tue Dec  3 
22:49:06 CST 2002 [EMAIL PROTECTED]:/scratch5/i386-obj/usr/src/sys/HAMMY  
i386


Some paths:  /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Using builtin specs.
gcc version 2.95.4 20020320 [FreeBSD]
Compilation info: CC='cc'  CFLAGS='-O -pipe '  CXX='cc'  CXXFLAGS='-O -pipe  
-felide-constructors -fno-rtti -fno-exceptions'  LDFLAGS=''
LIBC: 
-r--r--r--  1 root  wheel  1218496 Dec  3 23:29 /usr/lib/libc.a
lrwxrwxr-x  1 root  wheel  9 Dec  3 23:47 /usr/lib/libc.so -> libc.so.4
-r--r--r--  1 root  wheel  574916 Dec  3 23:47 /usr/lib/libc.so.4
Configure command: ./configure '--localstatedir=/var/db/mysql' '--without-perl' 
'--without-debug' '--without-readline' '--without-bench' '--with-mit-threads=no' 
'--with-libwrap' '--with-low-memory' '--with-comment=FreeBSD port: 
mysql-server-3.23.54' '--program-prefix=' '--with-innodb' '--enable-assembler' 
'--with-berkeley-db' '--prefix=/usr/local' 'i386-portbld-freebsd4.7' 'CFLAGS=-O -pipe 
' 'CXX=cc' 'host_alias=i386-portbld-freebsd4.7' 'build_alias=i386-portbld-freebsd4.7' 
'target_alias=i386-portbld-freebsd4.7' 'CC=cc' 'CXXFLAGS=-O -pipe  
-felide-constructors -fno-rtti -fno-exceptions'
Perl: This is perl, version 5.005_03 built for i386-freebsd

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: buggy round()

[Gordon]

This round discussion has nothing to do with floating point
approximations.

Most round routines are statistically biased on the high side. The MySQL
round is statistically correct. The more accurate rounding rule is 

If digit following the column you are rounding is a 5
If digit in the round column is even then 
round down 
else
round up
else
if digit > 5 then
round up
else
round down
end if
end if 

I built an Excel spreadsheet and the "regular" round routine introduced
a 10 times larger round error than the one above on ~200 values.

Gordon
Interstate Software
A MySQL training partner



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

FW: Difficulties between OS versions

[Gordon]

Two of my co-workers attended the MySQL training in San Francisco last
month.  We have successfully installed 4.01 on a SUN Ultra 250 running
Solaris 2.8.  Four myisam tables were created and populated.  When we
tried
to install the same software on a SUN Sparc 20 running Solaris 2.6
errors
were encountered.  Something about a missing file 'librt.so.1'.  We
thought
the OS version was 2.7.  Not correct!  I downloaded the binary MySQL
version
3.22.32 from the archive area on the website and it installed.  However,
the
same 4 tables that are working under Solaris 2.8 are presenting errors
under
Solaris 2.6.  The error message states that there is no field
information
available for any of the 4 tables.  Am I doing something wrong.

Next, does this older version have INNODB support available?   

Tom Lee
Group Lead
LM AERO - Field Information Network
Software Development/Administration Group
VOICE: 817-763-6541
FAX: 817-763-6548


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: LOOKUP

[Gordon]

This set of responses is getting away from the original question. The
lookup "data type" in Microsoft Access is not a data type at all. It
creates a join with another table underneath the surface.

If you try to implement this with ENUM and Alter Table you will destroy
the existing data/ENUM relationships for any values already in the table
which fall after the value in the list which you removed.

Either just add another table and do joins or live with only being to
add to the list.

Gordon Bruce
Interstate Software
A MySQL Training Partner

> -Original Message-
> From: Andrew Hazen [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, April 17, 2002 2:40 PM
> To: 'Gurhan Ozen'; [EMAIL PROTECTED]
> Subject: RE: LOOKUP
> 
> Good point.  Also, how would you capture the current enum list to add
or
> substract from?  Might have to hold that in an array or constant of
some
> sort depending upon the language used.
> 
> Andrew Hazen
> 
> 
> -Original Message-
> From: Gurhan Ozen [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, April 17, 2002 3:32 PM
> To: Andrew Hazen; [EMAIL PROTECTED]
> Subject: RE: LOOKUP
> 
> Hi,
> Your solution would work if the set of the values you'll get from one
> table
> is primary key.. If it is not a primary key, i.e. allowing duplicate
> values,
> then everytime there is an insertion to the table you will have to
check
> to
> see if it is a new-added value or not.
> 
> Gurhan
> 
> -Original Message-
> From: Andrew Hazen [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, April 17, 2002 1:58 PM
> To: 'Gurhan Ozen'; 'Simon Tienery'; [EMAIL PROTECTED]
> Subject: RE: LOOKUP
> 
> 
> That's what I thought too.  A workaround occurs to me though, but I'm
> not sure if it would muck things up.
> 
> At any point where you make a change to the table you want to
reference
> as the "lookup" (as in Access), you could include another sql command
to
> alter the table with the enum column to change the list of accepted
> values for the enum.
> 
> Would this work Gurhan, or would the constant ALTER table commands
mess
> things up?
> 
> Andrew Hazen
> 
> 
> -Original Message-
> From: Gurhan Ozen [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, April 17, 2002 1:46 PM
> To: Andrew Hazen; 'Simon Tienery'; [EMAIL PROTECTED]
> Subject: RE: LOOKUP
> 
> Ugh, I am being too goofy today..
> Simon, the answer to the question is no, you can't do that in MySQL.
> You can, however, create a column with enum datatype and define set of
> valid
> values.
> In a case where you try to insert an invalid value to an enum field,
it
> will
> insert an empty string for it.
> Sincerely,
> 
> Gurhan
> 
> 
> -Original Message-
> From: Andrew Hazen [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, April 17, 2002 12:54 PM
> To: 'Gurhan Ozen'; 'Simon Tienery'; [EMAIL PROTECTED]
> Subject: RE: LOOKUP
> 
> 
> Gurhan,
> 
> I think Simon was looking for a way to make the enum dynamic from
> another table. ??
> 
> Andrew Hazen
> 
> 
> -Original Message-
> From: Gurhan Ozen [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, April 17, 2002 10:22 AM
> To: Simon Tienery; [EMAIL PROTECTED]
> Subject: RE: LOOKUP
> 
> Hi ..
> You can define ENUM datatype for a column while creating the table and
> along
> with the possible values, and if you are using phpMyAdmin to admin
> MYSQL,
> it will only give you the possible values for that ENUM column.
> 
> See: http://www.mysql.com/doc/C/o/Column_types.html
> http://www.phpmyadmin.net/
> 
> Gurhan
> 
> -Original Message-
> From: Simon Tienery [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, April 17, 2002 3:50 AM
> To: [EMAIL PROTECTED]
> Subject: LOOKUP
> 
> 
> Dear Group,
> 
> Could anyone help me please, I want to create an ENUM column in one
> table
> that lists the entries in another table.
> 
> For example, the sales table to have a "staff" column that
automatically
> gives a choice of the names of all staff listed in the staff table.
> 
> I have been told that Access has a data type that does this called
> lookup,
> but I only want to use MySQL.
> 
> I couldn't locate the answer in the manual and I'd appreciate it if
> someone
> can explain how this can be achieved / worked around.
> 
> many thanks  Simon T
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> 

RE: how to delete from one table based on external conditions?

[Gordon]

Try

delete table1.* from table1 inner join table2 on table2.x=table1.y

You may not need the ".*" in table1.* but I know it works with the ".*".

delete table1.* from table1, table2 where table2.x=table1.y

>From the manual
6.4.6 DELETE Syntax

DELETE [LOW_PRIORITY | QUICK] FROM table_name
[WHERE where_definition]
[ORDER BY ...]
[LIMIT rows]

or

DELETE [LOW_PRIORITY | QUICK] table_name[.*] [table_name[.*] ...] FROM
table-references [WHERE where_definition]

Gordon Bruce
Interstate Software
A MySQL Training Partner

> -Original Message-
> From: Inandjo Taurel [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, April 18, 2002 10:37 AM
> To: [EMAIL PROTECTED]
> Subject: how to delete from one table based on external conditions?
> 
> hi,
> i would like to know how i can achieve this kind of query:
> delete form table1 where table2.x=table1.y
> I checked the manual, there nothing like that!
> Am using mysql 3.23.49
> 
> SQL SQL SQL SQL SQL
> 
> 
> 
> _
> Send and receive Hotmail on your mobile device: http://mobile.msn.com
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail  [EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: delta between rows?

[Gordon]

If you have a primary key which is an autoincrement field then the
following works.

CREATE TABLE `tab1` 
   (
   Key1 smallint(5) unsigned NOT NULL auto_increment,
   ColA float default NULL,
   PRIMARY KEY (`Key1`)
   )
TYPE=MyISAM;

insert into tab1 values (1, 3.4), (2,4.6), (3, 3.1), (4,8.2), (5,6.4);

Select * from tab1;

mysql> Select * from tab1;
+--+--+
| Key1 | ColA |
+--+--+
|1 |  3.4 |
|2 |  4.6 |
|3 |  3.1 |
|4 |  8.2 |
|5 |  6.4 |
+--+--+
5 rows in set (0.00 sec)

create   temporary table t1 
select Key1+1 as prev, colA 
from tab1
order by Key1;

select   key1, 
 (tab1.colA - t1.colA) as Delta 
from tab1 inner join t1 
  on (tab1.key1 = t1.prev)
order by key1;

+--+---+
| key1 | Delta |
+--+---+
|2 |  1.1980926514 |
|3 |  -1.5 |
|4 |  5.0990463257 |
|5 | -1.7971389771 |
+--+---+
4 rows in set (0.00 sec)

Gordon Bruce
A US MySQL Training Partner

> -Original Message-
> From: Nissim Lugasy [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, April 24, 2002 2:57 PM
> To: [EMAIL PROTECTED]
> Subject: delta between rows?
> 
> To Mysql Team
> 
> how can I generate a list of deltas between columns in different rows
for
> the entire table?
> what I need is an sql command that does something like this:
> for N =0 to i do : select "colA of current rowN" - "colA of pervious
> row(N-1)" from tab1;
> 
> ColA = floating point number.
> 
> Thanks
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail  [EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: geometric mean aggregate function

[Gordon]

This is probably not the most elegent solution, but here is something
that works in 3 SQL statements using user variables.

mysql> select @gmean := 1, @ct := count(*) from g_mean;
mysql> select @gmean := @gmean * test_val from g_mean;
mysql> select power(@gmean, (1/@ct)) as Geo_Mean;

See details below

Gordon Bruce
Interstate Software
A MySQL US Training Partner

mysql> create table g_mean (test_val float);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into g_mean values (23),(32),(12),(145),(18);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0



mysql> select avg(test_val) from g_mean;
+---+
| avg(test_val) |
+---+
|46 |
+---+
1 row in set (0.00 sec)



mysql> select @gmean := 1, @ct := count(*) from g_mean;
+-+-+
| @gmean := 1 | @ct := count(*) |
+-+-+
|   1 |   5 |
+-+-+
1 row in set (0.00 sec)

mysql> select @gmean := @gmean * test_val from g_mean;
+-+
| @gmean := @gmean * test_val |
+-+
|  23 |
| 736 |
|8832 |
| 1280640 |
|23051520 |
+-+
5 rows in set (0.00 sec)

mysql> select power(@gmean, (1/@ct)) as Geo_Mean;
+---+
| Geo_Mean  |
+---+
| 29.685196 |
+---+
1 row in set (0.02 sec)
X

> -Original Message-
> From: Andrew Gould [mailto:[EMAIL PROTECTED]]
> Sent: Saturday, May 11, 2002 7:51 AM
> To: [EMAIL PROTECTED]
> Subject: geometric mean aggregate function
> 
> Has anyone out there developed a costum, aggregate
> function for calculating the geometric mean of
> populations?  If not, would anyone be interested in
> creating one?
> 
> My development experience/skills is limited to SQL and
> simple Python scripts; so I had difficulty
> understanding the documentation on creating functions.
> 
> Why _you_ might want this function:  Geometric mean is
> a good replacement for arithmetic mean when you want
> to reduce the effects of extreme, infrequent values
> without excluding the occurrences altogether.  An
> example is Medicare's (US government health insurance
> for the elderly) use of geometric mean to derive
> standard lengths of stay (days in the hospital) for
> patients within Diagnostic Related  Groups (DRGs).
> For companies calculating statistics for internal
> process improvement projects, this function would be
> very useful because is describes what usually happens
> better than the arithmetic mean (see below).
> 
> Calculating geometric mean:
> Geometric mean is calculated by multiplying the values
> of each occurrence together and raising the resulting
> product by the power of 1 divided by the sample size.
> Therefore, the geometric mean of 2 3, 4, 5, and 40
> would be (2*3*4*5*40)^(1/5) = 5.448139855, which is
> very different from the arithmetic mean of 10.8.  I
> certainly would have liked my school grades to be
> calculated this way!
> 
> If anyone has created this function and would be
> willing to share it, I would greatly appreciate it.
> If the developers on this list see value in adding it
> as a standard function to MySQL, I think you will be
> ahead of the pack as industries are becoming more data
> savvy.
> 
> Is this a fairly simple request; or would it have been
> more appropriate to send this request to
> [EMAIL PROTECTED]?
> 
> Thanks,
> 
> Andrew Gould
> 
> __
> Do You Yahoo!?
> LAUNCH - Your Yahoo! Music Experience
> http://launch.yahoo.com
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail  [EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Filemaker Pro and Dates

[Gordon]

If you can load the data into MySQL, put the date field into a CHAR(10)
field. After the data load, add a date field using ALTER TABLE. You
should then be able to run something like

UPDATE TABLE tablename SET NewDate 
= CONCAT(
   RIGHT(OrigDate,4),"-",
   MID(OrigDate,4,2),"-",
   LEFT(OrigDate,2)
  );

You can then drop the original date column with alter table. If there
are more than 1 date fields just follow the same procedure for each one.
If there are several date fields in the converted database, you may want
to use an editor to build the multiple Alter/Update/Alter sequencies,
save as changedate.sql and then execute using 

MYSQL database name < changedate.sql

This lets you use copy paste to generate the multiple SQL statements
with minimal typing.

-

Another process which works if you have Microsoft Access is to 

Import the tables into an Access Database
Create the tables in MySQL
Link the MySQL tables into the Access database through an ODBC
DSN
Build an APPEND query to copy the Access tables into the MySQL
tables
Delete the Access database after verifying success

Moving the dates through the ODBC interface automatically converts the
DD-MM- format to the -MM-DD format used by MySQL.

Gordon Bruce
Interstate Software
US MySQL Training Partner

> -Original Message-
> From: Cameron Murdoch [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, May 29, 2002 5:46 PM
> To: [EMAIL PROTECTED]
> Subject: Filemaker Pro and Dates
> 
> Hi all,
> 
> I apologise if this question has been asked recently; I have checked
the
> list archives and could not see anything.  This is my problem:
> 
> I am trying to convert and import a fairly convoluted Filemaker Pro 5
> database to MySQL.  I have exported the filemaker records as tab
delimited
> files and have managed to load them in to MySQL without too much
problem.
> However I have one issue: Dates.  Of course MySQL stores dates in
-MM-
> DD
> whilst filemaker uses DD-MM- and so when I import my delimited
text
> file
> MySQL interprets the dates incorrectly.  I can't seem to find a way of
> changing the date format in Filemaker, nor can I figure out how to get
> MySQL
> to convert the dates into it's format.  Any help would be greatly
> appreciated and I apologise if this is something simple but this is my
> first
> entanglement with databases.
> 
> I am using MySQL 3.23.49 running on FreeBSD 4-STABLE.  MySQL was
compiled
> from the FreeBSD port.
> 
> Thanks for your help,
> 
> Cameron
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail  [EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Surrounding Rows

[Gordon]

If you know the value of the ZIP you want to "surround" lets call it
KZIP then try

SELECT code, abs(code - kzip) as Diff from zip order by Diff Limit n;

The '-' will make MySQL convert the zip codes into numeric values, ABS
is absolute value therefore making all of the results positive, order by
puts the results in order with the smallest differences first {i.e.
closest} and the limit gives you the first n where n is the number of
results you want.

This works for 9 digit ZIP's as well as 5 digit ZIP's as long as all of
your ZIP codes have the same # of digits or you can add 0's on the end
of your 5 digit ZIP's with RPAD().

Gordon Bruce
Interstate Software
A MySQL Training & Consulting Partner

> -Original Message-
> From: Jan Peuker [mailto:[EMAIL PROTECTED]]
> Sent: Monday, July 22, 2002 12:54 PM
> To: Gurhan Ozen
> Cc: [EMAIL PROTECTED]
> Subject: Re: Surrounding Rows
> 
> Hi,
> 
> sorry for my bad earlier explanation.
> What I want to do is, select the 10(say, a value) nearest values to a
> given
> number. The easiest way would be "near 5" shows 1..10. The problem is,
the
> number should affect rows, not values, because values are not unique
nor
> in
> order. What I want to do is a query like "SELECT code FROM zip WHERE
> zip=12345 SURROUND 5,5" or "SURROUND 10". At the moment, I create a
new
> table w/ an autoincrement and do a select...insert, then I select
first
> the
> id and then id-5 and id+5. This a a) very slow and redundant b) afaik
not
> very safe.
> Thanks a lot,
> 
> jan
> 
> 
> 
> - Original Message -
> From: "Gurhan Ozen" <[EMAIL PROTECTED]>
> To: "Jan Peuker" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
> Sent: Monday, July 22, 2002 7:39 PM
> Subject: Re: Surrounding Rows
> 
> 
> > Hi,
> > Can you please explain what is the algorithm here? Say you have zip
code
> > 12345, do you need all rows thats have zipcodes between 12340 and
12350,
> and
> > vice versa?
> > We can't help without knowing what do you mean by "surrounding" .
> >
> > Gurhan
> >
> >
> > - Original Message -
> > From: "Jan Peuker" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Monday, July 22, 2002 11:03 AM
> > Subject: Surrounding Rows
> >
> >
> > > Hi list,
> > >
> > > A nut to crack:
> > > I have a medium-sized database(about 1000 rows). The keys contain
> > > zip-address-information. Now I want to get the surrounding
persons,
> e.g.
> > > someone has zip 12345 I would get(now for 4 entries):
> > > +---+--+
> > > | 12340 | personA   |
> > > | 12345 | personB   | <-- I searched for this
> > > | 12345 | personC   |
> > > | 12347 | personD  |
> > > +---+--+
> > > As you see, it's no simple arithmetical question. At the moment I
fill
> an
> > > temptable w/ ids and select then - is there another way?
> > >
> > > regards,
> > >
> > > jan
> > >
> > > it would be nice if my sql could execute a query. thank you ezlm.
> > >
> > >
> > >
-
> > > Before posting, please check:
> > >http://www.mysql.com/manual.php   (the manual)
> > >http://lists.mysql.com/   (the list archive)
> > >
> > > To request this thread, e-mail
<[EMAIL PROTECTED]>
> > > To unsubscribe, e-mail
> > <[EMAIL PROTECTED]>
> > > Trouble unsubscribing? Try:
http://lists.mysql.com/php/unsubscribe.php
> > >
> >
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail  [EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Table Design

[Gordon]

First of all, changing action to INT(2) vs INT(11) takes the same space
{# inside ()'s is for display purposes not storage} on disk {I think
it's 4 bytes, but would have to look it up}. TINYINT or maybe even ENUM
{my preference} would only take 1 byte and would serve your purpose.

You could make details a 2nd table joined with the id field in earnings.
This complicates your code somewhat, but does save the space. 

The size issue isn't a # of records issue but more of a max file size on
the OS of your server. If your server OS only supports a max file size
of 2GB and you are using MyISAM then you will hit this limit way before
you have to worry about # of rows. There is a max # rows variable in
my.cnf, but it is usually set very large. 

You might want to consider using MERGE or UNION if you never delete any
records {See Manual}. This gets around the OS size issue and with
MyISAMPACK and MERGE could make queries against on the whole data set
run much faster.

Gordon Bruce
Interstate Software
A MySQL Training & Consulting Partner


> -Original Message-
> From: Daren [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, July 23, 2002 4:45 AM
> To: [EMAIL PROTECTED]
> Subject: Table Design
> 
> 
> 
> 
> First off, this post is going to be long :)
> 
> I have designed a database that tracks member earnings on the site.
> Currently, the site has been operating for two weeks, has 10,000
members,
> and the earnings table already has 750,000 rows. I ran a query to see
how
> many rows were being added each day, and it is growing exponentially.
> I.e., 5 days ago, 60,000 rows were added, yesterday, 100,000 rows were
> added.
> 
> Before I explain why the table is growing so quickly, some background:
> 
> Members are paid to read emails, click banners, etc. They are also
paid
> when one of their direct referrals (a member who signed up under their
> referral URL) reads an email or clicks a banner, or when their
indirect
> referrals (a member who signs up under one of their referrals'
referral
> URL) reads an email or clicks a banner. Therefore, each time an email
is
> read or a banner is clicked, 3 rows are added to the earnings table.
This
> table also keeps track of misc earnings, such as signup bonuses,
referral
> bonuses, etc. I designed it this way because if the site ever wants to
add
> on (say, pay their members to search the web), the table does not need
to
> be modified at all.
> 
> So, now it's obvious why the table is growing so rapidly. So far, the
size
> of the table is not a problem. The site is run on a dedicated server,
so
> physical file size will probably never be a problem (maybe it will?).
With
> 750,000 rows, the current file size is about 70mb. Also, since the
table
> is indexed properly, queries are running smoothly, and I also do not
think
> that should ever become a problem. However, data from this table will
> never be deleted, so it is just going to continue growing forever.
With
> 100,000 members to the site instead of 10,000, this table will grow by
> millions of rows each day.
> 
> My question: Can MySQL handle a table with tens or hundreds of
millions of
> rows?
> 
> Here is the design of the table:
> 
> CREATE TABLE earnings (
> id int(9) unsigned NOT NULL auto_increment,
> member_id int(8) unsigned NOT NULL default '0',
> action varchar(50) NOT NULL default '',
> details varchar(100) default NULL,
> amount decimal(7,3) NOT NULL default '0.000',
> time timestamp(14) NOT NULL,
> paid enum('N','Y') NOT NULL default 'N',
> PRIMARY KEY (id),
> KEY earnings_member_id(member_id)
> ) TYPE=MyISAM;
> 
> I have played around with some possible "reconstruction" of this table
to
> see the results.
> 
> 1) Removing the details column alltogether cuts the physical size of
the
> table to 70% of the original. Only about 5% of all the rows in this
table
> use the details field, but the 5% that do use it absolutely require
it. So
> this really isn't much of an option, but the 95% of rows that are not
> using this field are really wasting space. Is there some other way to
> accomplish this?
> 
> 2) Modifying the action field from varchar(35) to int(2). Since there
are
> only about 15 different actions (Banner Clicked, Banner Clicked by
Direct
> Referral, etc), I could create a new table with all the actions, and
then
> simply store the foreign key in the earnings table. This also cuts the
> physical file size down to 70% that of the original.
> 
> However, physical file size (at least I think) is not really the issue
> here, the issue is the number of rows in the table, and neither of the
> above solutions help with this. The only other solution I have come up
> with, is 

RE: rounding?!

[Gordon]

Try round(value*4,0)/4
Worked on the samples I tried

> -Original Message-
> From: Nicholas Stuart [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, July 23, 2002 1:47 PM
> To: [EMAIL PROTECTED]
> Subject: rounding?!
> 
> Ok here's the problem. Trying to write a select statement to be able
to
> round a set of numbers to the nearest quarter of an inch. Here is what
I
> have so far:
>
*---
--
> *
> SELECT
> CONCAT(ROUND(inside_length + (wall_thickness * 2), 0),'\'-',
> mod(((inside_length + (wall_thickness * 2)) * 12), 12),'\" x ',
> ROUND(inside_width + (wall_thickness * 2), 0),'\'-',
mod(((inside_width +
> (wall_thickness * 2)) * 12), 12),'\"' ) AS footprint,
> CONCAT(ROUND(inlet_invert - inlet_sump - base_slab_thck, 2), '\'') as
> outside_Bottom_Elevation,
> CONCAT(inlet_invert, '\'') as inlet_invert,
> CONCAT(outlet_invert, '\'') AS outlet_invert,
> CONCAT(TopofTank, '\'') AS TopOfTank,
> CONCAT(Rim, '\'') as Rim,
> CONCAT(ROUND(((inlet_Sump + base_slab_thck) - (((inlet_ko_dia -
inlet_dia)
> / 2) / 12)) * 12, 1), '\"') AS inlet_ko_outside,
> CONCAT(ROUND(((outlet_Sump + base_slab_thck) - (((outlet_ko_dia -
> outlet_dia) / 2) / 12)) * 12, 1), '\"') AS outlet_ko_outside,
> CONCAT(ROUND((inlet_Sump + base_slab_thck) * 12, 1), '\"') as
> inlet_invert_outside,
> CONCAT(ROUND((outlet_Sump + base_slab_thck) * 12, 1), '\"') as
> outlet_invert_outside,
> CONCAT(ROUND(inlet_Sump * 12, 1), '\" ') AS inlet_Sump,
> CONCAT(ROUND(outlet_Sump * 12, 1), '\"') AS outlet_Sump,
> CONCAT(ROUND(inlet_Sump * 12 + (inlet_dia / 2), 1), '\"') AS
> inlet_cl_floor,
> CONCAT(ROUND(outlet_Sump * 12 + (outlet_dia / 2), 1), '\"') AS
> outlet_cl_floor,
> CONCAT(inlet_dia, '\"') AS inlet_dia,
> CONCAT(outlet_dia, '\"') AS outlet_dia,
> CONCAT(inlet_ko_dia, '\"') AS inlet_ko_dim,
> CONCAT(outlet_ko_dia, '\"') AS outlet_ko_dim,
> CONCAT(ROUND((inlet_ko_dia - inlet_dia) / 2, 1), '\"') AS
> inlet_invert_ko_invert,
> CONCAT(ROUND((outlet_ko_dia - outlet_dia) / 2, 1), '\"') AS
> outlet_invert_ko_invert,
> CONCAT(inlet_offset, '\"') AS inlet_offset,
> CONCAT(outlet_offset, '\"') AS outlet_offset
> FROM tbl_prj_dims
>
*---
--
> *
> 
> This will get all of the values I need in decimal inches but I would
like
> to be able to round them up or down to the nearest quarter of an inch,
so
> the only decimal values will be .0 or .25 or .5 or .75
> I'm not really sure if there is efficent way to do it and would like
all
> of your inputs.
> And dont mind the first few values in feet. Those need to be like
that.
> 
> Thanks a lot!
> -Nick Stuart
> 
> Filter Fodder: MySQL QUERY
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail  [EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: removing duplicate records

[Gordon]

If you use IGNORE in the insert IGNORE into new_table you will get the
result you want.

> -Original Message-
> From: walt [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, July 30, 2002 3:11 PM
> To: David Kramer; [EMAIL PROTECTED]
> Subject: Re: removing duplicate records
> 
> Thanks David!
> 
> The entire row, not just one or two columns, is a duplicate which
makes
> life
> fun..
> :-)
> 
> I can key or unique index only a few columns once the data is cleaned
up
> to
> prevent this problem.
> 
> If I create an identical table and include either a key or unique
index
> (innodb seems to like the index better) on all the columns and do a
> 
> insert into new_table using select * from old_table
> 
> will mysql quit inserting once a duplicate is hit, or will it keep
going
> and
> skip over the duplicates?
> 
> walt
> 
> On Tuesday 30 July 2002 03:57 pm, David Kramer wrote:
> > You could always use an insert statement into a second table, when
> > performing the insert use a GROUP BY clause to consalidate your
records
> >
> > something along these lines but this isnt 100% accurate, I would
need
> the
> > table DDL and business rules behind the DEDUP
> >
> > Insert into tableB
> > (
> > column names, ...
> >
> >
> > )
> > (select
> > column a,
> > column b,
> > max(column c), --or you could use MIN
> > from
> > table A
> >
> > group by
> > column a,
> > column b);
> >
> >
> >
> >
> >
> > **JUST make sure your Identifing column, i.e. the column you use to
tell
> > which is a duplicate record or not is included in the group by.
Also
> what
> > are the business rules behind the DEDUP(Deduplication)? Are the
other
> > values contained in the other columns necessary?  If you tell me
more
> about
> > what your trying to do and provide some Table DDL I can help you
write
> this
> > query.  Just let me know!
> >
> > Thanks,
> >
> > DK
> >
> >   group by statement
> >
> > -Original Message-
> > From: walt [mailto:[EMAIL PROTECTED]]
> > Sent: Tuesday, July 30, 2002 12:43 PM
> > To: [EMAIL PROTECTED]
> > Subject: removing duplicate records
> >
> >
> > Does anyone know a good way find and remove duplicate records from a
> table?
> > I can create an identical table structure and use a script to pull
> records
> > from the existing table and insert them into the new table if they
are
> not
> > duplicates, but I'd rather not do it that way. Here is an example of
an
> sql
> > script  I use for Oracle databases
> >
> > delete   from employee a
> > whererowid < (
> > select  max(rowid)
> > fromemployee b
> > where   b.COL1 = a.COL1
> > and b.COL2 = a.COL2
> > and b.COL# = a.COL#);
> >
> > sql, query
> >
> > Thanks in advance!
> 
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail  [EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: A difficutl query I cannot do.

[Gordon]

The following SELECT should produce your table

mysql> Select id,
->Max(CASE TimeSlot
->  WHEN 1 THEN Subject
->ELSE ""
->END) AS TS1,
->Max(CASE TimeSlot
->  WHEN 2 THEN Subject
->ELSE ""
->END) AS TS2,
->Max(CASE TimeSlot
->  WHEN 3 THEN Subject
->ELSE ""
->END) AS TS3,
->Max(CASE TimeSlot
->  WHEN 4 THEN Subject
->ELSE ""
->END) AS TS4
-> From sch
-> Group by id
-> ;
+-+--++--++
| id  | TS1  | TS2| TS3  | TS4|
+-+--++--++
| 215 | Science1 | Maths1 | Eng1 ||
| 648 |  | Maths2 |  | Art|
| 901 | Science2 || Eng1 | French |
+-+--++--++
3 rows in set (0.00 sec)


-Original Message-
From: Sam Russo [mailto:[EMAIL PROTECTED]] 
Sent: Wednesday, January 09, 2002 5:25 PM
To: [EMAIL PROTECTED]
Subject: A difficutl query I cannot do.

I have a delimited file sent to me with students ID subjects and when
they
do these subjects. This file (table) looks like:
 ID TimeSlotSubject
 
215 3   Eng1 
648 2   Maths2
901 4   French
215 2   Maths1
901 1   Science2
648 4   Art
215 1   Science1
901 3   Eng1

I need to produce the following output (on a web page using php and 
mysql) with a mysql query.

ID  1   2   3   4
215 Science1Maths1  Eng1
648 Maths2  Art
901 Science2Eng1
French

any help would be appreciated.
sam russo
[EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Converting Access 2000 to Mysql

[Gordon]

There is a program at http://www.convert-in.com/acc2sqlp.htm which will
create the MySQL tables from the Access 2000 database and populates
created tables directly from the Access 2000 source. It also converts
date/datetime fields into MySQL format and Yes/No fields into tinyint.
The best news is that it takes about 1-2 minutes to complete the process
{not counting time to load the data}.

One caution, if you have Access queries that reference other queries you
will have to rework the design until MySQL supports VIEWS {currently
targeted for 4.1 according to the manual}.

> -Original Message-
> From: Gary Portellas [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, January 22, 2002 11:46 AM
> To: [EMAIL PROTECTED]
> Subject: Converting Access 2000 to Mysql
> 
> 
> Hi All
> 
> I am new to Mysql / Linux and would like to import my access 
> 2000 database 
> into a MYSQL database - is there an easy way to do this ??
> 
> Thanks
> Gary
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Using LIMIT to select random rows

[Gordon]

This is an example from MySQL's class on Using MySQL

MySQL> Select Name
-> From Country
-> Where Continent="Europe"
-> Order By RAND()
-> LIMIT 1;

If you do Limit 3 you should get 3 random rows.

-Original Message-
From: Ulf Harnhammar [mailto:[EMAIL PROTECTED]] 
Sent: Wednesday, January 23, 2002 10:09 AM
To: [EMAIL PROTECTED]
Subject: Using LIMIT to select random rows

This is a suggestion for a small addition to MySQL's syntax which I'm
proposing, unless someone is kind enough to show me that there already
is
equivalent functionality somewhere else in MySQL.

See, what I want to do is select a few random rows among those that
normally would be returned. I don't want to select all matching ID's in
one select, retrieve them to my script, and then construct another
SELECT,
because this seems to be slow. I don't want to use lots of "SELECT
something FROM sometable LIMIT number,1" statements either, because this
seems to be slow too.

I propose this additional syntax to Monty and the guys:
SELECT something FROM sometable WHERE somevar=somevalue
LIMIT number1,1,number2,1,number3,1,..
which should return the rows numbered "number1", "number2", "number3"
etc
from the rows that normally would be returned. This way, I could first
use
a "SELECT COUNT(*) FROM sometable WHERE somevar=somevalue" statement,
and
then issue another statement of the kind that I propose, to get a few
random rows.

Perhaps there already is some way to do this efficiently. In that case,
please enlighten me.

Regards, Ulf Härnhammar

PS: Talking about Monty, is he by any chance the same Monty who was in a
team called Monty & Kaj and wrote a PacMan clone called Blipp for the
ABC80 computer decades ago? Man, that game was cool! ;)


Ulf Härnhammar
System Developer

ST-Registry
St Eriksgatan 117, E2
SE-113 43 Stockholm
SWEDEN (GMT+1)

Telephone:  +46 (0)8-545 476 04
Facsimile:  +46 (0)8-32 63 33

E-mail: [EMAIL PROTECTED]
Web: http://www.nic.st/

The STreet domain - your Internet address


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

MySQL GRANTS

[Gordon]

1. IN 4.0.3 I CREATE 3 USERS using 3 different clients and get 
   slightly different results when I then run 
SHOW GRANTS FOR XXX;

The user created by MySQL Front has a slightly different set of
privileges even though the GRANT displayed by MySQL Front appears to be
the same as the one used for the mysql client and MyCC. 

I don't think SHOW  DATABASES,  CREATE TEMPORARYTABLES
and LOCK TABLES are the same as USAGE.

Any ideas?

2. Is the script to update the privileges in 4.0.2/4.0.3 included in the
Windows distribution? I don't seem to find it in 

Gordon Bruce

Oh, if you GRANT ALL ON *.* instead of .* with MySQL Front
you don't get the 
SHOWDATABASES,  CREATE TEMPORARYTABLES and LOCK TABLES
privileges.


___
GRANT   ALL PRIVILEGES  ON  test.*  TO  'Front'@'%'
IDENTIFIED  BY  'front' 

mysql>  showgrants  for Front;

+---
-+

|   Grants  for Front@% |

+---
-+

|   GRANT   SHOWDATABASES,  CREATE  TEMPORARY   TABLES,
LOCKTABLES  ON  *.* TO  'Front'@'%' IDENTIFIED
BY  PASSWORD'671fb08f73d32f5a'  |
|   GRANT   ALL PRIVILEGES  ON  `test`.*TO
'Front'@'%' |

+---
-+

2   rowsin  set (0.00   sec)



mysql>  grant   all on  test.*  to  cmd@'%' identified
by  'cmd';  
Query   OK, 0   rowsaffected(0.13   sec)


mysql>  showgrants  for cmd;

+---
+

|   Grants  for cmd@%   |

+---
+

|   GRANT   USAGE   ON  *.* TO  'cmd'@'%'
IDENTIFIED  BY  PASSWORD'7b49d2fa2263dafa'  |

|   GRANT   ALL PRIVILEGES  ON  `test`.*TO
'cmd'@'%'   |

+---
+




mysql>  showgrants  for MyCC;

+---
-+

|   Grants  for MyCC@%  |

+---
-+

|   GRANT   USAGE   ON  *.* TO  'MyCC'@'%'
IDENTIFIED  BY  PASSWORD'6caf0b02004e14af'  |
|   GRANT   ALL PRIVILEGES  ON  `test`.*TO
'MyCC'@'%'  |   
+---
-+

2   rowsin  set (0.00   sec)



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Problem with select.

[Gordon]

This is what happens when you use LIMIT

MySQL retrieves the entire record set for the select and then sends the
number requested in the LIMIT parameter to the client. MySQL has to
retrieve the entire result set to accommodate a LIMIT clause which skips
n records i.e. LIMIT 250,10.

With no ORDER BY or WHERE clause all records in the table are retrieved
in the order they were originally loaded {at least true for MyISAM table
type}. If the primary key is an autoincrement field then this will be in
primary key sequence, but this is coincedental. If the primary key is
not auto increment and the records are not loaded in primary key
sequence then the result set will not be in primary key order.

So if you want to use an index use WHERE and if you want a specific
sequence use ORDER BY. 



> -Original Message-
> From: Andrey Hristov [mailto:andrey@;webgate.bg] 
> Sent: Wednesday, October 23, 2002 9:47 AM
> To: [EMAIL PROTECTED]
> Subject: Problem with select.
> 
> 
> HI,
> I don't know is it bad or not but I face queries the work 
> quite long time.
> 
> the sql is :
>  select * from log_answers_index limit 0,10
> 
> and it takes too much time for me to receive the result.
> I got indexes on most of the fields, i got primary key which 
> is auto_increment int(11). When I did explain on the query I 
> got this : type : ALL rows : 123010 all other cells are empty.
> 
> When I do :
> select * from log_answers_index where log_entry_index between 
> 1 and 10 i got type : range
> 
> For me it is strange that the primary key is not used when 
> there is LIMIT clause without WHERE one.
> 
> Best regards
> Andrey Hristov
> 
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: update and data manipulation

[Gordon]

Use the concat function. It takes any number of arguments.

mysql> create table test (a char(5));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into test values ('A');
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+--+
| a|
+--+
| A|
+--+
1 row in set (0.02 sec)

mysql> update test set a = concat(a,"A");
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+--+
| a|
+--+
| AA   |
+--+
1 row in set (0.00 sec)

> -Original Message-
> From: Michael Brunson [mailto:brunson@;intercosmos.com] 
> Sent: Wednesday, October 16, 2002 11:30 PM
> To: [EMAIL PROTECTED]
> Subject: Re: update and data manipulation
> 
> 
> On Thu, 17 Oct 2002 03:50:47 +0100, Nikolas Galanis
> used a few recycled electrons to form:
> 
> | Hello
> | 
> | When I want to update a value, I can use numerical 
> functions like +,*
> | etc. Can I do the same with strings? For example, when I 
> want to append 
> | a string in an existing value, how (if of course) can I do 
> it inside the 
> | update query? Thanks.
> 
> Yes 
> 
> | Nikolas
> | 
> | p.s: I know I can do it with PHP, I was just wondering if it is 
> | possible
> | with SQL.
> 
> Yes
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Intersting MySQL / Access Issue

[Gordon Bruce]

When I have seen this error it was caused by a field defined in the
MySQL database as NOT NULL in the Create table and the value in Access
is NULL or usually for us an empty field in EXCEL which is appears to be
intreped as NULL when you do a PASTE APPEND. 

Ours is often times a datetime field but I don't think its limited to
data time fields defined as NOT NULL. I have not really found an easy
wasy to find the offending field except by process of elimination.

Hope this helps.



-Original Message-
From: Edward Maas [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 21, 2005 10:31 PM
To: mysql@lists.mysql.com
Subject: Intersting MySQL / Access Issue

Dear Community,

My team and I have been experiencing an interesting mysql error 
during the past few weeks of testing.  Here is the scenario we are 
trying to accomplish.  We are essentially working to use MsAccess as a 
windows client for a linux based mysql databases.  We have installed 
myODBC 3.51 and are using that for communication.  We seem to be able to

create a linked table just find and select queries work great.

The problem arises when we try to update or insert data.  Updates 
yield the following error:

"You Tried to assign the Null value to a variable that is not a 
Variant data type." 

 From my searching, I was unable to find how to set fields to variant 
data types.  Secondly, I am not sure which field is causing the error.  
The second issue was with inserts.  On insert of just one field (none 
are required other than the primary key), ALL of the fields of type text

are set to NULL.  This is particularly odd and occurs also in the mysql 
command line utilty.

If anyone has any ideas or experience, please send emails to 
[EMAIL PROTECTED]  We will definitely summarize the solution for the 
educaitonal purposes of the list.  Again many thanks in advance.

Sincerely,,
Ed Maas


-- 
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]

RE: question about field length for integer

[Gordon Bruce]

If you really need more than 20 digits of accuracy and can move to 5.0.3
+ 
you can use the Decimal data type without losing precision.

This is out of Chapter 23. Precision Math 

The maximum value of 64 for M means that calculations on DECIMAL values
are accurate up to 64 digits. This limit of 64 digits of precision also
applies to exact-value numeric literals, so the maximum range of such
literals is different from before. (Prior to MySQL 5.0.3, decimal values
could have up to 254 digits. However, calculations were done using
floating-point and thus were approximate, not exact.) This change in the
range of literal values is another possible source of incompatibility
for older applications. 

Values for DECIMAL columns no longer are represented as strings that
require one byte per digit or sign character. Instead, a binary format
is used that packs nine decimal digits into four bytes. This change to
DECIMAL storage format changes the storage requirements as well. Storage
for the integer and fractional parts of each value are determined
separately. Each multiple of nine digits requires four bytes, and the
"leftover" digits require some fraction of four bytes. For example, a
DECIMAL(18,9) column has nine digits on each side of the decimal point,
so the integer part and the fractional part each require four bytes. A
DECIMAL(20,10) column has 10 digits on each side of the decimal point.
Each part requires four bytes for nine of the digits, and one byte for
the remaining digit. 


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Monday, June 27, 2005 10:34 AM
To: Eko Budiharto
Cc: mysql@lists.mysql.com
Subject: Re: question about field length for integer

Eko Budiharto <[EMAIL PROTECTED]> wrote on 06/26/2005 11:02:30 AM:

> Hi,
> is there anyway that I can have more than 20 digits for integer 
> (bigInt)? If not, what I can use for database index?

BIGINT UNSIGNED can range from 0 to 18446744073709551615
(http://dev.mysql.com/doc/mysql/en/numeric-types.html)

Are you actually saying that you have a database with more than 1.8e+19 
records in it? I don't think you do. I think you are combining several 
pieces of information into something that looks like a number and it's 
exceeding the storage limits of even BIGINT.

What you have is actually a "good idea" but you are physically limited
by 
the capacity of the column types available. In this case if you cannot 
create all of your key values so that they look like numbers smaller
than 
18446744073709551615, it can't fit into a BIGINT UNSIGNED column.

You do have some options:
a) change the way you create your server keys so that they fit in the 
value allowed
b) use a character-based column to store your server key values
c) use some other value to identify your servers (IP address, for
example)
d) create a table of server keys:

CREATE TABLE server (
ID int auto_increment
, name varchar(25) not null
, ip int unsigned
, ... (any other fields you could define to describe this
server)
, PRIMARY KEY (ID)
, UNIQUE(name)
)

Then, refer to your servers using server.id instead of your composited 
key.

e) ...? (I am sure there are more ideas from others on the list)

To answer your literal question: No, MySQL cannot store integer values 
that contain more than 20 digits. Sorry!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Count(*)

[Gordon Bruce]

select count(distinct ordr_ID) from store 

-Original Message-
From: Gana [mailto:[EMAIL PROTECTED] 
Sent: Thursday, July 07, 2005 3:01 PM
To: mysql@lists.mysql.com
Subject: Count(*)

select count(*) from store group by orederId.

For the above sql, I am not getting the count of unique order ids..

help!!

-- 
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]

RE: Impossible join?

[Gordon Bruce]

Here is one way. 

Some time ago I set up a table named count with one field named count
and built 5000 rows of incrementing values . 


I think I originally populated it by originally createing it with a 2nd
field 

CREATE TABLE `count` (
  `count` int(10) unsigned NOT NULL auto_increment, 
  `addr_ID` int(10) NULL,  this field does not really
matter 
  PRIMARY KEY  (`count`)
) ENGINE=InnoDB DEFAULT CHARSET=;

and then doing something like 

INSERT INTO count(addr_ID) 
SELECT  addr_ID <<< any primary key out of any table with > 5000
entries
FROM   addresses
LIMIT 5000;

and then droping the 2nd field.

Just put an INSERT in front of the select and set the value = to the #
of dates you want to populate and the set value to 1 day pior to where
you want to start. 

mysql> set @d:='2004-12-31 00:00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select  @d:[EMAIL PROTECTED] + interval 1 day as date from count where 
count <=
10;
+-+
| date|
+-+
| 2005-01-01 00:00:00 |
| 2005-01-02 00:00:00 |
| 2005-01-03 00:00:00 |
| 2005-01-04 00:00:00 |
| 2005-01-05 00:00:00 |
| 2005-01-06 00:00:00 |
| 2005-01-07 00:00:00 |
| 2005-01-08 00:00:00 |
| 2005-01-09 00:00:00 |
| 2005-01-10 00:00:00 |
+-+
10 rows in set (0.00 sec)
-Original Message-
From: Jonathan Mangin [mailto:[EMAIL PROTECTED] 
Sent: Monday, July 18, 2005 8:03 AM
To: Michael Stassen
Cc: mysql@lists.mysql.com
Subject: Re: Impossible join?


> Jonathan Mangin wrote:
>
>> Hello all,
>>
>> I'm storing data from a series of tests throughout each
>> 24-hour period.  I thought to create a table for each test.
>> (There are six tests, lots more cols per test, and many
>> users performing each test.)
>
> But each test is performed no more than once per day by a given user?

Correct.
>
>> select test1.date, test1.time, test2.date, test2.time from
>> test1 left join test2 on test2.date=test1.date where
>> test1.date between '2005-07-01' and '2005-07-16' and
>> uid='me';
>
> Something is strange here.  Doesn't uid exist in both tables?  I'll
assume 
> it does.

Oops. Also correct.
>
>> ++--++--+
>> | date   | time | date   | time |
>> ++--++--+
>> | 2005-07-13 | 6:30 | 2005-07-13 | 7:30 |
>> | 2005-07-14 | 6:32 | 2005-07-14 | 7:45 |
>> | 2005-07-15 | 6:30 | 2005-07-15 | 7:42 |
>> | 2005-07-16 | 6:35 | NULL   | NULL |
>> ++--++--+
>>
>> Is there a join, or some other technique, that would
>> return (nearly) these same results if test1 (or any test)
>> has not been performed?  Using 4.1.11.
>>
>> TIA,
>> Jon
>

[ SNIP! ]


> A better solution would be to add a table:
>
>   CREATE TABLE `testdates` (`date` date default NULL,
>  UNIQUE KEY `date_idx` (`date`)
>);
>
> Insert one row into testdates for each day.  Now you can use something

> like this:
>
>   SELECT testdates.date, test1.time AS 'Test 1', test2.time AS 'Test
2'
>   FROM testdates
>   LEFT JOIN test1 on testdates.date = test1.date AND test1.uid = 'me'
>   LEFT JOIN test2 on testdates.date = test2.date AND test2.uid = 'me'
>   WHERE testdates.date BETWEEN '2005-07-11' AND '2005-07-16';
>
> ++--+--+
> | date   | Test 1   | Test 2   |
> ++--+--+
> | 2005-07-11 | NULL | NULL |
> | 2005-07-12 | NULL | 07:28:00 |
> | 2005-07-13 | 06:30:00 | 07:30:00 |
> | 2005-07-14 | 06:32:00 | 07:45:00 |
> | 2005-07-15 | 06:30:00 | 07:42:00 |
> | 2005-07-16 | 06:35:00 | NULL |
> ++--+--+
> 6 rows in set (0.01 sec)
>
> Much better, don't you think?  This generalizes pretty well, too.
>
>   SELECT testdates.date,
>  test1.time AS 'Test 1',
>  test2.time AS 'Test 2',
>  test3.time AS 'Test 3',
>  test4.time AS 'Test 4'
>   FROM testdates
>   LEFT JOIN test1 on testdates.date = test1.date AND test1.uid = 'me'
>   LEFT JOIN test2 on testdates.date = test2.date AND test2.uid = 'me'
>   LEFT JOIN test3 on testdates.date = test3.date AND test3.uid = 'me'
>   LEFT JOIN test4 on testdates.date = test4.date AND test4.uid = 'me'
>   WHERE testdates.date BETWEEN '2005-07-11' AND '2005-07-16';
>
> ++--+--+--+--+
> | date   | Test 1   | Test 2   | Test 3   | Test 4   |
> ++--+--+--+--+
> | 2005-07-11 | NULL | NULL | NULL | 08:12:00 |
> | 2005-07-12 | NULL | 07:28:00 | 07:14:00 | 08:14:00 |
> | 2005-07-13 | 06:30:00 | 07:30:00 | 07:16:00 | 08:29:00 |
> | 2005-07-14 | 06:32:00 | 07:45:00 | 07:14:00 | 08:26:00 |
> | 2005-07-15 | 06:30:00 | 07:42:00 | 07:19:00 | NULL |
> | 2005-07-16 | 06:35:00 | NULL | NULL | NULL |
> ++--+--+--+--+
> 6 rows in set (0.00 sec)
>
> Michael
>
I'm guessing thi

RE: Renaming a database

[Gordon Bruce]

A database in MySQL is simply a directory. 

 

So just rename the directory with appropriate tool for your platform. 

On my test box this becomes

 

mysql> show databases;

++

| Database  
|

++

| information_schema |

|
lois  
|

| mailprint  |

|
mysql 
|

|
test  
|

++

5 rows in set (0.22 sec)

 



 

mysql> show databases;

++

| Database  
|

++

| information_schema |

|
lois  
|

| mailprint  |

|
mysql 
|

|
test1 
|

++

5 rows in set (0.00 sec)

 

-Original Message-
From: Scott Hamm [mailto:[EMAIL PROTECTED] 
Sent: Monday, July 18, 2005 11:02 AM
To: 'Mysql '
Subject: Renaming a database

 

How do I rename a database? The help online is pretty ambigous.

 

-- 

Power to people, Linux is here.

 

RE: advanced group by

[Gordon Bruce]

Something like this 

SELECT CompanyName, 
   WhatToShip, 
   SUM(IF(TrackingNumber = '', 
  IF(SerialNumber = '', 
 1,
 0),
  0)
   ) AS READY, 
   SUM(IF(TrackingNumber <> '', 
  IF(SerialNumber = '', 
 1,
 0),
  0)
   ) AS Almost, 
   SUM(IF(TrackingNumber <> '', 
  IF(SerialNumber <> '', 
 1,
 0),
  0)
   ) AS Done 
FROM   shipments 
GROUP BY 1,2

-Original Message-
From: James M. Gonzalez [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 03, 2005 5:59 AM
To: mysql@lists.mysql.com
Subject: advanced group by 

Hello list! little GROUP BY problem here:

 

Table 'shipments'

 

ID  int(10)

CompanyName  char(50)

WhatToShip  char(50)

TrackingNumber  char(50)

SerialNumber  char(50)

 

I would like to obtain the following results:

 

CompanyName - WhatToShip   -  Ready - Almost - Done

 Foo-  car   - 26   -  2-
23 

 Foo-elephant  - 43  -  0-   15

 Foo-acuarium - 12  -  6-   47

 Bar- mobile- 9-  0-
52

 Bar- fan - 15  -  4-
43

 

 

Ready: items with empty TrackingNumber and empty SerialNumber

Almost: items with popullated TrackingNumber AND empty SerialNumber

Done: items with popullated TrackingNumber and popullated SerialNumber

 

I have been reading around and trying lots of things. I believe the
answer lies on how to group by an empty field. This means, I believe I
can make this work if I find a way to group by a field's emptiness or
not, instead of the actual content. 

 

Google is tired of seeinf me search around for ' advanced "grouping by"
' and so on and on. 

 

Any help will be greatly apprecieted. (Im begging for help)

 

James




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Number of SQL Queries curiosity

[Gordon Bruce]

We have a relatively small web site running 4.0.20 on a Dell 2850
running RedHat. 

We rarely see any SQL statements visible when we run SHOW PROCESSLIST
and typically use  30 - 40 concurrent connections.

 

The Number of SQL Queries graph in MySQL Administrator usually is in the
0 to 10 range with occaisional spikes to 40 - 50.

 

Lately I have been noticing the Max value in the Number of SQL Queries
setting at 2,000 +. This happens maybe once or twice a day and I have
only been looking at the display when it happened 1 time. There does not
seem to be any unusal difference in the rest of the graphs, page hits on
the web site, network traffic etc. Nothing seems to be impacted when
this activity occurs. I just don't understand what could cause this kind
of activity on the server with our web site usage profile. We do have
some people using access through ODBC, but I have not been able to
recreate the event.

 

Does anyone have any ideas on what could cause this?

Shouild I be concerned?

RE: top one row

[Gordon Bruce]

This is out of the MySQL class and is called the Max-Concat trick. 


What is the biggest country in each continent?
"The Rows Holding the Group-wise Maximum of a Certain Field"

MySQL> Select Continent,
->  SUBSTRING(MAX(CONCAT(LPAD(Population,10,'0'),Name)),11) AS
Country,
-> 0+LEFT(MAX(CONCAT(LPAD(Population,10,'0'),Name)),10) AS
Population
-> From Country
-> Group By Continent
-> Order By Population DESC;
+---+--+
+
| Continent | Country  |
Population |
+---+--+
+
| Asia  | China|
1277558000 |
| North America | United States|
278357000 |
| South America | Brazil   |
170115000 |
| Europe| Russian Federation   |
146934000 |
| Africa| Nigeria  |
111506000 |
| Oceania   | Australia|
18886000 |
| Antarctica| South Georgia and the South Sandwich Islands |
0 |
+---+--+



It looks ugly but what you have to do is tie the data you want together
and let the max work on the collection and then split it back out again
in the display. 

So in your case 

SELECT col1, 
   LEFT(MAX(CONCAT(LPAD(col2,10,'0'),col3)),10) + 0 AS col2, 
   MID((MAX(CONCAT(LPAD(col2,10,'0'),col3)),11,50) + 0 As col3
FROM sample 
GROUP BY col1


-Original Message-
From: Kemin Zhou [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 05, 2005 12:38 PM
To: mysql@lists.mysql.com
Subject: top one row

I have a simple table

col1  col2col3
A  2  3
A  100  70
A  100080
B20   90
B7080


To select the top one row for each unique value of col1

select distinct on (col1), col1, col2, col3
from table
order by col1, col2 desc, col3 desc;

What I want is
A  1000 80
B   70 80

How do you do it in mysql?

Kemin



-- 
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]

RE: JOIN QUERY -> UPDATE ... help?!

[Gordon Bruce]

Multi Table UPDATES are first supported in 4.0.x

-Original Message-
From: Brendan Gogarty [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 09, 2005 12:16 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: RE: JOIN QUERY -> UPDATE ... help?!


"Brendan Gogarty" <[EMAIL PROTECTED]> wrote on 08/09/2005
05:30:51 AM:

> Hi,
> We are running mysql  3.23.58 and I want to do a query with joins
> from two tables and then insert the
> results into the column of a third. This appears to be harder than I
> realised with this version of mysql and I am banging my head against a
> wall. Please Help!
> ok first query.
 [snip]
> any ideas?
> 
> 

Start from here: 
http://dev.mysql.com/doc/mysql/en/update.html 

Updates *are* allowed to use JOINED tables as the "thing to be updated".
Which means that an UPDATE statement can look VERY MUCH like a SELECT
statement turned on it's head. In your case, I think you are trying to
figure out how to flip this: 

select link_ID,of_group
FROM
page_elements
LEFT JOIN pages
   ON page_elements.link_to=pages.page_ID
LEFT JOIN links_DB_bk
   ON page_elements.link_ID=links_DB_bk.link_ID
WHERE content_type='text' 

into this (while adding the `links_db` table into the mix: 

UPDATE links_db 
INNER JOIN page_elements 
ON page_elements.link_ID=links_DB.link_ID
LEFT JOIN pages
   ON page_elements.link_to=pages.page_ID
LEFT JOIN links_DB_bk
   ON page_elements.link_ID=links_DB_bk.link_ID 
SET <*** see note***> 
WHERE content_type='text'; 


*** note:  your SET clause can reference ANY column from ANY table
defined in your UPDATE clause. You are not limited to just changing one
table at a time. Just make sure you properly identify the columns you
want to get data from and which ones you want to set. Now, you didn't
say exactly what you wanted to update with what or I would have filled
in more of the SET clause. 

If you want to flip a SELECT ... GROUP BY statement into an UPDATE
statement, you have to go through a temporary table first. That is
because the GROUP BY eliminates any one-to-one row-to-value mappings
99.9% of the time. There is no UPDATE ... GROUP BY  command for any
RDBMS that I know of. However, if you save the results of the
SELECT...GROUP BY statement, you can JOIN to that table in an UPDATE
statement just like any other data. 

Let me know if you run into any more issues and I can help you work it
out. 

 
Hi Shaun,
 
I'm afraid after a few hours of testing various things it doesn't work.
I am pretty sure its a version issue as even the simplest query such as 
UPDATE links_DB
LEFT JOIN
page_elements
SET links_DB.in_group=0
 
brings up an error
' 

MySQL said: 


You have an error in your SQL syntax near 'LEFT  JOIN page_elements SET
links_DB.in_group = 0' at line 1
'
cheers,
brendan.
 
 




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Index - max key length is 1024 bytes

[Gordon Bruce]

If you are on a version prior to 4.1.2 the max index size is 500 bytes 
{not sure why the error mentions 1024}

>From section 14.1 of documention

The maximum key length is 1000 bytes (500 before MySQL 4.1.2). This can
be changed by recompiling. For the case of a key longer than 250 bytes,
a larger key block size than the default of 1024 bytes is used.

-Original Message-
From: javabuddy [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 12, 2005 12:30 PM
To: mysql@lists.mysql.com
Subject: Index - max key length is 1024 bytes

I am trying to create an index with multiple fields. 

The sixe of each of the column is listed and thier sum is 560bytes. But
when I try to create an index with the colums, it complains on the size
exceeded 1024 bytes. Below is the query and the size of each..

create index selectTechnologyClubsThread_idx on content
(club_id, date_update, subject, message_id, id, date_published,
comment_count_d, display_usr_name_d, short_content)

COLUMN_NAME TYPESIZE
club_id Bigint  8
date_update Bigint  8
Subject Varchar(120)121
message_id  varchar(120)121
Id  Bigint  8
date_published  Datetime8
comment_count_d Int 4
short_content   Varchar(250)251
display_usr_name_d  Varchar(30) 31

TOTAL : 560

Any sort of help would be great

- javabuddy




People are conversing... without posting their email or filling up their
mail box. ~~1123867827435~~
roomity.com http://roomity.com/launch.jsp No sign up to read or search
this Rich Internet App




-- 
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]

RE: query

[Gordon Bruce]

You can use INTERVAL i.e.

Lets say you have a table 

mysql> CREATE TABLE foo (bar int(14), fdate date );
Query OK, 0 rows affected (0.27 sec)

mysql> INSERT INTO foo 
 > VALUES (1, now()),
  (25,now() - INTERVAL 1 DAY),
  (15,now() - INTERVAL 2 DAY);

mysql> SELECT f1.bar - f2.bar AS diff , f2.fdate
-> FROM   foo AS f1
->INNER JOIN foo AS f2
->ON (f1.fdate = f2.fdate + INTERVAL 1 DAY);
+--++
| diff | fdate  |
+--++
|  -24 | 2005-08-15 |
|   10 | 2005-08-14 |
+--++
2 rows in set (0.00 sec)

-Original Message-
From: Octavian Rasnita [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 16, 2005 7:51 AM
To: mysql@lists.mysql.com; Felix Geerinckx
Subject: Re: query

Hi,

I define the previous record by date. For each record corresponds a date
which is unique and the previous record is that that contains the
yesterday
date.

Thank you.

Teddy

- Original Message - 
From: "Felix Geerinckx" <[EMAIL PROTECTED]>
To: 
Sent: Tuesday, August 16, 2005 2:45 PM
Subject: Re: query


> On 16/08/2005, "Octavian Rasnita" wrote:
>
> > I want to create a query that selects the diference between the
value
> > of a field from the current record and the value of the same field
> > from the previous record.
>
> How do you define "current record" and "previous record"?
> (relational databases are not spreadsheets)
>
> -- 
> felix
>
> -- 
> 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]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: SELECT ... INTO OUTFILE ... and LOAD DATA INFILE ... with a key

[Gordon Bruce]

If you want to have all values except the primary key be the same and
say your is foo_ID
You can simply do 
INSERT INTO foo 
  (foo_ID... {rest of columns list}) 
SELECT new primary key value,
   {rest of columns list}
FROM   foo 
WHERE  foo_ID = {primary key value of row you want to copy}

If your PRIMARY KEY is an auto_increment field, just omit foo_ID from
the columns list in both the INSERT and SELECT.

-Original Message-
From: suomi [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 19, 2005 7:08 AM
To: mysql@lists.mysql.com
Subject: SELECT ... INTO OUTFILE ... and LOAD DATA INFILE ... with a key

Hi listers
I once asked if there is an SQL syntax permitting to copy a row in the 
same table. I got no answer, so there is no such syntax.

now i meant to have found a work-around using (see subject).

problem is, that when i do a SELECT * ... INTO OUTFILE .. i will also 
catch the PRIMARY KEY column if there is one and the LOAD DATA INFILE 
... of this file will fail because of duplicate keys. i tried to use the
FOREIGN_KEY_CHECKS=0 but obiousely this works on foreign keys not on the

primary key.

certainly, i can very very clumsily construct a SELECT at1, ... atn INTO

  OUTFILE statement which selects all columns except the primary key.

the REPLACE and IGNORE constructs are not what i want either, because i 
want to add a row in any case, not replace an existing one nore ignore 
the action.

is there a more elegant way then the clumsy making of an attr list, 
which includes alle columns except the primary key column?

thanks very much for your interest and understanding.

suomi

-- 
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]

RE: Date arithmetic: 2005-08-31 - 1

[Gordon Bruce]

Do You know about INTERVAL?

 

 Use it in an exprecssion or funtion as 

 

 

..INTERVAL expr type 

where expr is any numerical value

 

*   The INTERVAL keyword and the type specifier are not case
sensitive. 

The following table shows how the type and expr arguments are related: 

type Value 

Expected expr Format 

MICROSECOND

MICROSECONDS 

SECOND

SECONDS 

MINUTE

MINUTES 

HOUR

HOURS 

DAY

DAYS 

WEEK

WEEKS 

MONTH

MONTHS 

QUARTER

QUARTERS 

YEAR

YEARS 

SECOND_MICROSECOND

'SECONDS.MICROSECONDS' 

MINUTE_MICROSECOND

'MINUTES.MICROSECONDS' 

MINUTE_SECOND

'MINUTES:SECONDS' 

HOUR_MICROSECOND

'HOURS.MICROSECONDS' 

HOUR_SECOND

'HOURS:MINUTES:SECONDS' 

HOUR_MINUTE

'HOURS:MINUTES' 

DAY_MICROSECOND

'DAYS.MICROSECONDS' 

DAY_SECOND

'DAYS HOURS:MINUTES:SECONDS' 

DAY_MINUTE

'DAYS HOURS:MINUTES' 

DAY_HOUR

'DAYS HOURS' 

YEAR_MONTH

'YEARS-MONTHS' 

 

 

 

mysql> select min(addr_id) from addresses;

+--+

| min(addr_id) |

+--+

|2 |

+--+

1 row in set (0.00 sec)

 

mysql> select now() + INTERVAL min(addr_ID) Day from addresses;

+---+

| now() + INTERVAL min(addr_ID) Day |

+---+

| 2005-08-25 15:38:15   |

+---+

1 row in set (0.00 sec)

 

mysql> select now()

-> ;

+-+

| now()   |

+-+

| 2005-08-23 15:38:31 |

+-+

1 row in set (0.00 sec)

 

-Original Message-
From: Barbara Deaton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 23, 2005 2:37 PM
To: mysql@lists.mysql.com
Subject: Date arithmetic: 2005-08-31 - 1

 

All,

 

I know MySQL comes with all sorts of wonderful functions to do date
arithmetic, the problem is the context that my application is being
called in I don't know if a user wants me to add or subtract days.  I'm
just given the number of days that need to be either added or subtracted
from the date given.

 

So for example, if your table was

 

mysql> select * from dtinterval;

+

| datecol  

+

2005-09-01

2005-08-30

2005-08-31 

+--

 

a user could enter:

 

select count(*) from dtinterval where datecol - 1 = '30AUG2005'd;

 

Which is our applications SQL, my part of the product is only give the
value 1, I have to transform that into something MySQL will understand
as 1 day and then pass that back into the SQL statement to be passed
down to the MySQL database.  I transform our applications SQL into 

 select COUNT(*) from `dtinterval` where (`dtinterval`.`datecol` - 1) =
'1974-12-04'

 

I know that just doing the -1 is wrong, since "select '2005-08-31' - 1
and that just gives me a year

 

mysql> select '2005-08-31' - 1;

+--+

| '2005-08-31' - 1 |

+--+

| 2004 |

+--+

 

What do I need to translate the 1 into in order to get back the value
'2005-08-30' ?

 

Thanks for your help.

Barbara

 

-- 

MySQL General Mailing List

For list archives: http://lists.mysql.com/mysql

To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]

 

 

RE: Treating Two Fields Like One

[Gordon Bruce]

I think you misunderstand how auto_increment works. Primary keys using
auto_increment are NOT row numbers.

If your table has a primary key that is an auto_increment field then
when you add a row to the table the value of the primary key of the new
row is 1 greater than the max(Value) before the row was added. 

Once added the value in the field does not change. Let's say you have 
ID  V1  V2
1   a   b
2   x   y 
3   x   u 
4   b   a 
Now you delete the row with ID = 2. The row where V1=x and V2=u still
has a value of 3 in the ID field. 

>From reading the post I think to need to look at some refernces on
handling tree/hierarchie structures in a relational table.
Here are 2 references out of many.


http://www.sitepoint.com/article/hierarchical-data-database

http://www.intelligententerprise.com/001020/celko1_1.jhtml

They should help you understand your 2nd question 
" So I need a way to distinguish one leo from the other."

-Original Message-
From: David Blomstrom [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 24, 2005 2:31 PM
To: mysql@lists.mysql.com
Subject: Re: Treating Two Fields Like One

--- Peter Brawley <[EMAIL PROTECTED]> wrote:

"As you note, the names [of animal taxons] aren't
guaranteed to be unique, or to stay the same . . .
 
> One way out is to give every table an
> auto-incrementing integer PK, and 
> use those keys, which will never change, to mark
> parent-child relationships.

I wanted to follow up on this. I can easily substitute
integers from my primary key for names, but how do I
substitute them for parents? For example:

ID | NAME | PARENT
10 | Canidae | Carnivora
11 | Canis | Canidae
12 | Vulpes |Canidae

I can easily replace Canis with 11, Vulpes with 12.
But they both have the same family - Canidae, which
translates as 10. I could create a new field and
manually, like this:

ID | NAME | PARENT | PARENTID
10 | Canidae | Carnivora | 9
11 | Canis | Canidae | 10
12 | Vulpes |Canidae | 10

But if I add or delete a row, the numerals in my
primary key will change, messing up the values in
PARENTID.

Along similar lines, I have another question...

Consider the database table code below, which displays
animal names (representing all taxonomic heirarchies)
in a child-parent relationship:

ID | NAME | PARENT
1 | Mammalia | (NULL)
2 | Carnivora | Mammalia
3 | Canidae | Carnivora
4 | Canis | Canidae
5 | leo | Canis
6 | Felidae | Carnivora
7 | Panthera | Felidae
8 | leo | Panthera

Rows 5 and 8 represent identical species names, leo.
If I type http://geozoo/stacks/leo/ into my browser,
it defaults to Mammalia > Carnivora > Canidae > Canis
> leo, rather than the lion, Mammalia > Carnivora >
Felidae > Panthera > leo

So I need a way to distinguish one leo from the other.

Would it be possible to somehow combine my
auto-incrementing primary key with the field Name,
converting leo / leo to 5leo / 8leo?

There are two things I'd have to deal with...

1. I'd need to weed the numerals out of the display,
which should look like this...

http://geozoo/stacks/leo/";>leo

not this...

http://geozoo/stacks/8leo/";>8leo

2. The numerals would have to be fluid, as I will be
adding and deleting rows. Thus, the lion could be 8leo
one day and 9leo the next.

I can take this to a PHP forum to learn how to
implement it. But I thought someone on this forum
might tell me if it can be done in the first place.

Thanks.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-- 
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]

RE: Compare two tables

[Gordon Bruce]

If you have the 5.0.x version of MySQL then INFROMATION SCHEMA can give
you what you want. i.e. 

SELECT a.*, b.* 
FROM   INFORMATION_SCHEMA.COLUMNS AS a 
   INNER JOIN _SCHEMA.COLUMNS AS b 
   ON (a.column_name = b.column_name) 
WHERE  a.TABLE_NAME = 'foo_1' 
   AND b.TABLE_NAME = 'foo_2'

If you look up INFORMATION SCHEMA in the documentation you will find the
table definitions to chose the columns you need for your comparison.

21. The INFORMATION_SCHEMA Information Database 
21.1. INFORMATION_SCHEMA Tables

-Original Message-
From: Alfredo Cole [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 26, 2005 9:08 AM
To: mysql@lists.mysql.com
Subject: Compare two tables

Hi:

I need to compare the structure of two tables (fields, field types,
field 
lengths, indices, etc.) to determine if they have the same schema, even
if 
the fields may be in a different order. Is there a command in mysql that
will 
do this? This will be used to determine if the tables are basically the
same, 
or if they need to be upgraded based on the table structures of a
central 
office.

Thank you.

-- 
Alfredo J. Cole
Grupo ACyC

-- 
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]

RE: Union vs OR

[Gordon Bruce]

It's getting late on Friday, but couldn't you build a table with all of
the parameter combinations and then just join against that table?

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 26, 2005 4:25 PM
To: mysql@lists.mysql.com
Subject: Union vs OR

I have a table that holds attributes for users. This is the structure:

TABLE properties (
  id int(11) NOT NULL,
  userid int(11) NOT NULL,
  attrType int(11) NOT NULL,
  attrValue text NOT NULL,
  FULLTEXT KEY propValue (propValue)
) TYPE=MyISAM;

The table is used to find people based on criteria.

A simple query:

select
userID, attrType, attrValue from properties
where
propType = 1
and
propValue= 'some value'

The problem I'm running into is that the number of attributes could be
over
50.

Would a query with many sets of

(propType = 1 and propValue= 'some value')
or
(propType = 2 and propValue= 'some other value')
or ...

work better than doing the same thing with unions?

Or does anyone have an alternate solution?

Thanks for any help!

-- Avi



-- 
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]

RE: insert subquery

[Gordon Bruce]

What am I missing 

INSERT INTO table1 (column names.) 
SELECT VALUES..
FROM table2 
WHERE primary id = insert value

You will have to put in your real table name and column names.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 23, 2005 11:59 AM
To: DJ
Cc: mysql@lists.mysql.com
Subject: Re: insert subquery

DJ <[EMAIL PROTECTED]> wrote on 09/23/2005 12:49:35 PM:

> [EMAIL PROTECTED] wrote:
> 
> >DJ wrote on 09/23/2005 12:22:58 PM:
> > 
> >
> >>i want to insert a row into table1 only if the value being inserted
on 

> >>table1 exists on table2 primary id.
> >>
> >>can i do this with subquery?
> >>
> >>thanx.
> >>
> >> 
> >>
> >
> >Depending on what version MySQL you are using, probably not. A very 
robust 
> >method of doing what you propose is to allow MySQL to do it for you
by 
> >establishing a Foreign Key from table1 to table2. One drawback is
that 
> >both tables need to be InnoDB (which you may not want to support). 
> >
> >What version are you using and what is the possibility of using
InnoDB 
> >with your appliation?
> >
> >Shawn Green
> >Database Administrator
> >Unimin Corporation - Spruce Pine
> >
> >PS: always CC the list on all responses (unless you intentionally
mean 
to 
> >take the conversation off-list)
> > 
> >
> i am using 4.1.x
> hmm.. maybe it's easier if i just check the id is in table2 before 
> inserting into table1.
> not really a big deal just looking to create shortcuts without running

> multiple queries..
> 
> 

With a foreign key defined, you only need to run one query. That's why I

mentioned it. ;-)  Your way works, too.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: strange order by problem

[Gordon Bruce]

Try this

mysql> select distinct secname, date
-> from   optresult
-> where  secname like 'swap%'
->and date like '2005-09-2%'
-> order by if(secname like 'swap%',
-> (mid(secname,5,20)+0),
-> secname);
+--++
| secname  | date   |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5  | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10   | 2005-09-26 |
| SWAP10   | 2005-09-23 |
| SWAP10   | 2005-09-21 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-23 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-21 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP3| 2005-09-21 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
| SWAP5| 2005-09-21 |
| SWAP5| 2005-09-26 |
+--++
18 rows in set (0.00 sec)

-Original Message-
From: Claire Lee [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, September 27, 2005 2:48 PM
To: mysql@lists.mysql.com
Subject: strange order by problem

I need to order a few names by the number following
the main name. For example swap2, swap3, swap10 in the
order of swap2, swap3, swap10, not in swap10, swap2,
swap3 as it will happen when I do an order by.

So I came up with the following query:

mysql> select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname,lengt
h(secname)-locate('p',secname))+0), secname);

I was hoping it will order by the number following
each 'swap' in the secname, it doesn't work. It was
ordered instead by secname.

+--++
| secname  | date   |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5  | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10   | 2005-09-26 |
| SWAP10   | 2005-09-23 |
| SWAP10   | 2005-09-21 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-23 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-21 |
| SWAP3| 2005-09-21 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP5| 2005-09-21 |
| SWAP5| 2005-09-26 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
+--++

However, if I replace the second expression in the if
statement by date, like the following, it's ordered by
date as I would expect.

mysql> select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',date, secname);
+--++
| secname  | date   |
+--++
| SWAP3| 2005-09-21 |
| SWAP0.5  | 2005-09-21 |
| SWAP5| 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10   | 2005-09-21 |
| SWAP2| 2005-09-21 |
| SWAP0.25 | 2005-09-21 |
| SWAP2| 2005-09-22 |
| SWAP3| 2005-09-22 |
| SWAP5| 2005-09-22 |
| SWAP10   | 2005-09-23 |
| SWAP2| 2005-09-23 |
| SWAP3| 2005-09-23 |
| SWAP5| 2005-09-23 |
| SWAP10   | 2005-09-26 |
| SWAP2| 2005-09-26 |
| SWAP3| 2005-09-26 |
| SWAP5| 2005-09-26 |
+--++


So I tried different combinations of the second and
third expressions in the if statement in the query,
the next one is the only one I can get it to order my
way, which is not what I wanted of course since I
don't want other secnames than swap% to order this
way.

mysql> select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname, leng
th(secname)-locate('p', secname))+0),
right(secname,length(secname)-locate('p',secname))+0);
+--++
| secname  | date   |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5  | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-21 |
| SWAP2| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-21 |
| SWAP3| 2005-09-23 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
| SWAP5| 2005-09-26 |
| SWAP5| 2005-09-21 |
| SWAP10   | 2005-09-26 |
| SWAP10   | 2005-09-21 |
| SWAP10   | 2005-09-23 |
+--++

Can anyone see what problems I have in my query? I'm
really stuck here. Thanks.

Claire






__ 
Yahoo! Mail - PC Magazine Editors' Choice 2005 
http://mail.yahoo.com

-- 
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]

RE: how to list foreign keys

[Gordon Bruce]

IF you are on 5.0.x you can use INFORMATION_SCHEMA

 

21.1.13. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table

 

http://dev.mysql.com/doc/mysql/en/key-column-usage-table.html

 

-Original Message-
From: Operator [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 05, 2005 3:02 PM
To: mysql@lists.mysql.com
Subject: how to list foreign keys

 

HI everybody 

 

I'm tryng to find a way to know if a field is a foreign key, by example
if I run this 

 

describe ; 

 

in the "Key" colum I got "PRI" for the primary key field, somebody know
a way to get the foreign keys ? 

 

 

Regards 

 

Daniel

 

RE: [SPAM] - Query help - Bayesian Filter detected spam

[Gordon Bruce]

You might try UNION with the 1st statement pulling all products with
groupid = 0 and the 2nd pulling 1 product with groupid > 1.

-Original Message-
From: John Nichel [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 10, 2005 3:31 PM
To: MySQL List
Subject: [SPAM] - Query help - Bayesian Filter detected spam

Hi, I have this query below, and I have been pulling my hair out for the

past couple of hours trying to get it to do what I want.  As is, it 
works, but I need it to consider other conditions.  One of the columns 
in the products table is called groupid.  I need it to pull all products

with a groupid of 0 and only 1 product with a groupid > 0 (doesn't 
matter which one).  Any help will save my sanity. ;)

SELECT products.*, MIN(pricing.price) as price , products_lng.product as

product_lng, products_lng.descr as descr_lng, products_lng.full_descr as

fulldescr_lng, IF(variants.variantid IS NOT NULL,'Y','') as is_variant, 
IF(classes.classid IS NOT NULL,'Y','') as is_product_options, 
MIN(v_pricing.price) as v_price, products_lng.product as product_lng, 
products_lng.descr as descr_lng, products_lng.full_descr as 
fulldescr_lng, IF(variants.variantid IS NOT NULL,'Y','') as is_variant, 
IF(classes.classid IS NOT NULL,'Y','') as is_product_options, 
MIN(v_pricing.price) as v_price FROM products, pricing , 
products_categories, categories LEFT JOIN products_lng ON 
products_lng.productid = products.productid AND products_lng.code = 'US'

LEFT JOIN classes ON classes.productid = products.productid LEFT JOIN 
variants ON variants.productid = products.productid LEFT JOIN pricing as

v_pricing ON v_pricing.variantid = variants.variantid AND 
v_pricing.quantity = 1 AND v_pricing.membership IN ('','') WHERE 
pricing.productid=products.productid AND pricing.quantity=1 AND 
pricing.membership IN ('','') AND products.product_type <> 'C' AND 
products.product_type <> 'B' AND (pricing.variantid = 0 OR 
(variants.variantid = pricing.variantid AND variants.avail > 0)) AND 
products_categories.productid=products.productid AND 
products_categories.categoryid = categories.categoryid AND 
categories.membership IN ('','') AND 
products_categories.categoryid='412' AND (products_categories.main='Y' 
OR products_categories.main!='Y') AND products.forsale='Y' AND 
(products.avail>0 OR products.product_type NOT IN ('','N')) GROUP BY 
products.productid ORDER BY products_categories.orderby ASC, 
products.product ASC LIMIT 10, 10

-- 
John C. Nichel
KegWorks.com
716.856.9675
[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://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Sub Selects, Alias Names and stored procedures

[Gordon Bruce]

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 | Friends/Family | BAE2    |   10 |
|   1 | St. Michael    | BAE7    |  139 |
|   2 | JS Prospects   | BAE8    |  196 |
|   1 | Home Focus  

RE: SQL Statement Conversion

[Gordon Bruce]

If your MySQL server is a *nix system than table names are case
sensitive.

SELECT A.*, 
   CASE CounselorOnly 
 WHEN 1 THEN 'Yes' 
 WHEN 0 THEN 'No' 
   END AS CO
FROM Activities A 
ORDER BY Activity 

I also just noticed, remove the "CO =" and add "AS CO" following the END
of the case statement.

-Original Message-
From: Jesse Castleberry [mailto:[EMAIL PROTECTED] 
Sent: Friday, November 04, 2005 3:54 PM
To: MySQL List
Subject: SQL Statement Conversion

I'm converting an application from SQL Server to MySQL.  I have the
following query that I need to convert:

SELECT A.*, CO = CASE CounselorOnly WHEN 1 THEN 'Yes' WHEN 0 THEN 'No'
END
FROM Activities a ORDER BY Activity

What is the proper syntax for this in MySQL?

Thanks,
Jesse


-- 
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]

RE: Help optimize this simple find

[Gordon Bruce]

Is it possible to change the geocodes table to look like 

CREATE TABLE `geocodes` (
   `ip` int(10) unsigned zerofill NOT NULL default '00',
   `lat` double default NULL,
   `lon` double default NULL,
   PRIMARY KEY  (`ip`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 

Then you could do 

SELECT lat,lon 
FROM   geocodes 
WHERE  ip BETWEEN 1173020467 AND 1173020467 ;


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, November 04, 2005 10:33 PM
To: Brian Dunning
Cc: mysql@lists.mysql.com
Subject: Re: Help optimize this simple find

Brian Dunning <[EMAIL PROTECTED]> wrote on 11/04/2005 10:36:00 PM:

> This simple find is taking 4 to 7 seconds. Way too long!! (This is a 
> geotargeting query using the database from IP2location.)
> 
> select lat,lon from geocodes where ipFROM<=1173020467 and 
> ipTO>=1173020467
> 
> The database looks like this (how IP2location recommends):
> 
> CREATE TABLE `geocodes` (
>`ipFROM` int(10) unsigned zerofill NOT NULL default '00',
>`ipTO` int(10) unsigned zerofill NOT NULL default '00',
>`lat` double default NULL,
>`lon` double default NULL,
>PRIMARY KEY  (`ipFROM`,`ipTO`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
> 
> And there are 1.7 million records. Any suggestions?
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
> 

I would bet that if you do an EXPLAIN on your query that you will see
that 
you wound up with a full table scan. It did this because it takes fewer 
read operations to just scan the table than if you do an indexed lookup 
for any more than about 30%  of the rows in any table. 

Can you not change the query to not use <= or =>  ??

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Query producing default values

[Gordon Bruce]

Try this
{I wasn't sure whether cd_nature_ltr is in ligne_trans 
  or transaction. This assumes ligne_trans. If it is in 
  transaction thatn move "cd_nature_ltrsn  = 2" into the ON clause.}

SELECT CASE id_ltrsn 
 WHEN NULL THEN 0
 ELSE id_ltsrn
   END AS id_ltrsn, 
   CASE id_ltrsn
 WHEN NULL THEN NOW() 
 ELSE MAX(GREATEST(date_fin_ltrsn  + INTERVAL 1 MONTH,
CURDATE()))   
   END AS subs_start
FROM   ligne_trans  
   LEFT JOIN transaction 
   ON (transaction.id_trsn = ligne_trans.id_trans_ltrsn 
   AND transaction.id_pers_trsn = 278 
   )
WHERE  cd_nature_ltrsn  = 2 
   AND ligne_trans.id_cntxt_ltrsn = 1 
GROUP BY ligne_trans.id_cntxt_ltrsn
> >
> > When past subscipiton exixts it will produce as an example:
> >
> > id_ltrsn | subs_start
> > -
> > 79   | 2006-11-25
> >
> > When no past subscription exists I would like to get today's date as

> > a
> result
> > instead of nothing, example:
> >
> > id_ltrsn | subs_start
> > -
> > 0| 2005-11-09
-Original Message-
From: Jacques Brignon [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, November 09, 2005 9:19 AM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: Query producing default values

Thanks, that makes a lot of sense.

My only problem is that I am using here a standard piece of code on the
application side and I would hate to modify it, the thing I have all
liberty to
change is the query! Reason for trying to ask the query itself to tell
me if
there is nothing in the DB!

--
Jacques Brignon

Selon [EMAIL PROTECTED]:

> Jacques Brignon <[EMAIL PROTECTED]> wrote on 11/09/2005 09:58:07
AM:
>
> > I have a query which scans a subscription databse to locte the most
> recent
> > expiration date of the subscription to a given periodical or
serviceto
> compute
> > the start date of a renewal.
> >
> > It works fine when for a given person such a subscription exists. If
> none
> > exists, as expected the query produces no results.
> >
> > Any suggestion on how to transform this query to produce a default
value
> set
> > when no past subscription exists?
> >
> > Here is the query:
> >
> > SELECT
> > id_ltrsn, MAX(GREATEST(date_fin_ltrsn  + INTERVAL 1 MONTH,
CURDATE()))
> AS
> > subs_start
> > FROM
> > ligne_trans, transaction
> > WHERE
> > transaction.id_trsn = ligne_trans.id_trans_ltrsn AND cd_nature_ltrsn
= 2
> > AND ligne_trans.id_cntxt_ltrsn = 1
> > AND transaction.id_pers_trsn = 278
> > GROUP BY
> > ligne_trans.id_cntxt_ltrsn
> >
> > When past subscipiton exixts it will produce as an example:
> >
> > id_ltrsn | subs_start
> > -
> > 79   | 2006-11-25
> >
> > When no past subscription exists I would like to get today's date as
a
> result
> > instead of nothing, example:
> >
> > id_ltrsn | subs_start
> > -
> > 0| 2005-11-09
> >
> >
> > Thanks for any help you can provide
> >
> > --
> > Jacques Brignon
> >
>
> You are asking the database to return with data it does not have. Can
you
> not detect the fact that you found no records and use that in your
> application code to supply a default date?  That would be much easier
to
> implement and maintain than any database-based solution.  The SQL can
> become quite convoluted when you start trying to simulate missing
values.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>

-- 
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]

RE: Format for saving date field.

[Gordon Bruce]

What is the source of the data that is displayed on the screen. If it is
a field in a MySQL table and the data type for that field is either DATE
or DATETIME then it will intsert/update without any manipulation. 

Try doing a 

SELECT datefield 
FROM   table 
Limit 15;

outside of your ASP.NET environment. {Command line, SQLYOG, Query
Browser etc.}

-Original Message-
From: Jesse Castleberry [mailto:[EMAIL PROTECTED] 
Sent: Thursday, November 10, 2005 1:49 PM
To: MySQL List
Subject: Format for saving date field.

When doing an update or insert into a database with a date field, the
format
for the data on the screen is m/d/.  However, I believe that MySQL
is
expecting it in the format of -mm-dd.  I'm using MySQL in an ASP.Net
application.  Is there an EASY way to convert to the data to a format
that
MySQL will allow either with some MySQL function, or with an ASP.NET
function?  I realize that I could "rip the data apart", and put it back
in
the same format that MySQL is looking for, but there's got to be some
easier
way.

Thanks,
Jesse


-- 
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]

RE: copying data!!!

[Gordon Bruce]

This is kind of ugly, but with the multitable limitations of 3.23 it
probably works.

CREATE TABLE TEMP 
SELECT table_1 columns except age, table_2 age
FROM   table_1 
   INNER JOIN table_2 
   USING (name);
TRUNCATE table_1; 

INSERT INTO table_1 
SELECT * 
FROM TEMP; 

DROP TABLE_1;



-Original Message-
From: Sachin Bhugra [mailto:[EMAIL PROTECTED] 
Sent: Friday, December 09, 2005 3:00 PM
To: mysql@lists.mysql.com
Cc: [EMAIL PROTECTED]
Subject: Re: copying data!!!

Tnx for the reply Jimmy. I also sent another question( i know its a
very silly question for you all..but believe me i am tryin this for
past three days and not able to get it)

Pls hlp..(just give me hint in right direction, and i will try to do
the rest)

Tnx
Sachin

-- 
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]

RE: copying data!!!

[Gordon Bruce]

Sorry the DROP TABLE Should be TEMP not table1

-Original Message-
From: Gordon Bruce [mailto:[EMAIL PROTECTED] 
Sent: Friday, December 09, 2005 3:51 PM
To: Sachin Bhugra; mysql@lists.mysql.com
Cc: [EMAIL PROTECTED]
Subject: RE: copying data!!!

This is kind of ugly, but with the multitable limitations of 3.23 it
probably works.

CREATE TABLE TEMP 
SELECT table_1 columns except age, table_2 age
FROM   table_1 
   INNER JOIN table_2 
   USING (name);
TRUNCATE table_1; 

INSERT INTO table_1 
SELECT * 
FROM TEMP; 

DROP TABLE_1;



-Original Message-
From: Sachin Bhugra [mailto:[EMAIL PROTECTED] 
Sent: Friday, December 09, 2005 3:00 PM
To: mysql@lists.mysql.com
Cc: [EMAIL PROTECTED]
Subject: Re: copying data!!!

Tnx for the reply Jimmy. I also sent another question( i know its a
very silly question for you all..but believe me i am tryin this for
past three days and not able to get it)

Pls hlp..(just give me hint in right direction, and i will try to do
the rest)

Tnx
Sachin

-- 
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]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: [SPAM] - Re: locating ibdata1 and *.ibd files in different directories. - Bayesian Filter detected spam

[Gordon Bruce]

The following takes a little effort, but it should get you close to dump
file size.

On 5.0.x you can use Information_schema.columns to get average row
length for MyISAM tables. Take that plus the punctutation {~35 + 3* #
cols for insert per row if you enclose your columns in "'s} in the
insert statements generated by mysqldump times the number of rows and
that will give you the size of the MyISAM tables. 

For INNODB use 

mysql> select avg(length(concat( col1, col2,...))) AS Avg_Len, count(*)
-> from table ;
+--+--+
| Avg_Len  | count(*) |
+--+--+
| 107.5588 |  3514429 |
+--+--+
1 row in set (1 min 1.31 sec)

I would also use Information_schema.columns to get the column names so I
would not have to type them. 




-Original Message-
From: Gleb Paharenko [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, December 20, 2005 12:24 PM
To: mysql@lists.mysql.com
Subject: [SPAM] - Re: locating ibdata1 and *.ibd files in different
directories. - Bayesian Filter detected spam

Hello.



> symbolic links! Thats a neat solution. Question: when you say

> "symbolic links for databases" do you mean links to ibd files, ibdata1

> file, either, or something else?



I've meant symbolic links for databases. See:

  http://dev.mysql.com/doc/refman/5.0/en/symbolic-links.html



> Is mysql smart enough not to use my indices when importing until after

> the import, or should I de-activate my indices until after the import?



mysqldump from 5.0.17 distribution sets FOREIGN_KEY_CHECKS to 0; for

your version you can check this by yourself.



> Also, is there a formula of what I can expect the size of the dumped

> files to be?



For a pity, I don't know any formula, even approximate.









Nathan Gross wrote:

> On 12/20/05, Gleb Paharenko <[EMAIL PROTECTED]> wrote:

> 

>>Hello.

>>

>>

>>

>>Please, next time answer to the list as well.

> 

> Sorry. I didn't realize Gmail's 'reply' didn't go to the list. First

> time I noticed the 'reply to all' option in Gmail. Thanks.

> 

> 

>>As far as I know, you  can't specify the location of ibd files,
they're s=

> 

> tored in the

> 

>>database directory, however, you can use symbolic links for databases
to =

> 

> have 

> 

>>them in another place.

> 

> symbolic links! Thats a neat solution. Question: when you say

> "symbolic links for databases" do you mean links to ibd files, ibdata1

> file, either, or something else?

> 

> 

> 

>>>all databases? So the question is if I can locate the ibdata1 file
somew=

> 

> here else.

> 

>>

>>Have you tried just to change the value of innodb_home_dir to the new

>>location, and move there ibdata1 file?

> 

> And leave the original subdirs(databases) in the original place? I can

> try. This means though, that the absolute db paths are coded into the

> ibdata file.

> 

>>>ibd files. BUT, the ibdata1 file is still 7 gig and being

>>

>>

>>

>>If you want do decrease the size of ibdata1 file, you should dump all

>>your InnoDB tables, stop the server, remove all existing tablespace

>>files, configure a new tablespace, restart the server, import the dump

>>files. In such a way you'll move all your tables which are in ibdata1

>>tablespace to may ibd files in databases. See:

>>

>>  http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html

> 

> Is mysql smart enough not to use my indices when importing until after

> the import, or should I de-activate my indices until after the import?

> Also, is there a formula of what I can expect the size of the dumped

> files to be?

> 

> Thank you much!

> -nat

> 



-- 
For technical support contracts, goto
https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
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]

Is this a MySQL 5.0.x bug OR What am I missing?

[Gordon Bruce]

I have 2 almost identical SQL statements {copied except 1 is a LEFT join and 
the other is an INNER join}.
The INNER join gives me values for all of the fields. The LEFT join gives me 
NULL's for all of the prec_... {the LEFT join table} fields.
If the INNER JOIN gives me values and not an empty set then why does the LEFT 
join give me NULL's?

I have included the satements, SELECT * and CREATE TABLE for the 3 tables.
I have run this on 5.0.15 and 5.0.17.

mysql> SELECT user.user_ID, sesn.user_ID, sesn.orgn_ID, prec3.orgn_ID,
    ->    prec3.prec_Type,  prec3.prec_Level,prec3.prec_Value, 
prec3.prec_ID,
    ->    if(isnull(prec3.prec_Replace_Level),
    ->   'sesn',
    ->   prec3.prec_Replace_Level
    ->   ) as pl3
    -> FROM   users AS user
    ->    INNER JOIN sessions AS sesn
    ->    USING(user_ID
    ->  )
    ->    LEFT JOIN precedences AS prec3
    ->    ON (prec3.orgn_ID = sesn.orgn_ID
    ->    AND prec3.prec_Type = 'Phones'
    ->    AND prec3.prec_Level = 'user'
    ->    AND prec3.prec_Value = 'Primary'
    ->    AND prec3.prec_ID = 3
    ->    )
    -> where sesn.sesn_ID = 1;
+-+-+-+-+---+++-+--+
| user_ID | user_ID | orgn_ID | orgn_ID | prec_Type | prec_Level | prec_Value | 
prec_ID | pl3  |
+-+-+-+-+---+++-+--+
| AGB1    | AGB1    | AXIS    | NULL    | NULL  | NULL   | NULL   
|    NULL | sesn |
+-+-+-+-+---+++-+--+
1 row in set (0.02 sec)

mysql>
mysql>
mysql> SELECT user.user_ID, sesn.user_ID, sesn.orgn_ID, prec3.orgn_ID,
    ->    prec3.prec_Type,  prec3.prec_Level,prec3.prec_Value, 
prec3.prec_ID,
    ->    if(isnull(prec3.prec_Replace_Level),
    ->   'sesn',
    ->   prec3.prec_Replace_Level
    ->   ) as pl3
    -> FROM   users AS user
    ->    INNER JOIN sessions AS sesn
    ->    USING(user_ID
    ->  )
    ->    INNER JOIN precedences AS prec3
    ->    ON (prec3.orgn_ID = sesn.orgn_ID
    ->    AND prec3.prec_Type = 'Phones'
    ->    AND prec3.prec_Level = 'user'
    ->    AND prec3.prec_Value = 'Primary'
    ->    AND prec3.prec_ID = 3
    ->    )
    -> where sesn.sesn_ID = 1;
+-+-+-+-+---+++-+--+
| user_ID | user_ID | orgn_ID | orgn_ID | prec_Type | prec_Level | prec_Value | 
prec_ID | pl3  |
+-+-+-+-+---+++-+--+
| AGB1    | AGB1    | AXIS    | AXIS    | phones    | user   | Primary    
|   3 | locn |
+-+-+-+-+---+++-+--+
1 row in set (0.00 sec)

mysql>
mysql> SELECT * FROM sessions;
+-+--+--+-+-+-+-+-+-+-+
| sesn_ID | perm_user_ID | perm_usgp_ID | user_ID | usgp_ID | acct_ID | locn_ID 
| orgn_ID | sesn__Timestamp | sesn_Create |
+-+--+--+-+-+-+-+-+-+-+
|   1 | AGB1 | ADZZ | AGB1    | ADZZ    | | AXIS    
| AXIS    | 2005-12-23 08:32:26 | 2005-12-23 08:30:02 |
|   2 | AGB1 | ADZZ | AGB1    | ADZZ    | | AXIS    
| AXIS    | 2005-12-23 08:32:26 | 2005-12-23 08:30:07 |
+-+--+--+-+-+-+-+-+-+-+
2 rows in set (0.02 sec)

mysql>
mysql> SELECT * FROM users;
+-+-+-+-+--+--+---++++-+--+-+-+
| user_ID | orgn_ID | locn_ID | usgp_ID | prev_usgp_ID | user_Log_On_Name | 
user_Pass | user_FName | user_LName | user_PName | user_Active | user_Who | 
user_Timestamp  | user_Create |
+-+-+-+-+--+--+---+----+++-+--+-+-+
| AGB1    | AXIS    | AXIS    | ADZZ    | NULL | gbruce   | 
rgbjs1jc  | Ralph  | Bruce  | Gordon | Yes | AGB1 | 
2005-12-23 08:59:31 | NULL    |
+-+-+-+-

RE: Changing types on the fly in select queries?

[Gordon Bruce]

Generally MySQL does format conversion for you to match data types.

You can also force the CONCAT result to be integer by the following 

where concat(year,period,week) + 0 < 2007031
   ^^^

-Original Message-
From: Jay Paulson (CE CEN) [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, December 27, 2005 12:49 PM
To: mysql@lists.mysql.com
Subject: Changing types on the fly in select queries?

I have a strange question for you all.  I've inherated some code and the
way the code works is that I can only mess with the WHERE part of a
query.  Therefore, I was wondering if something like this would be
possible.

WHERE where concat(year,period,week) as type int < 2007031

Note that I'm trying to change the type of what the concat() is doing.
Is this even possible?  If so is it possible to do it in the WHERE?  

The reason why I think I need to do this is that 'period' is a char(2).
I have to have the leading zero for every entry into the database so I
can run my less than compare to it.  Is there a better way of doing this
than having the 'period' a char(2) type and trying to make whole
concat() a type of int() on the fly?

Thanks!



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]