Re: Need Restore Help
Jesse wrote: Are you sure this is the right line - I mean the whole statement? You are right, I did not include the whole statement. Here's the entire section: /*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/; DELIMITER ;; /*!50003 SET SESSION SQL_MODE=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION */;; /*!50003 CREATE TRIGGER `AlumniAddDate` BEFORE INSERT ON `alumni` FOR EACH ROW SET NEW.AddDate=Now() */;; DELIMITER ; /*!50003 SET SESSION [EMAIL PROTECTED] */; SELECT VERSION(); on my server returns 5.0.15-nt. This appears to be sufficient to execute the statement. ...perhaps as some comments suggest try to remove single apostrophes /which sometimes puzzle parser/ and see if it works. What puzzles me personally is double ;; at the end?! As you can probably see from the rest of the statement that I've included above, ;; ends the current line becuase the delimiter was changed before hand. Any ideas why this won't execute? I could go through the entire backup file and remove the comments, but this would take quite a while, and I'd rather it execute properly to begin with, but not sure why it's not executing now. Thanks, Jesse Strange it ran just fine here on my 5.0.22-nt with sample MyISAM table `alumni`... Is your table `alumni` MyISAM or Innodb? Here are few more pointers: 1. If it is possible *always* try latest version first when solving problems. In your case 5.0.22 I think... 2. Yes 5.0.15-nt should run commented code just fine - I still think problem is in that multiline comment... can you try running it from console (or to edit file with text editor if its only at line 29765) and see what happens? - just try to remove backticks first. I read somewhere on the forums for problems with some spec. characters `'..etc. in comments in earlier versions. 3. For single line CREATE TRIGGER changing DELIMITER wasn't needed actually... but if mandatory I would personally write it like this: DELIMITER //; DROP TRIGGER `AlumniAddDate`// create trigger `AlumniAddDate` BEFORE INSERT on `alumni` for each row SET NEW.AddDate=Now(); // DELIMITER ;// -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Restore Help
Hi, This is because of the version conflict only Try usinng this option as Login into the mysql :- use databasename \. filename.txt Thanks Regards Dilipkumar - Original Message - From: Jesse [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Monday, July 24, 2006 8:36 PM Subject: Need Restore Help I have a backup that was created by a MySQL 5 server using MySQLDump. When I try to restore the database using the following command: mysql -u root -p -D BPA c:\backup\mydata.sql I get the error: ERROR 1064 (42000) at line 29765: 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 '/' at line 1 Here is what line 29765 says in the backup file: /*!50003 CREATE TRIGGER `AlumniAddDate` BEFORE INSERT ON `alumni` FOR EACH ROW SET NEW.AddDate=Now() */;; This is obviously one of the triggers that I've created. I don't know why it's commented them out in the backup, but I don't seem to be able to overcome this. I'd rather it ignore these lines anyway. How do I get passed this? Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Watch the latest updates on Mumbai, with video coverage of news, events, Bollywood, live darshan from Siddhivinayak temple and more, only on www.mumbailive.in Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Cluster
Hi, Try out this :- http://dev.mysql.com/ Try the new MySQL 5.1 Beta! a.. Row-based Replication b.. Table and Index Partitioning c.. MySQL Cluster Disk-Based Tables d.. Dynamic Pluggable Storage Engine API e.. MySQL Cluster Replication f.. Learn About More Cool Features (pdf) » Thanks Regards Dilipkumar - Original Message - From: Jimmy Guerrero [EMAIL PROTECTED] To: 'Kaushal Shriyan' [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, July 25, 2006 7:18 PM Subject: RE: MySQL Cluster Hello, MySQL Cluster has been available since version 4.1. For production purposes we recommend the GA version of 5.0. For the testing of new features (Disk-Data, Replication, etc) take a look at the latest 5.1 version. Thanks, Jimmy Guerrero Sr Product Manager MySQL, Inc -Original Message- From: Kaushal Shriyan [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 25, 2006 8:33 AM To: mysql@lists.mysql.com Subject: Re: MySQL Cluster On 7/25/06, Kaushal Shriyan [EMAIL PROTECTED] wrote: On 7/25/06, Kaushal Shriyan [EMAIL PROTECTED] wrote: Hi ALL I want to implement MySQL Cluster, are there any step by step guide to implement it Thanks and Regards Kaushal Hi Is cluster suite is available only in version of MySQL 5 and above. Regards Kaushal Hi ALL Is cluster suite is available only in version of MySQL 5 and above. Regards Kaushal -- 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] ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Watch the latest updates on Mumbai, with video coverage of news, events, Bollywood, live darshan from Siddhivinayak temple and more, only on www.mumbailive.in Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave Replication issues
Hi,
This is because direct inserts are being pointed to one of the slave (or)
you have taken the dump from the master in which when you try to restore
it and start replication you will have to start from the master position
what u have noted. In such case you might get this error to ignore this
you can start your mysql with skip-slave-error=1062 in such case these
duplicates will not repeat.
Thanks Regards
Dilipkumar
- Original Message -
From: David Hillman [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Wednesday, July 26, 2006 12:34 AM
Subject: Re: Slave Replication issues
On Jul 25, 2006, at 12:47 PM, David Nance wrote:
Hi, we have been losing slaves due to errors in replication. See error
message below. Would appreciate if anyone could share if they have
seen same
issues. It seems something may be getting corrupted in the binary log.
Thanks.
The error reads:
Last_error: Error 'Duplicate entry '12312942' for key 1' on query
'INSERT
INTO permissions (user_id, journal_id, date_granted, start_date,
end_date,
type) VALUES ('1503443', '94', NOW(), '-00-00 00:00:00',
'-00-00
00:00:00', 'author')'. Default database: 'manuscript_central_1_1'
One of the indexes on your 'permissions' table in defined to be
'unique', and your application is trying to insert a row with a
duplicate value. This doesn't seem like a replication error, unless
there really isn't a unique index on permissions... but I bet there is.
--
David Hillman
LiveText, Inc
1.866.LiveText x235
** DISCLAIMER **
Information contained and transmitted by this E-MAIL is proprietary to
Sify Limited and is intended for use only by the individual or entity to
which it is addressed, and may contain information that is privileged,
confidential or exempt from disclosure under applicable law. If this is a
forwarded message, the content of this E-MAIL may not have been sent with
the authority of the Company. If you are not the intended recipient, an
agent of the intended recipient or a person responsible for delivering the
information to the named recipient, you are notified that any use,
distribution, transmission, printing, copying or dissemination of this
information in any way or in any manner is strictly prohibited. If you have
received this communication in error, please delete this mail notify us
immediately at [EMAIL PROTECTED]
Watch the latest updates on Mumbai, with video coverage of news, events,
Bollywood, live darshan from Siddhivinayak temple and more, only on
www.mumbailive.in
Watch the hottest videos from Bollywood, Fashion, News and more only on
www.sifymax.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Row count discrepancy when converting from MyISAM to InnoDB
Hi, Instead of using select count(*) from tablename You can try with show table status like 'tablename' This doesn't takes much longer time. Thanks Regards Dilipkumar - Original Message - From: David Hillman [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, July 25, 2006 11:51 PM Subject: Re: Row count discrepancy when converting from MyISAM to InnoDB On Jul 25, 2006, at 11:55 AM, Frank wrote: Why is the record count so low after conversion to InnoDB? Who should I believe: InnoDB or MyISAM? Any ideas as to what can be done to avoid loss of this many rows? InnoDB doesn't keep a count on number of rows, like MyISAM does. InnoDB only maintains an estimate of the number of rows in each table. This is why select count(*) from table takes a long time on big InnoDB tables. Usually the InnoDB count will be off by 50% or so. http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html -- David Hillman LiveText, Inc 1.866.LiveText x235 ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Watch the latest updates on Mumbai, with video coverage of news, events, Bollywood, live darshan from Siddhivinayak temple and more, only on www.mumbailive.in Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Returning results as a field name
Thanks for the start Peter. I have got that 'Pivot table using a JOIN' query returning results for 2 records just like the example but I'm stuck in modifying to get the result for the five questions (100 to 104). mysql select * from test; +---+-++ | name | question_id | answer | +---+-++ | Mark | 100 | Yes| | Mark | 101 | No | | Mark | 102 | Yes| | Mark | 103 | No | | Mark | 104 | Yes| | Leigh | 101 | No | | Leigh | 102 | Yes| | Leigh | 103 | No | | Leigh | 104 | Yes| | Leigh | 100 | Yes| +---+-++ 10 rows in set (0.00 sec) Adapting the query for my table like so: select t1.name, answer as '100', t2.101 from test as t1 inner join ( select name,answer as '101' from test where question_id='101' ) as t2 on t1.name=t2.name and t1.question_id='100'; +---+--+--+ | name | 100 | 101 | +---+--+--+ | Mark | Yes | No | | Leigh | Yes | No | +---+--+--+ 2 rows in set (0.00 sec) Any clues to get the other questions and their answers into the table greatly appreciated. cheers Mark Dale *** -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 July 2006 2:54 PM To: Mark Dale Subject: Re: Returning results as a field name I have a simple table that outputs results like so: select name, question_id, answer from table; +---+--+-+ |NAME |QUESTION_ID |ANSWER | +---+--+-+ |Mark |100 |Yes | |Mark |101 |No | |Leigh |100 |Yes | |Leigh |101 |No | +---+--+-+ Is there a way to query things so the result looks like this: select ??? ++---+---+ |NAME|100|101| ++---+---+ |Mark |Yes|No | |Leigh |No|Yes| ++---+---+ Have a look at 'Pivot table using a join' at http://www.artfulsoftware.com/queries.php PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Row count discrepancy when converting from MyISAM to InnoDB
Yes your right dilip , but it wont help for INNODB . INNODB , Rows ( show table status\G ) value is an approximation, and may vary from the actual value .Since innodb doesnt keep track on record count For innodb use |SELECT COUNT(*)| to obtain an accurate count.Correct me if iam wrong - Praj Dilipkumar wrote: Hi, Instead of using select count(*) from tablename You can try with show table status like 'tablename' This doesn't takes much longer time. Thanks Regards Dilipkumar - Original Message - From: David Hillman [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, July 25, 2006 11:51 PM Subject: Re: Row count discrepancy when converting from MyISAM to InnoDB On Jul 25, 2006, at 11:55 AM, Frank wrote: Why is the record count so low after conversion to InnoDB? Who should I believe: InnoDB or MyISAM? Any ideas as to what can be done to avoid loss of this many rows? InnoDB doesn't keep a count on number of rows, like MyISAM does. InnoDB only maintains an estimate of the number of rows in each table. This is why select count(*) from table takes a long time on big InnoDB tables. Usually the InnoDB count will be off by 50% or so. http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html -- David Hillman LiveText, Inc 1.866.LiveText x235 ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Watch the latest updates on Mumbai, with video coverage of news, events, Bollywood, live darshan from Siddhivinayak temple and more, only on www.mumbailive.in Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.com
Re: Can Innodb reuse the deleted rows disk space?
hi, Chris Thank you for your advice! I know that Innodb use the logfiles circularly. Can Innodb re-use the deleted rows' disk space in tablespace? Regards, Leo Huang 2006/7/26, Chris [EMAIL PROTECTED]: leo huang wrote: hi, Dilipkumar Thank you very much! I think I know the fact: The Innodb can't reuse the deleted rows' disk space. And a solution is: dump the data; shutdown mysql; delete the files; restart mysql; import the data. InnoDB does re-use the space inside the database, it's the logfiles that are growing. The logs are needed in case you need to replay transactions. I suggest you read this page: http://dev.mysql.com/doc/refman/5.1/en/adding-and-removing.html and this page: http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html Specify 2-3 entries in the innodb_data_file_path and mysql should (if I'm reading it properly) rotate between the files and keep size under control. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
developing for different mysqlclient target libraries?
All, how do I go about building a binary on my workstation with e.g. libmysqlclient.15 such that same binary can also be deployed on a production machine with libmysqlclient.14 )or lower) ? Do I need to have the lower-level mysqlclient library available on my development workstations? /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Restore Help
Strange it ran just fine here on my 5.0.22-nt with sample MyISAM table `alumni`... Is your table `alumni` MyISAM or Innodb? It is MyISAM. Here are few more pointers: 1. If it is possible *always* try latest version first when solving problems. In your case 5.0.22 I think... You were right. I updatd to 5.0.22, and the restore works just fine now. However, I've got one question. when I do a SELECT version(); now, it returns 5.0.22-community-nt. What is that? Does it make a difference? Did I download the wrong version? 3. For single line CREATE TRIGGER changing DELIMITER wasn't needed actually... but if mandatory I would personally write it like this: Unfortunately, I'm not writing it. I'm dealing with what MySQLDump gives me. Thanks for your help. I think the problem is resolved. Once the 5.0.22-community thing is resolved. Don't know if I should look into this or not. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Stored procedures
You would perform the prepare within the stored procedure. Dan. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jon Sent: Tuesday, July 25, 2006 10:31 AM To: mysql@lists.mysql.com Subject: Re: Stored procedures But the scope of a prepared statement is only the session? I want a stored procedure to avoid some sql in clientside code...Or do you mean a prep in the stored? /Jon On 7/25/06, Burke, Dan [EMAIL PROTECTED] wrote: If I understand correctly, what you need is prepared statements. http://dev.mysql.com/doc/refman/5.0/en/sqlps.html Dan. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jon Sent: Tuesday, July 25, 2006 7:44 AM To: Visolve DB Team Cc: mysql@lists.mysql.com; Sena Subject: Re: Stored procedures On 7/25/06, Visolve DB Team [EMAIL PROTECTED] wrote: Hello Jon. Hi there Team :) And thanks for the quick reply Could you tell me the version of MySql ?. You can find the version by excuting the command SELECT version() If the version is below 5, the stored procedure feature would not work . The version shown is 5.0.21-standard (from the rpm MySQL-server-standard-5.0.21-1.rhel3). I have no problem with other sp, like: CREATE PROCEDURE sp_test3 (IN value int) select count(*) from some_table where foo value; It's just defining table and limit I've had problems with (there is also one mentioning this in the manual about creating sp http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html ) So could it be that it's not possible? /Jon Thanks Visolve DB Team. - Original Message - From: Jon [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, July 25, 2006 2:40 PM Subject: Stored procedures Hi list I'm trying to make stored procedures use parameters for limit and tables, I guess this is either me using the wrong datatype or it's not possible. I'm having the same issue with seting the table for the query: CREATE PROCEDURE sp_test1 (IN some_limit int) select * from some_table limit some_limit; and CREATE PROCEDURE sp_test2 (IN some_table table) select * from some_table; Both fail with ERROR 1064 (42000). Can someone please shed some light on this for me? Is this a problem with procedures not being able to do this or is it wrong datatypes or something completely different? Thanks in advance Jon -- This message has been scanned for viruses by TechTeam's email gateway. --- This e-mail transmission is strictly confidential and intended solely for the person or organization to whom it is addressed. It may contain privileged and confidential information and if you are not the intended recipient, you must not copy, distribute or take any action in reliance on it. If you have received this e-mail in error, please notify the sender as soon as possible and delete the e-mail message and any attachment(s). This message has been scanned for viruses by TechTeam's email gateway. ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses by TechTeam's email gateway. --- This e-mail transmission is strictly confidential and intended solely for the person or organization to whom it is addressed. It may contain privileged and confidential information and if you are not the intended recipient, you must not copy, distribute or take any action in reliance on it. If you have received this e-mail in error, please notify the sender as soon as possible and delete the e-mail message and any attachment(s). This message has been scanned for viruses by TechTeam's email gateway. ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ar Problem solve and new with libstdc++.so.6
Hi Joerg, all, I found the ar utility under the /usr/ccs/bin. The make works very fine (AR=ar) even that it comes from the Sun CC Compiler , until the g++ -O3 -DDBUG_OFF -fno-implicit-templates -fno-exceptions -fno-rtti -D_FILE_OFFSET_BITS=64 -DHAVE_RWLOCK_T -o .libs/gen_lex_hash gen_lex_hash.o ../myisam/libmyisam.a ../myisammrg/libmyisammrg.a ../heap/libheap.a ../vio/libvio.a ../mysys/libmysys.a ../dbug/libdbug.a ../regex/libregex.a ../strings/libmystrings.a ../zlib/.libs/libz.so -lpthread -lthread -lpthread -lthread -lpthread -lthread -lpthread -lthread -lpthread -lthread -lposix4 -lcrypt -lgen -lsocket -lnsl -lm -lpthread -lthread -Wl,-R -Wl,/usr/opt/mysql/lib/mysql creating gen_lex_hash ./gen_lex_hash lex_hash.h ld.so.1: gen_lex_hash: fatal: libstdc++.so.6: open failed: No such file or directory make[2]: *** [lex_hash.h] Error 137 The real problem is that this PATH is not exported by default by solaris after the installation. I changed that under the /etc/default/su (or better idea ?). Now what happens with th LD_LIBRARY_PATH (or any other?) as I guess that ? Or because I am using the the GNU libraries Regards Claude Angeloz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Adding Foreign Key Fails
I am trying to add a foreign key to one of my tables. When I execute the following SQL Code: ALTER TABLE `bpa`.`confinvitems` ADD CONSTRAINT `FK_confinvitems_1` FOREIGN KEY `FK_confinvitems_1` (`InvDetID`) REFERENCES `confinvdet` (`ID`) ON DELETE CASCADE; I get the error: MySQL Error Number 1452 Cannot add or update a child row: a foreign key constraint fails (`bpa/#sql-162c_1b`, CONSTRAINT `FK_confinvitems_1` FOREIGN KEY (`InvDetID`) REFERENCES `confinvdet` (`ID`) ON DELETE CASCADE) I have checked, and all the indexes seem to be in place, the data types are exactly the same. There are no duplicate ID's in the ConfInvDet table. Any idea what this error means, and how to fix it? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change a empty value for 0
Hi. Let me explain something about coalesce. coalesce(field,0) return 0 if the field value equals null or the field value if it´s not null. obed [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] Hi. thanks ! but nop... SELECT id_ingenio,cantidad FROM detalle_transaccion WHERE cantidad IS NULL; Empty set (0.00 sec) and with coalesce nop :-( it's the same problem mysql select id_ingenio, (select case when sum( coalesce(cantidad,NULL) ) is null then 0 else sum(cantidad) end from detalle_transaccion where id_ingenio=ingenio.id_ingenio group by id_ingenio) as cantidad from ingenio LIMIT 5; ++--+ | id_ingenio | cantidad | ++--+ | 1 | NULL | | 2 | NULL | | 3 | NULL | | 4 | 2622.77 | | 5 | NULL | ++--+ 5 rows in set (0.00 sec) the problem is that my sub select returns a empty result, it isn't a null value, but somthing straing is happening if i make only the sub select look mysql select case when sum(cantidad) is null then 0 else sum(cantidad) end as a from detalle_transaccion where id_ingenio=1 group by id_ingenio; Empty set (0.00 sec) i think that the group by is doing this... becouse look select case when sum(cantidad) is null then 0 else sum(cantidad) end as a from detalle_transaccion where id_ingenio=1; +--+ | a| +--+ |0 | +--+ 1 row in set (0.01 sec) what can i do ? thanks -- http://www.obed.org.mx --- blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Returning results as a field name
Mark, Thanks for the start Peter. I have got that 'Pivot table using a JOIN' query returning results for 2 records just like the example but I'm stuck in modifying to get the result for the five questions (100 to 104). Right. The 'max-concat trick', 'Group column statistics in rows', might be easier, eg... SELECT name, MAX( IF(question=100,answer,'') ) AS Ans100, MAX( IF(question=101,answer,'') ) AS Ans101, MAX( IF(question=102,answer,'') ) AS Ans102, MAX( IF(question=103,answer,'') ) AS Ans103, MAX( IF(question=104,answer,'') ) AS And104 FROM tbl GROUP BY name PB - Mark Dale wrote: Thanks for the start Peter. I have got that 'Pivot table using a JOIN' query returning results for 2 records just like the example but I'm stuck in modifying to get the result for the five questions (100 to 104). mysql select * from test; +---+-++ | name | question_id | answer | +---+-++ | Mark | 100 | Yes| | Mark | 101 | No | | Mark | 102 | Yes| | Mark | 103 | No | | Mark | 104 | Yes| | Leigh | 101 | No | | Leigh | 102 | Yes| | Leigh | 103 | No | | Leigh | 104 | Yes| | Leigh | 100 | Yes| +---+-++ 10 rows in set (0.00 sec) Adapting the query for my table like so: select t1.name, answer as '100', t2.101 from test as t1 inner join ( select name,answer as '101' from test where question_id='101' ) as t2 on t1.name=t2.name and t1.question_id='100'; +---+--+--+ | name | 100 | 101 | +---+--+--+ | Mark | Yes | No | | Leigh | Yes | No | +---+--+--+ 2 rows in set (0.00 sec) Any clues to get the other questions and their answers into the table greatly appreciated. cheers Mark Dale *** -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 26 July 2006 2:54 PM To: Mark Dale Subject: Re: Returning results as a field name I have a simple table that outputs results like so: select name, question_id, answer from table; +---+--+-+ |NAME |QUESTION_ID |ANSWER | +---+--+-+ |Mark |100 |Yes | |Mark |101 |No | |Leigh |100 |Yes | |Leigh |101 |No | +---+--+-+ Is there a way to query things so the result looks like this: select ??? ++---+---+ |NAME|100|101| ++---+---+ |Mark |Yes |No | |Leigh |No |Yes | ++---+---+ Have a look at 'Pivot table using a join' at http://www.artfulsoftware.com/queries.php PB No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.4/396 - Release Date: 7/24/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Returning results as a field name
Pardon me, too early not enough coffee, that's not quite the 'max-concat trick', but it oughtta work. PB - Peter Brawley wrote: Mark, Thanks for the start Peter. I have got that 'Pivot table using a JOIN' query returning results for 2 records just like the example but I'm stuck in modifying to get the result for the five questions (100 to 104). Right. The 'max-concat trick', 'Group column statistics in rows', might be easier, eg... SELECT name, MAX( IF(question=100,answer,'') ) AS Ans100, MAX( IF(question=101,answer,'') ) AS Ans101, MAX( IF(question=102,answer,'') ) AS Ans102, MAX( IF(question=103,answer,'') ) AS Ans103, MAX( IF(question=104,answer,'') ) AS And104 FROM tbl GROUP BY name PB - Mark Dale wrote: Thanks for the start Peter. I have got that 'Pivot table using a JOIN' query returning results for 2 records just like the example but I'm stuck in modifying to get the result for the five questions (100 to 104). mysql select * from test; +---+-++ | name | question_id | answer | +---+-++ | Mark | 100 | Yes| | Mark | 101 | No | | Mark | 102 | Yes| | Mark | 103 | No | | Mark | 104 | Yes| | Leigh | 101 | No | | Leigh | 102 | Yes| | Leigh | 103 | No | | Leigh | 104 | Yes| | Leigh | 100 | Yes| +---+-++ 10 rows in set (0.00 sec) Adapting the query for my table like so: select t1.name, answer as '100', t2.101 from test as t1 inner join ( select name,answer as '101' from test where question_id='101' ) as t2 on t1.name=t2.name and t1.question_id='100'; +---+--+--+ | name | 100 | 101 | +---+--+--+ | Mark | Yes | No | | Leigh | Yes | No | +---+--+--+ 2 rows in set (0.00 sec) Any clues to get the other questions and their answers into the table greatly appreciated. cheers Mark Dale *** -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 26 July 2006 2:54 PM To: Mark Dale Subject: Re: Returning results as a field name I have a simple table that outputs results like so: select name, question_id, answer from table; +---+--+-+ |NAME |QUESTION_ID |ANSWER | +---+--+-+ |Mark |100 |Yes | |Mark |101 |No | |Leigh |100 |Yes | |Leigh |101 |No | +---+--+-+ Is there a way to query things so the result looks like this: select ??? ++---+---+ |NAME|100|101| ++---+---+ |Mark |Yes |No | |Leigh |No |Yes | ++---+---+ Have a look at 'Pivot table using a join' at http://www.artfulsoftware.com/queries.php PB No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.4/396 - Release Date: 7/24/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.4/396 - Release Date: 7/24/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding Foreign Key Fails
Hi, Is this a InnoDB table. Thanks Regards Dilipkumar - Original Message - From: Jesse [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Wednesday, July 26, 2006 6:28 PM Subject: Adding Foreign Key Fails I am trying to add a foreign key to one of my tables. When I execute the following SQL Code: ALTER TABLE `bpa`.`confinvitems` ADD CONSTRAINT `FK_confinvitems_1` FOREIGN KEY `FK_confinvitems_1` (`InvDetID`) REFERENCES `confinvdet` (`ID`) ON DELETE CASCADE; I get the error: MySQL Error Number 1452 Cannot add or update a child row: a foreign key constraint fails (`bpa/#sql-162c_1b`, CONSTRAINT `FK_confinvitems_1` FOREIGN KEY (`InvDetID`) REFERENCES `confinvdet` (`ID`) ON DELETE CASCADE) I have checked, and all the indexes seem to be in place, the data types are exactly the same. There are no duplicate ID's in the ConfInvDet table. Any idea what this error means, and how to fix it? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Watch the latest updates on Mumbai, with video coverage of news, events, Bollywood, live darshan from Siddhivinayak temple and more, only on www.mumbailive.in Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding Foreign Key Fails
Is this a InnoDB table. Yes, sorry, I should have mentioned that. The rest of my tables are MyISAM, and I discovered that it does not support Foreign Keys. So, I converted these tables to InnoDB so that I can put the Foreign Keys in. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change a empty value for 0
On 7/26/06, João Cândido de Souza Neto [EMAIL PROTECTED] wrote: Hi. Let me explain something about coalesce. coalesce(field,0) return 0 if the field value equals null or the field value if it´s not null. :-) ooo !!! ok... it's the same to do: case when field is null then 0 else field end Than's a lot everyone !!! i didn't know it -- http://www.obed.org.mx --- blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Checkboxes
I am trying to allow a user to use checkboxes for multiple selections, and I am trying to get the database to record the choices made by the user. I am using a VARCHAR(250), but I am guessing that may be wrong. Also, other than adding a [] to the end of the 'name' (e.g. input type=checkbox name=mod_type[] value=alternative /Alternativebr /), what do I need to do? I hope I am asking this question correctly. If not, please feel free to flame me. Nick
Re: Checkboxes
Nicholas Vettese wrote: I am trying to allow a user to use checkboxes for multiple selections, and I am trying to get the database to record the choices made by the user. I am using a VARCHAR(250), but I am guessing that may be wrong. Also, other than adding a [] to the end of the 'name' (e.g. input type=checkbox name=mod_type[] value=alternative /Alternativebr /), what do I need to do? If you have N = a large number of checkboxes, if the list will definitely not change, and if they are not likely to require frequent individual edits, saving them as a string of N 0|1|? values can be quite efficient for saving retrieving them. We've written test scoring software based on that. But if frequent item edits are possible, you are further ahead saving each as a named TINYINT. P. -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.4/399 - Release Date: 7/25/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Checkboxes
Looks like more of a php question that a mysql question ;) anyway, the mysql part: use either an unsigned interger (tinyint, if less than 256 possibilities, smallint if over 256 but less than 65565, etc) or use a set or enum. Using a varchar is not really the way to go. If you choose to use an integer and you need some kind of string representation of the choice, use a secondary table to hold the string values and let the integer in the main table be a foreign key to the secondary table ;) Nicholas Vettese wrote: I am trying to allow a user to use checkboxes for multiple selections, and I am trying to get the database to record the choices made by the user. I am using a VARCHAR(250), but I am guessing that may be wrong. Also, other than adding a [] to the end of the 'name' (e.g. input type=checkbox name=mod_type[] value=alternative /Alternativebr /), what do I need to do? I hope I am asking this question correctly. If not, please feel free to flame me. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Checkboxes
Nick, It looks like the mysql part has been answered. I was going to suggest using a ENUM field myself. For the PHP part, if you are trying to keep multiple rows in your HTML correlated, ie $customer[1] = $mod_type[1] you may not want to use a checkbox field checkbox fields only pass a field if they are checked - so if you have information for customer[0], and the checkbox is not checked, then $mod_type[0] would NOT be from the first row, it would be from the first CHECKED row. The best solution I have found is to use a RADIO button with the value set to the default. this way it always gets a value. I have seen others suggest to using a hidden field to preset this field so there is always a value -- input type=hidden name=mod_type_0 value=other/ input type=checkbox name=mod_type_0 value=alternative /Alternativebr /) This way, if the checkbox is unchecked, it gets a value of other, but if it is checked, it will pass a value ot alternative. Note - I am not sure how this would work with an array (mod_type[]). PHP might treat the hidden field as index 0, and if the checkbox is checked, it would be index 1 - George -Original Message- From: Nicholas Vettese [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 26, 2006 12:04 PM To: mysql@lists.mysql.com Subject: Checkboxes I am trying to allow a user to use checkboxes for multiple selections, and I am trying to get the database to record the choices made by the user. I am using a VARCHAR(250), but I am guessing that may be wrong. Also, other than adding a [] to the end of the 'name' (e.g. input type=checkbox name=mod_type[] value=alternative /Alternativebr /), what do I need to do? I hope I am asking this question correctly. If not, please feel free to flame me. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.25
I know the general philosophy with regards to releases, but the bug fix lists within MySQL 5.0.23, 24, and 25 seem substantial enough to merit a new binary release for MySQL. Does anyone else feel similarly on this? I'm really looking forward to MySQL 5.0.25...how about it? Best regards, Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
WHERE problem, or is it a problem?
Best group member, I have this query on MySQL version 4.0.27: SELECT part.memberid, part.prefname, part.email, COUNT(*) AS numberofans FROM tblparticipants part LEFT JOIN tblanswers answer ON (answer.par_num=part.memberid) LEFT OUTER JOIN profilepdfauth pdfauth ON (part.memberid=pdfauth.memberid) WHERE pdfauth.id IS NULL GROUP BY part.memberid ORDER BY numberofans DESC, part.memberid; This works fine, However, I only want the results where COUNT(*)=31. So I tried: SELECT part.memberid, part.prefname, part.email, COUNT( * ) AS numberofans FROM tblparticipants part LEFT JOIN tblanswers answer ON ( answer.par_num = part.memberid ) LEFT OUTER JOIN profilepdfauth pdfauth ON ( part.memberid = pdfauth.memberid) WHERE pdfauth.id IS NULL AND COUNT( * ) =31 GROUP BY part.memberid ORDER BY numberofans DESC , part.memberid But then MySQL answered with: # - Invalid use of group function What is the problem here? Why can I not do a WHERE COUNT(*)=31? Is there any other way to just select the COUNT(*)=31? Best regards, Peter Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WHERE problem, or is it a problem?
Peter Lauri wrote: Best group member, I have this query on MySQL version 4.0.27: SELECT part.memberid, part.prefname, part.email, COUNT(*) AS numberofans FROM tblparticipants part LEFT JOIN tblanswers answer ON (answer.par_num=part.memberid) LEFT OUTER JOIN profilepdfauth pdfauth ON (part.memberid=pdfauth.memberid) WHERE pdfauth.id IS NULL GROUP BY part.memberid ORDER BY numberofans DESC, part.memberid; This works fine, However, I only want the results where COUNT(*)=31. So I tried: SELECT part.memberid, part.prefname, part.email, COUNT( * ) AS numberofans FROM tblparticipants part LEFT JOIN tblanswers answer ON ( answer.par_num = part.memberid ) LEFT OUTER JOIN profilepdfauth pdfauth ON ( part.memberid = pdfauth.memberid) WHERE pdfauth.id IS NULL AND COUNT( * ) =31 GROUP BY part.memberid ORDER BY numberofans DESC , part.memberid But then MySQL answered with: # - Invalid use of group function What is the problem here? Why can I not do a WHERE COUNT(*)=31? Is there any other way to just select the COUNT(*)=31? Best regards, Peter Lauri WHERE conditions determine which rows to select. You can't count how many rows you've selected until after you've selected them. Use HAVING to filter the results after selection. Try: SELECT part.memberid, part.prefname, part.email, COUNT(*) AS numberofans FROM tblparticipants part LEFT JOIN tblanswers answer ON ( answer.par_num = part.memberid ) LEFT OUTER JOIN profilepdfauth pdfauth ON ( part.memberid = pdfauth.memberid) WHERE pdfauth.id IS NULL GROUP BY part.memberid HAVING numberofans =31 ORDER BY numberofans DESC , part.memberid Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WHERE problem, or is it a problem?
Use the HAVING clause: SELECT part.memberid, part.prefname, part.email, COUNT(*) AS numberofans FROM tblparticipants part LEFT JOIN tblanswers answer ON (answer.par_num=part.memberid) LEFT OUTER JOIN profilepdfauth pdfauth ON (part.memberid=pdfauth.memberid) WHERE pdfauth.id IS NULL GROUP BY part.memberid HAVING count(*) =31 ORDER BY numberofans DESC, part.memberid; Peter Lauri [EMAIL PROTECTED] 07/26/2006 09:58 AM To mysql@lists.mysql.com cc Subject WHERE problem, or is it a problem? Best group member, I have this query on MySQL version 4.0.27: SELECT part.memberid, part.prefname, part.email, COUNT(*) AS numberofans FROM tblparticipants part LEFT JOIN tblanswers answer ON (answer.par_num=part.memberid) LEFT OUTER JOIN profilepdfauth pdfauth ON (part.memberid=pdfauth.memberid) WHERE pdfauth.id IS NULL GROUP BY part.memberid ORDER BY numberofans DESC, part.memberid; This works fine, However, I only want the results where COUNT(*)=31. So I tried: SELECT part.memberid, part.prefname, part.email, COUNT( * ) AS numberofans FROM tblparticipants part LEFT JOIN tblanswers answer ON ( answer.par_num = part.memberid ) LEFT OUTER JOIN profilepdfauth pdfauth ON ( part.memberid = pdfauth.memberid) WHERE pdfauth.id IS NULL AND COUNT( * ) =31 GROUP BY part.memberid ORDER BY numberofans DESC , part.memberid But then MySQL answered with: # - Invalid use of group function What is the problem here? Why can I not do a WHERE COUNT(*)=31? Is there any other way to just select the COUNT(*)=31? Best regards, Peter Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
RE: WHERE problem, or is it a problem?
That did it, thank you all! -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Thursday, July 27, 2006 2:10 AM To: Peter Lauri Cc: mysql@lists.mysql.com Subject: Re: WHERE problem, or is it a problem? Peter Lauri wrote: Best group member, I have this query on MySQL version 4.0.27: SELECT part.memberid, part.prefname, part.email, COUNT(*) AS numberofans FROM tblparticipants part LEFT JOIN tblanswers answer ON (answer.par_num=part.memberid) LEFT OUTER JOIN profilepdfauth pdfauth ON (part.memberid=pdfauth.memberid) WHERE pdfauth.id IS NULL GROUP BY part.memberid ORDER BY numberofans DESC, part.memberid; This works fine, However, I only want the results where COUNT(*)=31. So I tried: SELECT part.memberid, part.prefname, part.email, COUNT( * ) AS numberofans FROM tblparticipants part LEFT JOIN tblanswers answer ON ( answer.par_num = part.memberid ) LEFT OUTER JOIN profilepdfauth pdfauth ON ( part.memberid = pdfauth.memberid) WHERE pdfauth.id IS NULL AND COUNT( * ) =31 GROUP BY part.memberid ORDER BY numberofans DESC , part.memberid But then MySQL answered with: # - Invalid use of group function What is the problem here? Why can I not do a WHERE COUNT(*)=31? Is there any other way to just select the COUNT(*)=31? Best regards, Peter Lauri WHERE conditions determine which rows to select. You can't count how many rows you've selected until after you've selected them. Use HAVING to filter the results after selection. Try: SELECT part.memberid, part.prefname, part.email, COUNT(*) AS numberofans FROM tblparticipants part LEFT JOIN tblanswers answer ON ( answer.par_num = part.memberid ) LEFT OUTER JOIN profilepdfauth pdfauth ON ( part.memberid = pdfauth.memberid) WHERE pdfauth.id IS NULL GROUP BY part.memberid HAVING numberofans =31 ORDER BY numberofans DESC , part.memberid 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]
pbm with Triggers
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I've a problem with triggers and federated table: I would like insert a record in a unknown table. The table where insert the record is unknown when the trigger starts. But, with a field value, I select the table name. CREATE TRIGGER insert_users AFTER INSERT ON users FOR EACH ROW BEGIN DECLARE tablename CHAR(50); SELECT SERVER_usertable INTO @tablename FROM servers WHERE SERVER_id = NEW.SERVER_id ; INSERT INTO @tablename VALUES (NEW.USER_id, NEW.USER_email, NEW.USER_password, NEW.USER_nickname, NEW.USER_max_space, 0) END; But, it doesn't work... Does somebody have an idea ? Thanks, Jean-Yves. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2.2 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFEx9U6ckDu29zkZ3kRAmucAKC/iiJeidWM2UHDPWVu5DL4in4VvACeN42N 9YMHop0YHtA9ul0ns0qGNk8= =IOV8 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
yum version 5.*
I am using Red Hat Fedora Core 4 and I wanted to yum mysql version 5 of any other and find with Core 4 I can yum only mysql version 4. I imagine Core 5 might be able to yum mysql version 5 but not certain of that. Is there a way I can yum the later version? I studied the man for yum but could not see a way to do that. Karl Larsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JOIN table where not in other table
Best group member, I just made up this query, but let us work from this: SELECT * FROM table1 LEFT OUTER JOIN table2 ON (table1.id=table2.id) WHERE table2.prop IS NULL; This selects all rows from table1 where the id is not also in the table2. Is there any more logic way to do this, this is what I would like to do (in words): I want to select all rows in table1 that does not already have an reference in table2. Is that understandable? Best regards, Peter Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN table where not in other table
On Wednesday 26 July 2006 10:31 am, Peter Lauri wrote: Best group member, I just made up this query, but let us work from this: SELECT * FROM table1 LEFT OUTER JOIN table2 ON (table1.id=table2.id) WHERE table2.prop IS NULL; If I understand correct: SELECT * FROM table 1 WHERE id NOT IN (SELECT id FROM table2); -- Chris White PHP Programmer/DBacardi Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JOIN table where not in other table
I tried that, but that generates: #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 'SELECT id FROM table2) LIMIT 0, 100' at line 1 Maybe it is a Version issue? What version of MySQL do support sub queries? /Peter -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Thursday, July 27, 2006 5:37 AM To: mysql@lists.mysql.com Subject: Re: JOIN table where not in other table On Wednesday 26 July 2006 10:31 am, Peter Lauri wrote: Best group member, I just made up this query, but let us work from this: SELECT * FROM table1 LEFT OUTER JOIN table2 ON (table1.id=table2.id) WHERE table2.prop IS NULL; If I understand correct: SELECT * FROM table 1 WHERE id NOT IN (SELECT id FROM table2); -- Chris White PHP Programmer/DBacardi Interfuel -- 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 table where not in other table
On Wednesday 26 July 2006 10:53 am, Peter Lauri wrote: I tried that, but that generates: #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 'SELECT id FROM table2) LIMIT 0, 100' at line 1 What's the full query look like right now (btw, there was a typo earlier, that should have been table1 and not table 1 :/)? -- Chris White PHP Programmer/DBarkTree Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JOIN table where not in other table
The query in full was exactly as you wrote it (but without the typo) :) -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Thursday, July 27, 2006 5:56 AM To: Peter Lauri Cc: mysql@lists.mysql.com Subject: Re: JOIN table where not in other table On Wednesday 26 July 2006 10:53 am, Peter Lauri wrote: I tried that, but that generates: #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 'SELECT id FROM table2) LIMIT 0, 100' at line 1 What's the full query look like right now (btw, there was a typo earlier, that should have been table1 and not table 1 :/)? -- Chris White PHP Programmer/DBarkTree Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN table where not in other table
On Wednesday 26 July 2006 11:00 am, Peter Lauri wrote: The query in full was exactly as you wrote it (but without the typo) :) Alright, yes, what is your version of MySQL? I'm in the 5.0.22 series here and that works just fine. -- Chris White PHP Programmer/DBackItUp Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JOIN table where not in other table
4.0.27, so that is probably the reason. Any other way then with a sub query? I solved it with my stupid solution, feels strange to JOIN tables and choose rows where the join value is NULL (left outer join) :) -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Thursday, July 27, 2006 6:05 AM To: mysql@lists.mysql.com Subject: Re: JOIN table where not in other table On Wednesday 26 July 2006 11:00 am, Peter Lauri wrote: The query in full was exactly as you wrote it (but without the typo) :) Alright, yes, what is your version of MySQL? I'm in the 5.0.22 series here and that works just fine. -- Chris White PHP Programmer/DBackItUp Interfuel -- 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: Returning results as a field name
Many thanks Peter, that works a treat. Just recapping for others who may be interested. The problem was to get a result table that was displaying like this: mysql select * from test; +---+-++ | name | question_id | answer | +---+-++ | Mark | 100 | Yes| | Mark | 101 | No | | Mark | 102 | Yes| | Mark | 103 | No | | Mark | 104 | Yes| | Leigh | 101 | No | | Leigh | 102 | Yes| | Leigh | 103 | No | | Leigh | 104 | Yes| | Leigh | 100 | Yes| +---+-++ 10 rows in set (0.00 sec) to display like this: +---+--+--+--+--+--+ | name | Q100 | Q101 | Q102 | Q103 | Q104 | +---+--+--+--+--+--+ | Leigh | Yes | No | Yes | No | Yes | | Mark | Yes | No | Yes | No | Yes | +---+--+--+--+--+--+ 2 rows in set (0.01 sec) Solution: mysql SELECT - name, - MAX( IF(question_id=100,answer,'') ) AS Q100, - MAX( IF(question_id=101,answer,'') ) AS Q101, - MAX( IF(question_id=102,answer,'') ) AS Q102, - MAX( IF(question_id=103,answer,'') ) AS Q103, - MAX( IF(question_id=104,answer,'') ) AS Q104 - FROM test - GROUP BY name; From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 July 2006 11:25 PM To: [EMAIL PROTECTED] Cc: Mark Dale; mysql@lists.mysql.com Subject: Re: Returning results as a field name Pardon me, too early not enough coffee, that's not quite the 'max-concat trick', but it oughtta work. PB - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN table where not in other table
On Wednesday 26 July 2006 11:30 am, Peter Lauri wrote: 4.0.27, so that is probably the reason. Any other way then with a sub query? I solved it with my stupid solution, feels strange to JOIN tables and choose rows where the join value is NULL (left outer join) :) Are you using phpMyAdmin? I was told by a coworker that phpMyAdmin adds those limits in. Wondering if taking the LIMIT out might do it. Also, are the tables really named table1 and table2 (Yah, I know.. but I have to make sure :( )? -- Chris White PHP Programmer/DBoy Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JOIN table where not in other table
The tables are of course not named table1 and table2, just using that in the discussion. :) All is working right now, but my solution is NOT that good according to the small amount of logic I have :) -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Thursday, July 27, 2006 6:43 AM To: mysql@lists.mysql.com Subject: Re: JOIN table where not in other table On Wednesday 26 July 2006 11:30 am, Peter Lauri wrote: 4.0.27, so that is probably the reason. Any other way then with a sub query? I solved it with my stupid solution, feels strange to JOIN tables and choose rows where the join value is NULL (left outer join) :) Are you using phpMyAdmin? I was told by a coworker that phpMyAdmin adds those limits in. Wondering if taking the LIMIT out might do it. Also, are the tables really named table1 and table2 (Yah, I know.. but I have to make sure :( )? -- Chris White PHP Programmer/DBoy Interfuel -- 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 table where not in other table
Peter feels strange to JOIN tables and choose rows where the join value is NULL (left outer join) :) It's standard SQL and has a name, "exclusion join". Not a bit more "strange" than counting wot's missing. PB Peter Lauri wrote: 4.0.27, so that is probably the reason. Any other way then with a sub query? I solved it with my "stupid" solution, feels strange to JOIN tables and choose rows where the join value is NULL (left outer join) :) -Original Message- From: Chris White [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 27, 2006 6:05 AM To: mysql@lists.mysql.com Subject: Re: JOIN table where not in other table On Wednesday 26 July 2006 11:00 am, Peter Lauri wrote: The query in full was exactly as you wrote it (but without the typo) :) Alright, yes, what is your version of MySQL? I'm in the 5.0.22 series here and that works just fine. No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.4/399 - Release Date: 7/25/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can Innodb reuse the deleted rows disk space?
leo huang wrote: hi, Chris Thank you for your advice! I know that Innodb use the logfiles circularly. Can Innodb re-use the deleted rows' disk space in tablespace? I'm sure it will, what makes you think it won't? You might need an 'optimize table' or something to see a reduction in the on disk file size but mysql will reclaim that space as it needs to. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: hi, I am not able view all the records in the tabel
Annam Srinivas wrote: Hi, Problem is like this. There is table with 'table_name-1' where, when I am executing the query like ' select * from table_name-1;' it is showing only 432 records but acctually there are 539 records in the table. when I select individual records from the table it is showing (able to view the record data) from 1, . . . 539 record. How do you know there are 539? Did you count them by hand? There could be id's missing in the list, eg: 1 2 3 -- 4 is missing 5 where records have been deleted. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
