Re: Help me
On 21/07/2010 16:33, Karthik Pr wrote:
I have created a table as follows but i was not able to use full text search on
a specific data.
create table racebike (id int auto_increment not null primary key, name
varchar(10), user text,fulltext(name,user));
[snip]
The query is
mysql select * from racebike where match (user) against ('speed');
It should be:
mysql select * from racebike where match (name,user) against ('speed');
When using a fulltext index, the query has to name all the fields
included in the index, unless you're performing the search in Boolean mode.
http://dev.mysql.com/doc/refman/5.1/en/fulltext-restrictions.html
Mark
--
http://mark.goodge.co.uk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: help me out for this scenario
-Original Message- From: MuraliKrishna [mailto:murali_kris...@arthaoptions.com] Sent: Thursday, February 11, 2010 12:52 AM To: mysql@lists.mysql.com Subject: help me out for this scenario I have a table like as follws Emp_id, first_login , second_login [JS] I think you are asking for trouble doing things this way. If life has taught me anything, it's that someday somebody is going to ask for a third_login. You'd be much better off in the long run using a second table for the login times, even if it seems like overkill. In the second table, store the login number and time for each occurrence for each user: Emp_id, login_number, login_time Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: help me regarding how to use cursor and handler
A handler is something you handle things by, for example a table you've opened. It's basically a pointer to an object. A cursor, like the one on your screen, keeps track of where you are in a resultset - operations like next() move it around. Those things are only useful to you if you're also good at programming :-) On Fri, Feb 5, 2010 at 4:47 AM, muralikrishna g muralikrishn...@gmail.comwrote: hi.. i like to know really what is a cursor and a handler and those functionality and purpose... i have tried much over net... but i didnt find proper material and examples for those.. if any body familiar with those.. please help me.. thnaks in advance by Muralikrishna -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
RE: help me out for this problem...
Instead of: - else 2003 - where id between 1 and 6; Try - else 2003 - end - where id between 1 and 6; Or maybe even: mysql update table1 - set year=case when id IN (1, 2, 4) then 2000 - when id IN(2, 4, 6) then 2001 - else 2003 - end - where id between 1 and 6; Good luck! Regards, m -Original Message- From: MuraliKrishna [mailto:murali_kris...@arthaoptions.com] Sent: Friday, February 05, 2010 12:04 PM To: mysql@lists.mysql.com Subject: help me out for this problem... Here I have to update year column with reference to the row id mysql update table1 - set year=case when id=1 then 2000 - when id=2 then 2001 - when id=3 then 2000 - when id=4 then 2001 - when id=5 then 2000 - when id=6 then 2001 - else 2003 - where id between 1 and 6; 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 'where id between 1 and 6' at line 9 mysql -- Urocze domki... Zobacz http://link.interia.pl/f25aa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: help me out for this problem...
See this link. Might me help for you http://answers.yahoo.com/question/index?qid=20070309043307AATLe9k On 5 February 2010 17:46, misiaQ mis...@poczta.fm wrote: Instead of: - else 2003 - where id between 1 and 6; Try - else 2003 - end - where id between 1 and 6; Or maybe even: mysql update table1 - set year=case when id IN (1, 2, 4) then 2000 - when id IN(2, 4, 6) then 2001 - else 2003 - end - where id between 1 and 6; Good luck! Regards, m -Original Message- From: MuraliKrishna [mailto:murali_kris...@arthaoptions.com] Sent: Friday, February 05, 2010 12:04 PM To: mysql@lists.mysql.com Subject: help me out for this problem... Here I have to update year column with reference to the row id mysql update table1 - set year=case when id=1 then 2000 - when id=2 then 2001 - when id=3 then 2000 - when id=4 then 2001 - when id=5 then 2000 - when id=6 then 2001 - else 2003 - where id between 1 and 6; 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 'where id between 1 and 6' at line 9 mysql -- Urocze domki... Zobacz http://link.interia.pl/f25aa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=rames...@gmail.com -- Ramesh
Re: Help me format this statement
Thanks to everyone who replied. So simple I couldn't see it. :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me format this statement
On Wednesday 12 March 2008 00:14:46 Brian Dunning wrote: I am an idiot. table_a and table_b have exactly the same structure. How do I say this in SQL: INSERT (all records from table_a) into table_b where table_a.customer = '12' Just trying to eventually duplicate the whole table, one customer's set of records at a time. Thanks. IIRC ... something like insert into Table_b select table_a where table_a.customer = '12' Check the EXCELLENT docs at ... http://dev.mysql.com/doc/refman/5.1/en/sql-syntax.html ... and refer to the SQL statement syntaxes ... datamanipulation HTH W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help me optimize this sql
SELECT * FROM table_one t1 INNER JOIN table_two t2 ON t1.column_one = t2.column_one LEFT JOIN table_three t3 ON t3.column_two = t1.column_three AND t3.column_four = t1.column_five WHERE column_six LIKE '%dsc%' AND column_seven LIKE '%aaa%'; There is no need for a derived table. Also, using LIKE '%xxx%' prohibits indexes on column_six and column_seven from being used. Also, typically, when doing a LEFT JOIN to a table, that table is used on the *right* side of the ON expression. In your SQL, it is on the left side, which doesn't make much sense. I think you mean for it to be on the right... Cheers, Jay wangxu wrote: sql: select * from table_one inner join table_two on table_two.column_one = table_one.column_one left join (SELECT * from table_three) table_four on table_four.column_two = table_one.column_three and table_four.column_four= table_one.column_five where column_six like '%dsc%' and column_seven like '%aaa%' explain: *** 1. row *** id: 1 select_type: PRIMARY table: table_one type: ALL possible_keys: key: NULL key_len: NULL ref: NULL rows: 481 Extra: Using where *** 2. row *** id: 1 select_type: PRIMARY table: table_two type: ref possible_keys: idx_column_one key: idx_column_one key_len: 153 ref: table_one.column_one rows: 1 Extra: Using where *** 3. row *** id: 1 select_type: PRIMARY table: derived2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2297 Extra: *** 4. row *** id: 2 select_type: DERIVED table: table_three type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2250 Extra: Can I optimize this sql ? thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help me optimize this ALL
No, because you have no WHERE condition. wangxu wrote: sql: SELECT * FROM table_one INNER JOIN table_one table_one1 ON table_one1.column_one = table_one.column_two INNER JOIN table_one table_one2 ON table_one2.column_one = table_one.column_three explain: *** 1. row *** id: 1 select_type: SIMPLE table: table_one type: ALL possible_keys: idx_column_two,idx_column_three key: NULL key_len: NULL ref: NULL rows: 2037 Extra: *** 2. row *** id: 1 select_type: SIMPLE table: table_one1 type: ref possible_keys: idx_column_one key: idx_column_one key_len: 5 ref: table_one.column_two rows: 1 Extra: Using where *** 3. row *** id: 1 select_type: SIMPLE table: table_one2 type: ref possible_keys: idx_column_one key: idx_column_one key_len: 5 ref: table_one.column_three rows: 1 Extra: Using where Can I optimize this ALL on table one? thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help me optimize this ALL
thank you - Original Message - From: Jay Pipes [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, March 06, 2007 4:29 AM Subject: Re: help me optimize this ALL No, because you have no WHERE condition. wangxu wrote: sql: SELECT * FROM table_one INNER JOIN table_one table_one1 ON table_one1.column_one = table_one.column_two INNER JOIN table_one table_one2 ON table_one2.column_one = table_one.column_three explain: *** 1. row *** id: 1 select_type: SIMPLE table: table_one type: ALL possible_keys: idx_column_two,idx_column_three key: NULL key_len: NULL ref: NULL rows: 2037 Extra: *** 2. row *** id: 1 select_type: SIMPLE table: table_one1 type: ref possible_keys: idx_column_one key: idx_column_one key_len: 5 ref: table_one.column_two rows: 1 Extra: Using where *** 3. row *** id: 1 select_type: SIMPLE table: table_one2 type: ref possible_keys: idx_column_one key: idx_column_one key_len: 5 ref: table_one.column_three rows: 1 Extra: Using where Can I optimize this ALL on table one? thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help me to understand multiple locking the same tables (lock; lock; unlock)
Yes, the two examples are equivalent. UNLOCK TABLES releases any locks held by the current thread. All tables that are locked by the current thread are implicitly unlocked when the thread issues another LOCK TABLES... So there is a hole there with either example. In order to keep others from changing your data under you, you'll need a single (WRITE) lock around your entire operations. Sad, but true. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Denis Solovyov [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 10, 2007 2:29 PM To: mysql@lists.mysql.com Subject: Help me to understand multiple locking the same tables (lock; lock; unlock) Dear friends, Please help me to understand several LOCKing the same tables without unlocking them between LOCKs. Imagine the following code: LOCK TABLES t1 READ, t2 READ; -- some hard select queries which need that other threads do not update tables LOCK TABLES t1 WRITE, t2 WRITE; -- some easy update queries UNLOCK TABLES; Is this code equal to the following: LOCK TABLES t1 READ, t2 READ; -- some hard select queries which need that other threads do not update tables UNLOCK TABLES; -- here other threads have a moment to update these tables! LOCK TABLES t1 WRITE, t2 WRITE; -- some easy update queries UNLOCK TABLES; or t1 and t2 will not be unlocked even for a moment before the second lock? Really, I don't want to have a single WRITE LOCK here and freeze everything for some time, but I can't understand if here is a chance for other threads to update tables between two lockings or not... MySQL 4.1, myisam tables (if it is important). Best regards, Denis Solovyov -- 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 me please
Hello. The start point for you in such kind of problems should be researching the output of the following statement: SHOW VARIABLES LIKE '%char%'; Please, run it from different environments (PHP, MySQL Query Browser), and send results to the list. Include the CREATE statement for you tables which hold Latvian characters. Aleksejs Pavlans wrote: Hellou! I have MySQL(charset is utf-8) + PHP 5.0.5. Latvian symbols(auczsnsczikl) not correctly viewed in MySQL Query Browser, but in 'phpmyadmin' its ok. When i use php to viewed data with latvian symbols in IE, then latvian symbols(aui.) is converted to ? symbols. Please Help! Ar cienu, Aleksejs! -- 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]
Re: Help me....i strucke down...
Hello. This is a line from one of your files which were attached in archive: [EMAIL PROTECTED] Test_pgms]# gcc -c -I/usr/lib/bcc/include ^^ -I/usr/include/mysql createdb.c Are you using Borland C++ includes with gcc?! Ashok Kumar [EMAIL PROTECTED] wrote: [-- text/plain, encoding 8bit, charset: iso-8859-1, 25 lines --] Hi friends, In the previous mail i mentioned abt the error in installing mysql4.1 on Linux9. that is still continuing, so i tried with 4.0. It's installed successfully. Now i'm having some doubts on running mysql client pgms on Linux. My sys config is, 1.Intel P4, Intel 845 Chipset and Intel board. 2.Linux 9.0 3.gcc version GCC 3.2.2 20030222 (Red Hat Linux 3.2.2-5) With this mail i attached the mysql client pgm and as well as the error msg which i'm getting. Pls, help me in this. Thanks and Regards, Ashok. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com [-- application/zip, encoding base64, 79 lines, name: err.zip --] [-- Description: 1535120561-err.zip --] [-- text/plain, encoding 7bit, charset: us-ascii, 5 lines --] -- 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]
Re: Help me for God!!
Carlos J Souza wrote: Dear Friends, Iam trying to install MySQL 4.1.x in Windows 2003 Server, and on error occurs when installation try a start de service. The service does not start and installation don't finish. I Try install and reinstall many times and all fail My WIndows is a 2003 Server SP1 I Need a help about it. Regards for all. Carlos J Souza From Brazil Whats the error? -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me for God!!
Carlos J Souza wrote: The error as follows: when installation is try to finish, the install try a start de new service MYSQL 4 and does not success I try the install and reinstall many times and nothing. Regards On Wed, 29 Jun 2005 08:39:51 -0400, JamesDR wrote: Carlos J Souza wrote: Dear Friends, Iam trying to install MySQL 4.1.x in Windows 2003 Server, and on error occurs when installation try a start de service. The service does not start and installation don't finish. I Try install and reinstall many times and all fail My WIndows is a 2003 Server SP1 I Need a help about it. Regards for all. Carlos J Souza From Brazil Whats the error? -- Thanks, James Is this the error number/message? It almost sounds like you are missing some info in your cnf/ini. Also check out perms on the data/(cnf/ini) directories. Short of the exact error message/number from windows/mysql it'll be hard to figure out what the error is. Check your event log and mysql's error logs for any details in the mysql.err log in your data dir. -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me for God!!
Hi Carlos, My suggestion for this problem is, just uninstall the MySQL Server and manually delete all the corresponding files, restart the system once (not a must, but it's a advisable), then u install the MySQL, hope now it'll definitely work. This is happening because of mis-configuration b/w the prev and current installations. Thanks and Regards, Ashok. Carlos J Souza wrote: The error as follows: when installation is try to finish, the install try a start de new service MYSQL 4 and does not success I try the install and reinstall many times and nothing. Regards Yahoo! Sports Rekindle the Rivalries. Sign up for Fantasy Football http://football.fantasysports.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me for God!!
Hello. Very often similar error occurs when you didn't remove service from previous MySQL installation. See: http://dev.mysql.com/doc/mysql/en/windows-troubleshooting.html Carlos J Souza [EMAIL PROTECTED] wrote: Dear Friends, =A0 Iam trying to install MySQL 4.1.x in Windows 2003 =A0Server, and on error occurs when installation try a =A0start de service. The service does not start and =A0installation don't finish. =A0 I Try install and reinstall many times and all fail =A0 My WIndows is a 2003 Server SP1 =A0 =A0I Need a help about it. =A0 =A0 =A0Regards for all. =A0 Carlos J Souza From Brazil -- 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]
Re: Help me optimize this query
Thanks for your inputs Eric. I tried this but it couldn't give me any insight abt how can optimize this for space saving of temp tables. Maybe I am not expert enough to interpret this output, so here it is - - Manish Seeing the explain log, it looks to me like you donot have any index defined on t1 / t2. Having indexes on columns you frequently query on should help reduce space and time. In your case t1.YYY or t2.ZZZ Is there a foreign key relation ship between t1.YYY and t2.ZZZ ? Also, is it not possible to use nested queries in the mysql version you are using. I am not sure from what version nested queries are supported if at all :- select * from t1 where t1.YYY in (select t2.ZZZ from t2 where t2.AAA like '%bla%'); If the nested queries are not supported then you would have to use temporary tables or heap tables in mysql to generate a temporary result by breaking the query. insert into temporary_table select t2.ZZZ from t2 where t2.AAA like '%bla%'; Then use this temporary to join with t1 in an update. Again, before trying this, make sure you have all indexes, foreign keys defined. Defining this itself may solve your problem. Regards, Goutham S Mohan --- Software Engineer, Hewlett Packard [GDIC] __ 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]
Re: Re: Help me optimize this query
Thanks for your inputs Eric. I tried this but it couldn't give me any insight abt how can optimize this for space saving of temp tables. Maybe I am not expert enough to interpret this output, so here it is - - Manish Seeing the explain log, it looks to me like you donot have any index defined on t1 / t2. Having indexes on columns you frequently query on should help reduce space and time. In your case t1.YYY or t2.ZZZ Is there a foreign key relation ship between t1.YYY and t2.ZZZ ? Also, is it not possible to use nested queries in the mysql version you are using. I am not sure from what version nested queries are supported if at all :- select * from t1 where t1.YYY in (select t2.ZZZ from t2 where t2.AAA like '%bla%'); If the nested queries are not supported then you would have to use temporary tables or heap tables in mysql to generate a temporary result by breaking the query. insert into temporary_table select t2.ZZZ from t2 where t2.AAA like '%bla%'; Then use this temporary to join with t1 in an update. Again, before trying this, make sure you have all indexes, foreign keys defined. Defining this itself may solve your problem. Regards, Goutham S Mohan --- Software Engineer, Hewlett Packard [GDIC] __ 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]
Re: help me pls
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Wu_Yang_SKZ, JPcommon wrote: java.sql.SQLException: Communication failure during handshake. Is there a server running on localhost:3306? at org.gjt.mm.mysql.MysqlIO.init(MysqlIO.java:275) at org.gjt.mm.mysql.Connection.init(Connection.java:230) at org.gjt.mm.mysql.Driver.connect(Driver.java:126) at java.sql.DriverManager.getConnection(DriverManager.java:512) at java.sql.DriverManager.getConnection(DriverManager.java:171) Wu, You're using a _very_ old version of the JDBC driver. Try upgrading to something more recent, like 3.0.16 at http://dev.mysql.com/downloads/connector/j/3.0.html and see if the problem goes away. (This is especially true if you are trying to connect to MySQL 4.1 or newer). -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Client Connectivity Office: +1 708 332 0507 www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBtwEctvXNTca6JD8RAh49AJ9/Nk62obyErj6QGDiGZKeTcaCumgCdFiJb G6WC74IeSWAN4o3e3k5CIzI= =D4BQ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me optimize this query
Thanks for your inputs Eric. I tried this but it couldn't give me any insight abt how can optimize this for space saving of temp tables. Maybe I am not expert enough to interpret this output, so here it is - mysql explain select * from T1, T2 where AAA=BBB AND ZZZ like '%R%'; ++--+---+--+-+--+--- ---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+--- ---+-+ | T1 | ALL | NULL | NULL |NULL | NULL | 400 | | | T2 | ALL | NULL | NULL |NULL | NULL | 6591 | Using where | ++--+---+--+-+--+--- ---+-+ 2 rows in set (0.08 sec) mysql explain select * from T1, T2 where ZZZ like '%X%' AND AAA=BBB; ++--+---+--+-+--+--- ---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+--- ---+-+ | T1 | ALL | NULL | NULL |NULL | NULL | 400 | | | T2| ALL | NULL | NULL |NULL | NULL | 6591 | Using where | ++--+---+--+-+--+--- ---+-+ 2 rows in set (0.00 sec) Any inputs??? TIA, - Manish - Original Message - Sent: Saturday, December 04, 2004 10:26 AM Subject: Re: Help me optimize this query If you change the update to a select you can use explain to see it's execution path just like you would with a normal select. The rows returned are the rows that would be matched by the update query. Example: explain select * from t1, t2 where t1.YYY=t2. and t2. like '%X%'; Optimize the query then turn it back into an update. -Eric On Wed, 1 Dec 2004 11:38:29 +0530, Manish [EMAIL PROTECTED] wrote: I am trying to execute this query and it is failing with Table is full error (I know I can make temp tables big). update t1, t2 set t1.XXX=1 where t1.YYY=t2. and t2. like '%X%'; My t1 has 10,00,000+ records and t2 has about 70,000 recorsds. I would like to know how can I optimize this query? What are the parmeters for this optimization? Can someone give me links where I can read up about such optimizations for update query. TIA, - Manish -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] bleated.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me optimize this query
If you change the update to a select you can use explain to see it's execution path just like you would with a normal select. The rows returned are the rows that would be matched by the update query. Example: explain select * from t1, t2 where t1.YYY=t2. and t2. like '%X%'; Optimize the query then turn it back into an update. -Eric On Wed, 1 Dec 2004 11:38:29 +0530, Manish [EMAIL PROTECTED] wrote: I am trying to execute this query and it is failing with Table is full error (I know I can make temp tables big). update t1, t2 set t1.XXX=1 where t1.YYY=t2. and t2. like '%X%'; My t1 has 10,00,000+ records and t2 has about 70,000 recorsds. I would like to know how can I optimize this query? What are the parmeters for this optimization? Can someone give me links where I can read up about such optimizations for update query. TIA, - Manish -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] bleated.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP ME WITH THIS
Dear Dobromir Velev and members of list, Thank you so much for the help, yes the sql query works perfectly, though i did add some, as to cancel the duplicates ones, and also to not to take into negative timings, please advice on this too, select SEC_TO_TIME(unix_timestamp(concat(e.Date,' ',e.Time)) - unix_timestamp(concat(s.Date,' ',s.Time))) as time, s.CallingStationId, s.CalledStationId, s.Date, s.Time, s.AcctStatusType, e.Date, e.Time, e.AcctStatusType from VOIP s left join VOIP e on ( s.CallingStationId=e.CallingStationId and s.CalledStationId=e.CalledStationId and e.AcctStatusType='Stop' ) where s.AcctStatusType='Start' and s.Time e.Time and s.Date = e.Date group by s.Time,e.CallingStationId order by s.date,s.time; Thanks, Karma - Original Message - From: Dobromir Velev [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, September 03, 2004 6:48 PM Subject: Re: HELP ME WITH THIS Hi, The only way I can think of is to join the table to itself. It should look something like this: select unix_timestamp(concat(s.date,' ',s.time)) - unix_timestamp(concat(e.date,' ',e.time)) from table s left join table e on ( s.CallingStationId=e.CallingStationId and s.CalledStationId=e.CalledStationId and e.AcctStatusType='Stop' ) where s.AcctStatusType='Start' order by s.date,s.time; I haven't tested it so you will probably need to play a bit with the date/time formating but it should be enough to give you the idea. It will only work if there is only one session for each CallingStationId and CalledStationId pair, otherwise you will receive a lot of irrelevant results. -- Dobromir Velev On Friday 03 September 2004 15:21, Peter J Milanese wrote: If it were all in one row, you may be able to compare datetime fields. I do not know if you can do this with 2 rows, and the query will probably be rough. Did you design the table? Can you create it so that your row has start and stop times, instead of creating another row? -Original Message- From: Karma Dorji [mailto:[EMAIL PROTECTED] Sent: Friday, September 03, 2004 5:06 AM To: [EMAIL PROTECTED] Subject: HELP ME WITH THIS Hello can anyone help me with this, i have a table, like the one below, i need to find the time difference between the Start and Stop from a particular CallingStationId to particular CalledStationId. ++--++---+ -- +---+ | Date | Time | CallingStationId | CalledStationId | AcctStatusType | AcctSessionTime | ++--++---+ -- +---+ | 09/01/2004 | 17:28:27 | 02327125 | 00018151635 | Start | | 09/01/2004 | 19:00:34 | 02320176 | 01181471822125| Start | | 09/01/2004 | 19:10:08 | 17113080 | 01022586815 | Start 09/01/2004 | 20:28:27 | 02327125 | 00018151635 | Sop | 09/01/2004 | 21:00:34 | 02320176 | 01181471822125| Stop | | 09/01/2004 | 22:10:08 | 17113080 | 01022586815 | Stop Thanking you all in advance. Karma -- 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: HELP ME WITH THIS
If it were all in one row, you may be able to compare datetime fields. I do not know if you can do this with 2 rows, and the query will probably be rough. Did you design the table? Can you create it so that your row has start and stop times, instead of creating another row? -Original Message- From: Karma Dorji [mailto:[EMAIL PROTECTED] Sent: Friday, September 03, 2004 5:06 AM To: [EMAIL PROTECTED] Subject: HELP ME WITH THIS Hello can anyone help me with this, i have a table, like the one below, i need to find the time difference between the Start and Stop from a particular CallingStationId to particular CalledStationId. ++--++---+ -- +---+ | Date | Time | CallingStationId | CalledStationId | AcctStatusType | AcctSessionTime | ++--++---+ -- +---+ | 09/01/2004 | 17:28:27 | 02327125 | 00018151635 | Start | | | 09/01/2004 | 19:00:34 | 02320176 | 01181471822125| Start | | | 09/01/2004 | 19:10:08 | 17113080 | 01022586815 | Start | | 09/01/2004 | 20:28:27 | 02327125 | 00018151635 | Sop | | | 09/01/2004 | 21:00:34 | 02320176 | 01181471822125| Stop | | | 09/01/2004 | 22:10:08 | 17113080 | 01022586815 | Stop | | Thanking you all in advance. Karma -- 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 ME WITH THIS
* Karma Dorji
i have a table, like the one below,
i need to find the time difference between the Start and Stop from a
particular CallingStationId to particular CalledStationId.
++--++---+
--
+---+
| Date | Time | CallingStationId | CalledStationId |
AcctStatusType | AcctSessionTime |
++--++---+
--
+---+
| 09/01/2004 | 17:28:27 | 02327125 | 00018151635 | Start
| |
| 09/01/2004 | 19:00:34 | 02320176 | 01181471822125| Start
| |
| 09/01/2004 | 19:10:08 | 17113080 | 01022586815 | Start
| |
09/01/2004 | 20:28:27 | 02327125 | 00018151635 | Sop
| |
| 09/01/2004 | 21:00:34 | 02320176 | 01181471822125| Stop
| |
| 09/01/2004 | 22:10:08 | 17113080 | 01022586815 | Stop
| |
First you need to pair the rows:
SELECT t1.Time,t2.Time
FROM tab t1,tab t2
WHERE
t1.CallingStationId = t2.CallingStationId AND
t1.CalledStationId = t2.CalledStationId AND
t1.AcctStatusType = 'Start' and
t2.AcctStatusType = 'Stop'
This should work with your example data, if you have duplicate combinations
of CallingStationId/CalledStationId/AcctStatusType you will also need to
check the date and time.
To calculate the time difference you could transform the time string to
seconds, and then just subtract. This will give you the answer in seconds,
but it will only work if your dates are valid mysql dates, in the format
-MM-DD:
SELECT unix_timestamp(concat(t2.Date,' ',t2.Time)) -
unix_timestamp(concat(t1.Date,' ',t1.Time))
FROM tab t1,tab t2
WHERE
t1.CallingStationId = t2.CallingStationId AND
t1.CalledStationId = t2.CalledStationId AND
t1.AcctStatusType = 'Start' and
t2.AcctStatusType = 'Stop'
If the date is irrelevant (allways same day) you can use any date:
SELECT unix_timestamp(concat('2004-09-01 ',t2.Time)) -
unix_timestamp(concat('2004-09-01 ',t1.Time))
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP ME WITH THIS
Hi, The only way I can think of is to join the table to itself. It should look something like this: select unix_timestamp(concat(s.date,' ',s.time)) - unix_timestamp(concat(e.date,' ',e.time)) from table s left join table e on ( s.CallingStationId=e.CallingStationId and s.CalledStationId=e.CalledStationId and e.AcctStatusType='Stop' ) where s.AcctStatusType='Start' order by s.date,s.time; I haven't tested it so you will probably need to play a bit with the date/time formating but it should be enough to give you the idea. It will only work if there is only one session for each CallingStationId and CalledStationId pair, otherwise you will receive a lot of irrelevant results. -- Dobromir Velev On Friday 03 September 2004 15:21, Peter J Milanese wrote: If it were all in one row, you may be able to compare datetime fields. I do not know if you can do this with 2 rows, and the query will probably be rough. Did you design the table? Can you create it so that your row has start and stop times, instead of creating another row? -Original Message- From: Karma Dorji [mailto:[EMAIL PROTECTED] Sent: Friday, September 03, 2004 5:06 AM To: [EMAIL PROTECTED] Subject: HELP ME WITH THIS Hello can anyone help me with this, i have a table, like the one below, i need to find the time difference between the Start and Stop from a particular CallingStationId to particular CalledStationId. ++--++---+ -- +---+ | Date | Time | CallingStationId | CalledStationId | AcctStatusType | AcctSessionTime | ++--++---+ -- +---+ | 09/01/2004 | 17:28:27 | 02327125 | 00018151635 | Start | | 09/01/2004 | 19:00:34 | 02320176 | 01181471822125| Start | | 09/01/2004 | 19:10:08 | 17113080 | 01022586815 | Start 09/01/2004 | 20:28:27 | 02327125 | 00018151635 | Sop | 09/01/2004 | 21:00:34 | 02320176 | 01181471822125| Stop | | 09/01/2004 | 22:10:08 | 17113080 | 01022586815 | Stop Thanking you all in advance. Karma -- 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 me!!!how to write such sql in mysql?
xian ting [EMAIL PROTECTED] wrote: in sql: insert into table select from ... can select from a remote data base server such as sqlserver using odbc as 'SELECT * into test01 FROM Fund IN [ODBC] [ODBC;Driver=SQL Server;UID=admin;PWD=;Server=(lacal);DataBase=CMBXMDM;]'in access. thanks! You can't do it. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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]
Re: Help me - please
i added ulimit -n 1024 in mysql_install_db, i can't to install. Appear the error again. Please again!!! On Thu, 18 Dec 2003, Chris Elsworth wrote: On Thu, Dec 18, 2003 at 05:28:30PM -0200, Carlos Andr? Moura de Amorim wrote: 031217 14:32:34 Warning: setrlimit couldn't increase number of open files to more than 256 (request: 510) It might be worth putting a ulimit -n 1024 (or some other decent number) in the rc.d script that starts mysql; then (assuming the kernel is going to allow it) mysql will be able to change the number of file descriptors it can open, to the best of my knowledge. -- 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: Help me - please
You must be 'root' to do a ulimit -n 1024. Marc. -Message d'origine- De : Carlos André Moura de Amorim [mailto:[EMAIL PROTECTED] Envoyé : vendredi 19 décembre 2003 14:55 À : Chris Elsworth Cc : [EMAIL PROTECTED] Objet : Re: Help me - please i added ulimit -n 1024 in mysql_install_db, i can't to install. Appear the error again. Please again!!! On Thu, 18 Dec 2003, Chris Elsworth wrote: On Thu, Dec 18, 2003 at 05:28:30PM -0200, Carlos Andr? Moura de Amorim wrote: 031217 14:32:34 Warning: setrlimit couldn't increase number of open files to more than 256 (request: 510) It might be worth putting a ulimit -n 1024 (or some other decent number) in the rc.d script that starts mysql; then (assuming the kernel is going to allow it) mysql will be able to change the number of file descriptors it can open, to the best of my knowledge. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me - please
what distro are you using? what is running on this server? another server is runnin' on the same machine? what hardware are you using? is it heavy loaded? On Fri, 2003-12-19 at 09:54, Carlos Andr Moura de Amorim wrote: i added ulimit -n 1024 in mysql_install_db, i can't to install. Appear the error again. Please again!!! On Thu, 18 Dec 2003, Chris Elsworth wrote: On Thu, Dec 18, 2003 at 05:28:30PM -0200, Carlos Andr? Moura de Amorim wrote: 031217 14:32:34 Warning: setrlimit couldn't increase number of open files to more than 256 (request: 510) It might be worth putting a ulimit -n 1024 (or some other decent number) in the rc.d script that starts mysql; then (assuming the kernel is going to allow it) mysql will be able to change the number of file descriptors it can open, to the best of my knowledge. -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- .. * _ _ __ __ .. * \ \ \ | | __ \ /\ | | || Victor E Medina M * \ \ \ | |__ | |__) / \ | | || Linux - Java - MySQL * | __| | ___/ /\ \ | | || Dpto. Sistemas - Ferreteria EPA * / / / | || | / \|_| || www.superferreteria.com.ve * /_/_/ |__|_| /_/\_(_) || [EMAIL PROTECTED] * || geek by nature - linux by choice .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help me - please
i'm root!! *** Carlos Andre Moura de Amorim -- Funcionario UNCISAL -- Tecnico de Informatica CEFET-AL -- Acad. de Mecicina UNCISAL -- Fone: (0xx82) 9904-0117 *** On Fri, 19 Dec 2003, Mechain Marc wrote: You must be 'root' to do a ulimit -n 1024. Marc. -Message d'origine- De : Carlos André Moura de Amorim [mailto:[EMAIL PROTECTED] Envoyé : vendredi 19 décembre 2003 14:55 À : Chris Elsworth Cc : [EMAIL PROTECTED] Objet : Re: Help me - please i added ulimit -n 1024 in mysql_install_db, i can't to install. Appear the error again. Please again!!! On Thu, 18 Dec 2003, Chris Elsworth wrote: On Thu, Dec 18, 2003 at 05:28:30PM -0200, Carlos Andr? Moura de Amorim wrote: 031217 14:32:34 Warning: setrlimit couldn't increase number of open files to more than 256 (request: 510) It might be worth putting a ulimit -n 1024 (or some other decent number) in the rc.d script that starts mysql; then (assuming the kernel is going to allow it) mysql will be able to change the number of file descriptors it can open, to the best of my knowledge. -- 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] -- 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 me - please
On Thu, Dec 18, 2003 at 05:28:30PM -0200, Carlos Andr? Moura de Amorim wrote: 031217 14:32:34 Warning: setrlimit couldn't increase number of open files to more than 256 (request: 510) It might be worth putting a ulimit -n 1024 (or some other decent number) in the rc.d script that starts mysql; then (assuming the kernel is going to allow it) mysql will be able to change the number of file descriptors it can open, to the best of my knowledge. -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me pls
You may be better asking this one on the PHP list: http://www.php.net/mailing-lists.php But you will need a bit more detail such as your version/setup/platform and some code (without password/username that is!) On 6/26/03 2:12 PM, Syamsul Arifien [EMAIL PROTECTED] wrote: Date: Thu, 26 Jun 2003 18:02:23 + To: [EMAIL PROTECTED] From: Syamsul Arifien Subject: Help me pls Hi ... I hv problem... I can't conec to my MysqlSvr with mysql_connect() in my php script.. anybody knows? best regard Syamsul Arifien -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me: I'm a beginner
On Thu, Jun 26, 2003 at 08:07:48AM -0400, David Bush wrote: I run the following query: select * from x where x like 'x/%' ; I would like the result of this query to go to a file or pause at the end of the screen so that I can view each page. Any help would be appreciated. mysql pager /usr/bin/less mysql tee somefile.log I guess this is documented somewhere, or try: mysql help Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me: I'm a beginner
- Original Message - From: Fred van Engen [EMAIL PROTECTED] To: David Bush [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, June 26, 2003 3:11 PM Subject: Re: Help me: I'm a beginner On Thu, Jun 26, 2003 at 08:07:48AM -0400, David Bush wrote: I run the following query: select * from x where x like 'x/%' ; I would like the result of this query to go to a file or pause at the end of the screen so that I can view each page. Any help would be appreciated. try using the into outfile syntax after your select...(see manual/ask me for an example if needed)... aik_b -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me: I'm a beginner
David Bush [EMAIL PROTECTED] wrote: I run the following query: select * from x where x like 'x/%' ; I would like the result of this query to go to a file or pause at the end of the screen so that I can view each page. Any help would be appreciated. Use SELECT .. INTO OUTFILE command: http://www.mysql.com/doc/en/SELECT.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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]
RE: Help me!!!
mailto:[EMAIL PROTECTED] This is what I found at lists.mysql.com Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Luis Enrique Bauzá Peña [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 10:00 AM To: Lista Mysql Inglés Subject: Help me!!! Hi, I need some link to a spanish mysql list, would you ...? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help me!!!
Luis Enrique Bauzá Peña, [EMAIL PROTECTED] David M Friscia friscia.rootsweb.com http://friscia.rootsweb.com [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- From: Luis Enrique Bauzá Peña [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 9:00 AM To: Lista Mysql Inglés Subject: Help me!!! Hi, I need some link to a spanish mysql list, would you ...? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me!!!
mailto:[EMAIL PROTECTED] - Original Message - From: Luis Enrique Bauzá Peña [EMAIL PROTECTED] To: Lista Mysql Inglés [EMAIL PROTECTED] Sent: Thursday, June 19, 2003 11:59 AM Subject: Help me!!! Hi, I need some link to a spanish mysql list, would you ...? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me!!!
Hola, Puedes encontrar una lista Mysql Espanol a http://lists.mysql.com/list.php?list=mysql-es#b . Yo pienso que ellos pueden ayudarte ma's que nosotros podemos. You can find a spanish MySQL list at http://lists.mysql.com/list.php?list=mysql-es#b . I think they can help you more than we can. -- Michael Conlen Luis Enrique Bauzá Peña wrote: Hi, I need some link to a spanish mysql list, would you ...? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: help me, please
select * from your_table order by no desc limit 0,1 -Original Message- From: mustakim abas [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 12:07 PM To: [EMAIL PROTECTED] Subject: help me, please Hello, i am new in mysql. I got a problem. I try write my C program with mysql API.I have 5 field in table.No,Name,Phone,Date,Time. How can i take one row where the No is bigger? Thanks for help. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.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: help me, please
On Thu, 19 Dec 2002, mustakim abas wrote: Hello, i am new in mysql. I got a problem. I try write my C program with mysql API.I have 5 field in table.No,Name,Phone,Date,Time. How can i take one row where the No is bigger? Thanks for help. Provided I understand your question, then I would suggest: SELECT * FROM mytable WHERE No somevalue LIMIT 1; Then 'somevalue' would be the value of 'No' from your last query. Thomas -- sql,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
Re: help me, please
At 8:06 -0800 12/19/02, mustakim abas wrote: Hello, i am new in mysql. I got a problem. I try write my C program with mysql API.I have 5 field in table.No,Name,Phone,Date,Time. How can i take one row where the No is bigger? Thanks for help. Bigger than what? - 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: help me, please
Select max(No) from table Marcos Henke - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: mustakim abas [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, December 19, 2002 1:51 PM Subject: Re: help me, please At 8:06 -0800 12/19/02, mustakim abas wrote: Hello, i am new in mysql. I got a problem. I try write my C program with mysql API.I have 5 field in table.No,Name,Phone,Date,Time. How can i take one row where the No is bigger? Thanks for help. Bigger than what? - 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: help me, please
At 8:06 -0800 12/19/02, mustakim abas wrote: Hello, i am new in mysql. I got a problem. I try write my C program with mysql API.I have 5 field in table.No,Name,Phone,Date,Time. How can i take one row where the No is bigger? Thanks for help. Bigger than what? Maybe SELECT No, Name, Phone, Date, Time FROM tablename ORDER BY No DESC LIMIT 1 - 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: Help me optimize my query
* Maximo Migliari Im running the following query: SELECT user.nickname, user.id, user_detail.points FROM user, user_detail WHERE user.details = user_detail.id AND user.id 101 AND user.language = 'en' ORDER BY user_detail.points DESC LIMIT 5; [...] This is the result of the EXPLAIN for the query: +-+---+-+-+--- --++---+-+ | table | type | possible_keys | key | key_len | ref| rows | Extra | +-+---+-+-+--- --++---+-+ | user_detail | range | PRIMARY,id,points | points | 4 | NULL | 15567 | where used; Using index; Using filesort | | user| ref | PRIMARY,id,details,language | details | 4 | user_detail.id | 1 | where used | +-+---+-+-+--- --++---+-+ Someone told me that Using filesort is not a good sign... how can I improve the performace of this query? Any other tuning suggestions? The EXPLAIN shows that the 'user_detail' table is read first, using the 'points' index. Approximately 15567 rows must be examined, and then the 'details' index of the 'user' table is used to do a lookup for each 'user_detail'. Try using STRAIGHT_JOIN: SELECT user.nickname, user.id, user_detail.points FROM user STRAIGHT_JOIN user_detail WHERE user.details = user_detail.id AND user.id 101 AND user.language = 'en' ORDER BY user_detail.points DESC LIMIT 5; This will force mysql to read the user table first, probably using the 'language' index or the 'id' index, and then do a lookup on the 'user_detail' table. Because both tables have the approximate same number of records (32.000) in this case, and the criteria id 101 AND language = 'en' probably matches most of the users, it may not help much. But it is worth a try. If most of the rows in the 'user_detail' table are relavant to this query (i.e. few users with id = 101 and/or language'en') you may want to try a different approach: select the 10 (or so) highest points from 'user_details' first, then join with the 'user' table in a separate query: CREATE TEMPORARY TABLE tmp1 SELECT user_detail.id, user_detail.points FROM user_detail ORDER BY points DESC LIMIT 10; SELECT user.nickname, user.id, tmp1.points FROM tmp1 STRAIGHT_JOIN user WHERE user.details = tmp1.id AND user.id 101 AND user.language = 'en' ORDER BY points DESC LIMIT 5; HTH, -- Roger - 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: Help me optimize my query
Im running the following query: SELECT user.nickname, user.id, user_detail.points FROM user, user_detail WHERE user.details = user_detail.id AND user.id 101 AND user.language = 'en' ORDER BY user_detail.points DESC LIMIT 5; Try: SELECT ... FROM user LEFT JOIN user_detail ON user_detail.id = user.details WHERE user.id 101 AND user.language = 'en' ... Make sure you index user.details ... -- Michael T. Babcock CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc) http://www.fibrespeed.net/~mbabcock/ - 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: Help me optimize my query
Im running the following query: SELECT user.nickname, user.id, user_detail.points FROM user, user_detail WHERE user.details = user_detail.id AND user.id 101 AND user.language = 'en' ORDER BY user_detail.points DESC LIMIT 5; I don't really know how LEFT JOINS, INNER JOINS and so on work, but I want to increase the performance of the query. This is the result of the EXPLAIN for the query: if you would like to learn more about queries and if you have a windows box for a front end, download corereader from http://corereader.com it will let you quickly do and re-do join queries just by clicking on your database objects, so you can concentrate on developing logic without worrying about syntax. when you get the query that you like, copy the sql code into your app. -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.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: Help me optimize my query
John Ragan wrote: Im running the following query: SELECT user.nickname, user.id, user_detail.points FROM user, user_detail WHERE user.details = user_detail.id AND user.id 101 AND user.language = 'en' ORDER BY user_detail.points DESC LIMIT 5; I don't really know how LEFT JOINS, INNER JOINS and so on work, but I want to increase the performance of the query. This is the result of the EXPLAIN for the query: if you would like to learn more about queries and if you have a windows box for a front end, download corereader from http://corereader.com it will let you quickly do and re-do join queries just by clicking on your database objects, so you can concentrate on developing logic without worrying about syntax. when you get the query that you like, copy the sql code into your app. I don't think that's exactly what he was after. Please read the question before spamming the list with advertising. -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.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: help me out here guys... you gotta have a primary key
Hi Eric I dunno why people make 100 field tables, sounds a little harsh. I'll keep you in mind the next time i wanna make a 100 field table. Anyways, just to let you know mysql 4 will be supporting foreign key constraints. And yes thoes other db technologies like postGrep already support key constraints, but i thought (and correct me if i'm wrong) that mysql just performs a little better (maybe because of having no key constraints). But regardless of mysql not supporting foreign keys, i still put them in the defenitions of my tables because i believe it's good for documentation and also if we upgrade to mysql 4 things will be less painful when updating tables to use foreign keys. Is it such a big deal to use more than one field for a primary key? Two field keys are only a little slower than single field Primary keys for selects. Often in linking tables you want to have duplicates of the two foreign keys right? Not quite sure what you mean by having duplicates of a foreign key. I'm guessing you mean something like this in a linking table: Linking table __ authorID, bookID Author table _ authorID, firsname, lastname, ... Book table ___ bookID, name, pubDate, Thus the linking table will have a primary key of authorID and the bookID to uniquely id the row in the linking table. However, you can have this: LInking table _ 6, 1 7, 1 Author table _ 6, Puff, Daddy, ... 7, keanu, reves, ... Book table ___ 1, Bad acting and rapping cross over, 2000, Thus, the book Bad acting and rapping cross over is written by Both Puff and Keanu. Therefore you have multiple entries of the book with id 1 in the table. It appears 2 times on 2 different rows, but 2 times within the same column. (My apologies to keanu and puffy fans out there). Good luck with the 100 field tables, and thanks for your reply and all the other replies out there for this message. I'm glad to know that i'm not the only one that thinks having no primary key is NOT a good idea. I'll be sure to give my collegue a mouthful if he bitches about primary keys again. Thanks Desmond _ Chat with friends online, try MSN Messenger: http://messenger.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
Re: help me out here guys... you gotta have a primary key
So, one of my associates has made a linking table (some people also call it intersection table, cross tab table, but i believe that the propper way to model a many to many relationship is via a linking table). in the linking table, there is no primary key defined. Let us suppose you wish to define a many-to-many relationship between people and telephones (and it IS a many-to-many relationship: most people have at least a home and a work phone (and some also have a home cellphone and a work cellphone), and most families do not have a separate phone for each family member). You have a 'people' table with names, birth dates, employee number, etc. The primary key is the employee number (id int not null auto_increment). (Hint: Social Security numbers are NOT guaranteed unique, even if everyone relevant has one.) You have a 'telephone' table with a telephone number, type (landline or cellular), owner (company or someone else), location, and an artificial primary key (id int not null auto_increment). Why not use the telephone number as primary key? Some extensions, such as the one in the lobby for guests, or the ones in elevators, can't take incoming calls and don't HAVE numbers in the conventional sense. So how do you represent the relationship? Add another table (linking table is a reasonable description) containing two columns, the primary key of the person and the primary key of the telephone. So what is a suitable primary key for THIS table? It's a key on both columns. The same person can't have the same telephone more than once, so requiring uniqueness prevents multiple identical rows that don't make any sense. Should the key be (person_id, telephone_id) or (telephone_id, person_id)? This depends on which index you anticipate the SQL query to need most often. Now, to those who like bringing up the foreign keys as some sort of alternative: I don't believe it. Having foreign keys gets you referential integrity. It does NOT eliminate the need for the linking table, nor does it change what's in the table, to represent a many-to-many relationship. I challenge anyone to demonstrate otherwise. I believe that every table must have a primary key. It is absolutely essential, otherwise you'll get tons of problems including redundancy, and inconsistency. Yes, you could record the relationship between me and my work telephone several times, if the primary key didn't prevent that. I don't believe you have to have a SINGLE-FIELD primary key on every table. However, my associate believes that our coding will ensure that such problems will be avoided and that it's okay for a table to have no primary key defined. I totally disagree. Even if our code is perfect, a primary key must be defined. So, am i correct in being concerned, or am i just being close minded? If, i'm totally wrong, in what situations is it a good idea, okay, or benificial to not have a primary key defined for a table? I can't think of a good reason to not have a primary key. Gordon L. Burditt - 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: help me out here guys... you gotta have a primary key
Desmond, A primary key is needed to maintain a unique identity of each record and there by help in linking it to other records in other tables. However, we have certain tables which link one table to another (needed because of absence of foreign keys in mysql) which don't necessarily have primary keys. If you know that a column can have only unique records you are better off describing it as unique or primary key, not depending upon your code to always do that because there is a good chance that your code can have bugs and then all your data might turn out to be invalid. So the bottom line is that you don't *need* a primary key but if u know that a column will be unique then, do define it as unique or primary. Primary keys will certainly help in reducing redundant data as long as they have some meaning (Social security number, telephone number, etc). Bhavin. - Original Message - From: Desmond Lee [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, July 27, 2002 7:26 PM Subject: help me out here guys... you gotta have a primary key k This is seems ridiculous to me,.. but i'll let you tell me if i'm just being stubborn. So, one of my associates has made a linking table (some people also call it intersection table, cross tab table, but i believe that the propper way to model a many to many relationship is via a linking table). in the linking table, there is no primary key defined. I believe that every table must have a primary key. It is absolutely essential, otherwise you'll get tons of problems including redundancy, and inconsistency. However, my associate believes that our coding will ensure that such problems will be avoided and that it's okay for a table to have no primary key defined. I totally disagree. Even if our code is perfect, a primary key must be defined. So, am i correct in being concerned, or am i just being close minded? If, i'm totally wrong, in what situations is it a good idea, okay, or benificial to not have a primary key defined for a table? Thanks Desmond (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: help me out here guys... you gotta have a primary key
Hello Desmond, I am not an expert in DB, but I support your ideas. The primery key is one of the fundamental concept of database. With the key, you can refer faster to the record that you are looking for. Even you have a perfect codes, you will be not able to cover all senario that your software to to deal with. May suggest your partner to do some research on database design ! Good luck ! /Khanh Desmond Lee wrote: k This is seems ridiculous to me,.. but i'll let you tell me if i'm just being stubborn. So, one of my associates has made a linking table (some people also call it intersection table, cross tab table, but i believe that the propper way to model a many to many relationship is via a linking table). in the linking table, there is no primary key defined. I believe that every table must have a primary key. It is absolutely essential, otherwise you'll get tons of problems including redundancy, and inconsistency. However, my associate believes that our coding will ensure that such problems will be avoided and that it's okay for a table to have no primary key defined. I totally disagree. Even if our code is perfect, a primary key must be defined. So, am i correct in being concerned, or am i just being close minded? If, i'm totally wrong, in what situations is it a good idea, okay, or benificial to not have a primary key defined for a table? Thanks Desmond (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: help me out here guys... you gotta have a primary key
Well, there are people who feel that tables should be linked by foreign keys to ensure referential integrity. Everyone who uses MySQL gets by without them though. Its up to your coding to make sure referential integrity is not violated. Although foreign keys are left out of MySQL for performance reasons, and there is ussualy not much performance hit for having a primary key and most of the time there is probably a performance gain. So I think generally a primary key is a good thing, unless you have a strange situation where for performance reasons (speed or size) a primary key doesn't make sense. Dave -Original Message- From: Desmond Lee [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 27, 2002 9:27 PM To: [EMAIL PROTECTED] Subject: help me out here guys... you gotta have a primary key k This is seems ridiculous to me,.. but i'll let you tell me if i'm just being stubborn. So, one of my associates has made a linking table (some people also call it intersection table, cross tab table, but i believe that the propper way to model a many to many relationship is via a linking table). in the linking table, there is no primary key defined. I believe that every table must have a primary key. It is absolutely essential, otherwise you'll get tons of problems including redundancy, and inconsistency. However, my associate believes that our coding will ensure that such problems will be avoided and that it's okay for a table to have no primary key defined. I totally disagree. Even if our code is perfect, a primary key must be defined. So, am i correct in being concerned, or am i just being close minded? If, i'm totally wrong, in what situations is it a good idea, okay, or benificial to not have a primary key defined for a table? Thanks Desmond (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: help me out here guys... you gotta have a primary key
Hi, Is it such a big deal to use more than one field for a primary key? Two field keys are only a little slower than single field Primary keys for selects. Often in linking tables you want to have duplicates of the two foreign keys right? I just wish people would stop making 100 field tables, then I would be happy. Ever do an insert on a 78 row table that has all fields set to NOT NULL, yet you only need to insert about 15 columns worth of data? It isn't pretty.. And BTW, do people not ever read the mySQL site? Ever heard of InnoDB, Berkely DB? Yes, foreign keys, even cascade deletes. Is this going to become one of those Perl sucks because it is CGI kind of things? mySQL truly rocks, it keeps getting better and better. Actualy I started a project recently where I thought I would go ahead be a good boy, and use foreign keys, it ends up that I just can't think of a good reason for it in my particular situation. I was kind of disappointed really.. But I am very happy to know that at any time, if I want transactions and foreign keys, it is a few keystrokes away. Eric PS why oh why do people make 100 field tables! At 11:35 PM 7/27/02 -0500, Dave Dutcher wrote: Well, there are people who feel that tables should be linked by foreign keys to ensure referential integrity. Everyone who uses MySQL gets by without them though. Its up to your coding to make sure referential integrity is not violated. Although foreign keys are left out of MySQL for performance reasons, and there is ussualy not much performance hit for having a primary key and most of the time there is probably a performance gain. So I think generally a primary key is a good thing, unless you have a strange situation where for performance reasons (speed or size) a primary key doesn't make sense. Dave -Original Message- From: Desmond Lee [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 27, 2002 9:27 PM To: [EMAIL PROTECTED] Subject: help me out here guys... you gotta have a primary key k This is seems ridiculous to me,.. but i'll let you tell me if i'm just being stubborn. So, one of my associates has made a linking table (some people also call it intersection table, cross tab table, but i believe that the propper way to model a many to many relationship is via a linking table). in the linking table, there is no primary key defined. I believe that every table must have a primary key. It is absolutely essential, otherwise you'll get tons of problems including redundancy, and inconsistency. However, my associate believes that our coding will ensure that such problems will be avoided and that it's okay for a table to have no primary key defined. I totally disagree. Even if our code is perfect, a primary key must be defined. So, am i correct in being concerned, or am i just being close minded? If, i'm totally wrong, in what situations is it a good idea, okay, or benificial to not have a primary key defined for a table? Thanks Desmond (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 http://www.kwinternet.com/eric (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant - 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: help me out here guys... you gotta have a primary key
If it's for a linking table, you'd just have something like this: CREATE TABLE linking_table ( first_id INTEGER, second_id INTEGER, PRIMARY KEY (first_id, second_id) ); For an n-m relationship, that's the only way to do it. Dean Harding. P.S. The syntax may be wrong, that's just off the top of my head, but you get the idea - a primary key doesn't have to be on one field only, it can be on as many as you need. -Original Message- From: Desmond Lee [mailto:[EMAIL PROTECTED]] Sent: Sunday, 28 July 2002 12:27 pm To: [EMAIL PROTECTED] Subject: help me out here guys... you gotta have a primary key k This is seems ridiculous to me,.. but i'll let you tell me if i'm just being stubborn. So, one of my associates has made a linking table (some people also call it intersection table, cross tab table, but i believe that the propper way to model a many to many relationship is via a linking table). in the linking table, there is no primary key defined. I believe that every table must have a primary key. It is absolutely essential, otherwise you'll get tons of problems including redundancy, and inconsistency. However, my associate believes that our coding will ensure that such problems will be avoided and that it's okay for a table to have no primary key defined. I totally disagree. Even if our code is perfect, a primary key must be defined. So, am i correct in being concerned, or am i just being close minded? If, i'm totally wrong, in what situations is it a good idea, okay, or benificial to not have a primary key defined for a table? Thanks Desmond (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 mysql-unsubscribe- [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: help me with this query
Hi. IMHO, the self-join is the correct solution for the given table layout (which looks reasonable to me). Greetings, Benjamin. On Wed 2002-07-17 at 09:26:48 +0530, [EMAIL PROTECTED] wrote: [...] Select companyid, count(*) from CompanyServices where serviceid = 1 or serviceid = 2 or serviceid = 3 group by companyid having count(*) = 3 Bye and Good Luck. --- Arul [EMAIL PROTECTED] wrote: Hi All The Table design goes like this Company Table companyid integer name varchar(100) Services Table ServiceIDinteger Servicevarchar(50) Company Services --- companyidinteger - References Company(companyid) serviceid integer - References Services(ServiceID) [...] select distinct(c.companyid) , c.name from company c , companyservices cs1, companyservices cs2, companyservices cs3 where cs1.serviceid = 1 AND cs2.serviceid = 2 AND cs3.serviceid = 3 AND c.companyid = cs1.companyid Is there any other way , i could achive the result without using a self join [...] -- [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
Re: help me with this query
See comments below... -- Arul [EMAIL PROTECTED] wrote: Hi I Dont think this will work coz I need the company which has serviceid = 1 AND serviceid = 2 and serviceid = 3 I think that you are mixing natural language with logical expression, right? The expression above always returns FALSE, that is, no record will be returned. So let's take this Query , Select companyid, count(*) from CompanyServices where serviceid = 1 or serviceid = 2 or serviceid = 3 group by companyid having count(*) = 3 If i have another company say companyid = 5 ,who has serviceid = 1 AND serviceid = 6 AND serviceid = 7 , even this company will be retrieved which will be against the search criteria. This company won't be retrieve because just services 1. Look the query again Arul, we are filtering the records: serviceid = 1 or serviceid = 2 or serviceid = 3 Only records with serviceid 1, 2, or 3 will be returned in the query. Then we are grouping to count the records for each group, and finally (with the HAVING COUNT), we are making sure that only company with the three services are shown. Have you really tried this query? Because I tried and it worked unless I misunderstood what you want to do. Also another company whose companyid = 7 has serviceid = 1 AND serviceid = 2 AND serviceid = 3 AND serviceid = 5 .This company will not be retrieved since the count is not 3.But by search criteria , this company should be retrieved. The count for this company will be 3 because the same reason as above. Look this simple table: id1,id2 1,1 1,2 1,3 2,3 3,4 3,5 3,6 Select * from tblService where id2 = 1 or id2 = 2 or id2 = 3 will return: id1,id2 1,1 1,2 1,3 2,3 Select id1, count(*) from tblService where id2 = 1 or id2 = 2 or id2 = 3 group by id1 returns: id1,count(*) 1,3 2,1 and finally Select id1, count(*) from tblService where id2 = 1 or id2 = 2 or id2 = 3 group by id1 having count(*) = 3 id1,count(*) 1,3 Only and only id1 (company) 1 services the three service type. Let me know if this is not what you wanted. Bye and Good Luck. So how do we handle these situations -Arul - Original Message - From: Francisco Reinaldo [EMAIL PROTECTED] To: Arul [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Cc: Arul [EMAIL PROTECTED] Sent: Wednesday, July 17, 2002 3:27 AM Subject: Re: help me with this query Hi, Select companyid, count(*) from CompanyServices where serviceid = 1 or serviceid = 2 or serviceid = 3 group by companyid having count(*) = 3 Bye and Good Luck. --- Arul [EMAIL PROTECTED] wrote: Hi All The Table design goes like this Company Table companyid integer name varchar(100) Services Table ServiceIDinteger Servicevarchar(50) Company Services --- companyidinteger - References Company(companyid) serviceid integer - References Services(ServiceID) The Values in Company table are companyidname 1'ABC' 2'XYZ' 3'CDE' The Values in Services table are serviceidservice 1'Ebiz' 2'MainFrames' 3'CAD' 4'Maintenance' The Values in Company Services table are companyidserviceid 1 1 1 2 1 3 2 1 2 2 3 4 I have a List Box where the user selects his Need for Services. Suppose the User selects 1 , 2 and 3 , then i need to show him the company which provides him all 1 , 2 and 3 services . By our data , its company 1 I cant search by using IN coz it would select a record even if the company is providing any one of the services. I did this by using self join for ex: select distinct(c.companyid) , c.name from company c , companyservices cs1, companyservices cs2, companyservices cs3 where cs1.serviceid = 1 AND cs2.serviceid = 2 AND cs3.serviceid = 3 AND c.companyid = cs1.companyid Is there any other way , i could achive the result without using a self join -Arul sql , 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
Re: help me with this query
Hi, Select companyid, count(*) from CompanyServices where serviceid = 1 or serviceid = 2 or serviceid = 3 group by companyid having count(*) = 3 Bye and Good Luck. --- Arul [EMAIL PROTECTED] wrote: Hi All The Table design goes like this Company Table companyid integer name varchar(100) Services Table ServiceIDinteger Servicevarchar(50) Company Services --- companyidinteger - References Company(companyid) serviceid integer - References Services(ServiceID) The Values in Company table are companyidname 1'ABC' 2'XYZ' 3'CDE' The Values in Services table are serviceidservice 1'Ebiz' 2'MainFrames' 3'CAD' 4'Maintenance' The Values in Company Services table are companyidserviceid 1 1 1 2 1 3 2 1 2 2 3 4 I have a List Box where the user selects his Need for Services. Suppose the User selects 1 , 2 and 3 , then i need to show him the company which provides him all 1 , 2 and 3 services . By our data , its company 1 I cant search by using IN coz it would select a record even if the company is providing any one of the services. I did this by using self join for ex: select distinct(c.companyid) , c.name from company c , companyservices cs1, companyservices cs2, companyservices cs3 where cs1.serviceid = 1 AND cs2.serviceid = 2 AND cs3.serviceid = 3 AND c.companyid = cs1.companyid Is there any other way , i could achive the result without using a self join -Arul sql , 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 __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.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: help me with this query
Hi I Dont think this will work coz I need the company which has serviceid = 1 AND serviceid = 2 and serviceid = 3 So let's take this Query , Select companyid, count(*) from CompanyServices where serviceid = 1 or serviceid = 2 or serviceid = 3 group by companyid having count(*) = 3 If i have another company say companyid = 5 ,who has serviceid = 1 AND serviceid = 6 AND serviceid = 7 , even this company will be retrieved which will be against the search criteria. Also another company whose companyid = 7 has serviceid = 1 AND serviceid = 2 AND serviceid = 3 AND serviceid = 5 .This company will not be retrieved since the count is not 3.But by search criteria , this company should be retrieved. So how do we handle these situations -Arul - Original Message - From: Francisco Reinaldo [EMAIL PROTECTED] To: Arul [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Cc: Arul [EMAIL PROTECTED] Sent: Wednesday, July 17, 2002 3:27 AM Subject: Re: help me with this query Hi, Select companyid, count(*) from CompanyServices where serviceid = 1 or serviceid = 2 or serviceid = 3 group by companyid having count(*) = 3 Bye and Good Luck. --- Arul [EMAIL PROTECTED] wrote: Hi All The Table design goes like this Company Table companyid integer name varchar(100) Services Table ServiceIDinteger Servicevarchar(50) Company Services --- companyidinteger - References Company(companyid) serviceid integer - References Services(ServiceID) The Values in Company table are companyidname 1'ABC' 2'XYZ' 3'CDE' The Values in Services table are serviceidservice 1'Ebiz' 2'MainFrames' 3'CAD' 4'Maintenance' The Values in Company Services table are companyidserviceid 1 1 1 2 1 3 2 1 2 2 3 4 I have a List Box where the user selects his Need for Services. Suppose the User selects 1 , 2 and 3 , then i need to show him the company which provides him all 1 , 2 and 3 services . By our data , its company 1 I cant search by using IN coz it would select a record even if the company is providing any one of the services. I did this by using self join for ex: select distinct(c.companyid) , c.name from company c , companyservices cs1, companyservices cs2, companyservices cs3 where cs1.serviceid = 1 AND cs2.serviceid = 2 AND cs3.serviceid = 3 AND c.companyid = cs1.companyid Is there any other way , i could achive the result without using a self join -Arul sql , 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 __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.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: help me please with mysqlgui
Bas, Thursday, July 04, 2002, 9:43:07 PM, you wrote: BM When I start mysqlgui on my mandrake 8.2 machine the program starts, but BM I get an error message: BM Can't connect to local MySQL server through socket'/tmp/mysql.sock'(2) BM I don't know what to do and I cannot acces the help file or find BM documentation on MySQLserver. Do you fill up entries in the Options dialog box? If no, please do it, then save and re-connect. BM Please tell me how I can solve this and/or where I can find BM documentation on MySQLGui README file that comes with MySQLGUI -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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: help me please with mysqlgui
You might have your socket files in /var/lib/mysql/. Search for the socket file and create a symlink in the /tmp directory as such: ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock Regards, Bhavin. - Original Message - From: Bas Mooyman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 04, 2002 11:43 AM Subject: help me please with mysqlgui When I start mysqlgui on my mandrake 8.2 machine the program starts, but I get an error message: Can't connect to local MySQL server through socket'/tmp/mysql.sock'(2) I don't know what to do and I cannot acces the help file or find documentation on MySQLserver. Please tell me how I can solve this and/or where I can find documentation on MySQLGui Kind regards, Bas Mooyman - 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: help me with this query
select u.userid , c.companyname from users u left join company c on (u.companyid = c.companyid) where u.status = 'ACT' Best regards, Mikhail. - Original Message - From: Arul [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Tuesday, July 02, 2002 5:15 PM Subject: help me with this query Hi All I have two tables User and Company User tables has userid , companyid and status Company table has companyid and companyname It's not necessary that all users should have company.There can be users without company also and for them the companyid is null. so i need all the users whose status are active and if they have companyid , i need the companyname of their company In Oracle i can write this as select u.userid , (select companyname from company where companyid = u.companyid) from users u where u.status = 'ACT' how do i get this in mysql i wrote one query select u.userid , c.companyname from users u , company c where u.companyid = c.companyid AND u.status = 'ACT' This returned only the users who have company...how do i take the users also who doesnt have a company Regards -Arul - 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: Help me to Frame Query
I would structure things differently. Have you thought about making each question a record/row in a table? Then if you add or remove a question, you don't have to change you table structure or your code if you do it right. You could also then easily compile stats on a per question basis. Like how many people answer 4 for question #25 or how many people got the right/wrong answer. It's a little harder to program, but it would be the most flexible. hi all, i am working on Mysql to make a table to store following data Varchar Semester, Varchar Name, Varcahr Course, int Enroll, int Responses, int Question#22, int Question#23, int Question#24, int Question#25, int Question#26, int Question#27, int Question#28, int Question#29, int Average One Sample Data row is XYZ, 1999,ABCDEF,990,6,4,4.75,3.33,4.67,4.00,4.25,4.00,4.25,4.50,4.22 My question is, can i store the data for fields Question#22.Question#29 in one data field in table (Say TABLE1), and when i retrieve the field, i should be able to break them into individual questions#, take each Question# into PHP variables and work on it. Any idea how to do that? My aim is to reduce the number of columns for the TABLE1. Your answer will be appreciated. thnaks Imrani - 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 -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - 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: help me
prajak, Friday, April 05, 2002, 7:50:44 AM, you wrote: pp mysql select distinct(nexthop) ip from NameRoute pp where OwnerIP='10.0.1.1' and pp nexthop not in (select ip from NameIP where pp OwnerIP='10.0.1.1'); pp ERROR 1064: You have an error in your SQL syntax near pp 'select ip from NameIP whe pp re OwnerIP='10.0.1.1')' at line 1 Sub-selects is not currently supported in MySQL. How you can re-write your query, read at: http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html http://www.mysql.com/doc/J/O/JOIN.html -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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: help me
hello, mysql doesn't support sub-select yet...maybe in 4.1 ;) mysql select distinct(nexthop) ip from NameRoute where OwnerIP='10.0.1.1' and nexthop not in (select ip from NameIP where OwnerIP='10.0.1.1'); ERROR 1064: You have an error in your SQL syntax near 'select ip from NameIP whe re OwnerIP='10.0.1.1')' at line 1 __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.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: HELP ME!!!
Have you got your LD_LIBRARY_PATH set correctly? Andrea Soracchi wrote: Hello, I have a Sun Os 5.8 with gcc version 2.95.3. When i try to compile mysql with the following command: CC=gcc CFLAGS=-O6 CXX=gcc CXXFLAGS=-O6 -felide-constructors -fno-exceptions -fno-rtti ./configure --prefix=/usr/local/mysql I have this result: [skip] checking for inline... inline checking for off_t... yes checking for st_rdev in struct stat... yes checking whether time.h and sys/time.h may both be included... yes checking whether struct tm is in sys/time.h or time.h... time.h checking size of char... 0 configure: error: No size for char type. A likely cause for this could be that there isn't any static libraries installed. You can verify this by checking if you have libm.a in /lib, /usr/lib or some other standard place. If this is the problem, install the static libraries and try again. If this isn't the problem, examine config.log for possible errors. If you want to report this, use 'scripts/mysqlbug' and include at least the last 20 rows from config.log! The library libm.a is on my server, I have checked What's the problem!?!! Thanks in advanced - 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 -- Sammy Lau mailto: [EMAIL PROTECTED] - Tell me what you want and I'll tell you how you can live without it. - 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: Help me!
Did you try installing a new copy of MySQL and then simply copying the appropriate files from mysql/data/mysql over to your damaged installation? Just an idea. joe -Original Message- From: noat naut - nart [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 27, 2002 5:49 AM To: [EMAIL PROTECTED] Subject: Help me! My Server is Redhat 7.2 OS and MySQL Server. I've deleted the mysql system table in MySQL Server. I can't connect to MySQL Server from any Client. I would like to know the structure of it in order to recreate it. Help me, please! Thank you very much _ Chat with friends online, try MSN Messenger: http://messenger.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: help me with complicate sql query
BS Wow, never would have come up with that, thanks very much for the help. I'm half way done with a programming version rather than sql. I will try yours first. BS I guess this would work if person_id 3 was also assigned to org 1 as well as org 2, they wouldn't show up the final results? Yes. That's like person_id 4 in my example. I don't know if you've got big tables, but I note that EXPLAIN tells me that my query will generate a temporary table. Of course, in any event, you want an index on person_orgs.person_id. If you are going to have a temporary table, anyway, it might be clearer to do it explicitly: First, create a temporary table containing the person_id's you DON'T want -- i.e., those assigned to org_id 1: mysql create temporary table temp (person_id int not null); Query OK, 0 rows affected (0.02 sec) mysql insert into temp - select p.person_id from persons p, person_orgs po - where p.person_id = po.person_id and po.org_id = 1; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql select * from temp; +---+ | person_id | +---+ | 2 | | 4 | +---+ 2 rows in set (0.00 sec) Then, use an outer join to get the ones you do want: mysql select p.person_id, p.name - from persons p left join temp t - on p.person_id = t.person_id - where t.person_id is null; +---++ | person_id | name | +---++ | 1 | unassigned | | 3 | in org 2 | +---++ 2 rows in set (0.00 sec) You'll want an index on temp.person_id. You get to figure out which is better for you. Anyway, it was an interesting puzzle. I came up with the first--and most offbeat--solution, because I was looking for a one-SQL-statement solution. Hope this helps. - Original Message - From: Brian Smith To: Bill Easton Sent: Saturday, March 02, 2002 8:51 AM Subject: RE: help me with complicate sql query I guess this would work if person_id 3 was also assigned to org 1 as well as org 2, they wouldn't show up the final results? Brian/ -Original Message- From: Bill Easton [mailto:[EMAIL PROTECTED]] Sent: Friday, March 01, 2002 7:46 PM To: [EMAIL PROTECTED] Cc: Brian Smith Subject: Re: help me with complicate sql query Well, it's possible, but it's not pretty. The right way, of course, is to have subselects. Ah, well, someday... You can't do it using just joins (inner or outer) and where clauses. The reason is that the joins will give you a cross product and the on clauses and the where clauses will throw away some of the rows. There aren't any rows that indicate that a person is NOT a member of org_id = 1. Here's a way of doing it using a GROUP BY clause. We need some way of getting a row for each person and some way to identify whether that row has a person we want. The trick--which works for a numeric column here--is to group multiple rows by person and then look at the minimum absolute value of (org_id - 1); this is zero precisely when the person is assigned to org_id - 1; Here are the tables: mysql select * from persons; +---++ | person_id | name | +---++ | 1 | unassigned | | 2 | in org 1 | | 3 | in org 2 | | 4 | in org 1 and 2 | +---++ 4 rows in set (0.00 sec) mysql select * from person_orgs; +---++ | person_id | org_id | +---++ | 2 | 1 | | 3 | 2 | | 4 | 1 | | 4 | 2 | +---++ 4 rows in set (0.00 sec) Here's the first try, with just the GROUP BY. Note that the rows we want have either null or a non-zero value in mn. mysql select p.person_id, p.name, min(abs(org_id - 1)) as mn - from persons p left join person_orgs po on p.person_id = po.person_id - group by p.person_id; +---++--+ | person_id | name | mn | +---++--+ | 1 | unassigned | NULL | | 2 | in org 1 |0 | | 3 | in org 2 |1 | | 4 | in org 1 and 2 |0 | +---++--+ 4 rows in set (0.00 sec) And here's the second try, where we use a HAVING clause to throw away the rows we don't want: mysql select p.person_id, p.name, min(abs(org_id - 1)) as mn - from persons p left join person_orgs po on p.person_id = po.person_id - group by p.person_id - having mn is null or mn 0; +---++--+ | person_id | name | mn | +---++--+ | 1 | unassigned | NULL | | 3 | in org 2 |1
Re: help me with complicate sql query
Well, it's possible, but it's not pretty. The right way, of course, is to have subselects. Ah, well, someday... You can't do it using just joins (inner or outer) and where clauses. The reason is that the joins will give you a cross product and the on clauses and the where clauses will throw away some of the rows. There aren't any rows that indicate that a person is NOT a member of org_id = 1. Here's a way of doing it using a GROUP BY clause. We need some way of getting a row for each person and some way to identify whether that row has a person we want. The trick--which works for a numeric column here--is to group multiple rows by person and then look at the minimum absolute value of (org_id - 1); this is zero precisely when the person is assigned to org_id - 1; Here are the tables: mysql select * from persons; +---++ | person_id | name | +---++ | 1 | unassigned | | 2 | in org 1 | | 3 | in org 2 | | 4 | in org 1 and 2 | +---++ 4 rows in set (0.00 sec) mysql select * from person_orgs; +---++ | person_id | org_id | +---++ | 2 | 1 | | 3 | 2 | | 4 | 1 | | 4 | 2 | +---++ 4 rows in set (0.00 sec) Here's the first try, with just the GROUP BY. Note that the rows we want have either null or a non-zero value in mn. mysql select p.person_id, p.name, min(abs(org_id - 1)) as mn - from persons p left join person_orgs po on p.person_id = po.person_id - group by p.person_id; +---++--+ | person_id | name | mn | +---++--+ | 1 | unassigned | NULL | | 2 | in org 1 |0 | | 3 | in org 2 |1 | | 4 | in org 1 and 2 |0 | +---++--+ 4 rows in set (0.00 sec) And here's the second try, where we use a HAVING clause to throw away the rows we don't want: mysql select p.person_id, p.name, min(abs(org_id - 1)) as mn - from persons p left join person_orgs po on p.person_id = po.person_id - group by p.person_id - having mn is null or mn 0; +---++--+ | person_id | name | mn | +---++--+ | 1 | unassigned | NULL | | 3 | in org 2 |1 | +---++--+ 2 rows in set (0.00 sec) From: Brian Smith [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: help me with complicate sql query Date: Fri, 1 Mar 2002 10:48:20 -0500 [...] Hello sql guru's, I need help find SQL to solve a problem: I have two tables, the first is persons: person_id int unsigned not null auto_increment firstname varchar(25) lastname varchar(25) The second is person_orgs person_id int unsigned not null org_id int unsigned not null A person may be assigned more than one org by using person_orgs table, but they can only be assigned to an org one time by way of application rules/logic. Here is my problem: I need to select a list of persons that have not ever been assigned to an org OR have not been assigned to a specific org, such as org_id = 1, so I tried: select p.firstname,p.lastname from persons p left join person_orgs po on p.person_id = po.person_id where (po.org_id 1 OR po.og_id IS NULL) I thought this was correct, and it does return persons never assigned before, but all it does concerning the specific org_id is to leave out the one record where the person is assigned to org_id = 1. If the person is assigned to org_id =1 plus any additional, then the record for the other org(s) is/are returned, giving me a person I don't need. I have no idea how to do this. It is almost as if I need to transform: person_id org_id 11 12 1 3 into: person_id org1 org2 org3 1 1 2 3 but I don't really know how in sql, and the number of orgs will be different for each person. I know I could build some large associative array in my language of choice and sort through it somehow, that just seems slow and cumbersome. I feel there is some SQL based solution, I just can't grasp it. Can anyone else? Thanks, Brian - 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: Help me to create a table with autoincrementing field
Check out: http://www.mysql.com/doc/C/R/CREATE_TABLE.html Gurhan -Original Message- From: Charitha [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 10:51 PM To: [EMAIL PROTECTED] Subject: Help me to create a table with autoincrementing field Hi all there, I am a fresher to mysql. I want to know how to create a table with an autoincrement field in that along with a name field with the varchar datatype. Thank you. Regards, Charitha. - 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: Help me with SELECT - GROUP BY - IF
Hi. On Tue, Oct 02, 2001 at 01:02:19PM +0100, [EMAIL PROTECTED] wrote: Hi there. I have problems with mySQL What I would like to do is: I have a statement SELECT a.id AS ID, IDmark, IDrecipe, ocenjevalec, ocena, ROUND(AVG(ocena),1) as povprecje, COUNT(*) as all, IF (ID=ocenjevalec, 'yes', 'no') as zeocenil FROM ocenerecepti, obiskovalci WHERE IDrecept = 1365 group by IDrecipe But 'zeocenil' allways returns no, because it groups all the data together and only the first data that has IDrecept = 1365 is checked with if sentance. I have to use group by because of AVG and count. Is there any if-like function that checks ALL records for the ID=ocenjevalec, not just the first (or the grouped by, seems to take 'ocenjevalec' as from the first data). It may be randomly chosen, but most often is the first. this is not possible, how can I achieve that? Should I make another recordset? You can use the fact that boolean expression in MySQL evaluate to 1 resp. 0 if used in an integer context, and use SUM() to force evaluation of all of them: IF( SUM( ID=ocenjevalec ) 0 ,'yes', 'no') as zeocenil which is a short form for the more obvious IF( SUM( IF(ID=ocenjevalec,1,0) ) 0 ,'yes', 'no') as zeocenil Bye, Benjamin. -- [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
Re: Help me with SELECT - GROUP BY - IF
Hi there. I have problems with mySQL What I would like to do is: I have a statement SELECT a.id AS ID, IDmark, IDrecipe, ocenjevalec, ocena, ROUND(AVG(ocena),1) as povprecje, COUNT(*) as all, IF (ID=ocenjevalec, 'yes', 'no') as zeocenil FROM ocenerecepti, obiskovalci WHERE IDrecept = 1365 group by IDrecipe But 'zeocenil' allways returns no, because it groups all the data together and only the first data that has IDrecept = 1365 is checked with if sentance. I have to use group by because of AVG and count. Is there any if-like function that checks ALL records for the ID=ocenjevalec, not just the first (or the grouped by, seems to take 'ocenjevalec' as from the first data). If this is not possible, how can I achieve that? Should I make another recordset? Thank you for your time and patience Yours Jerry - 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: HELP ME trouble with instalation of Apache+php4+MySQL!!!!!
Pablo, Try this. The key part is putting LIBS=-lz in. The rest of it was specific to my configuration. SSL_BASE=/usr/local/openssl-0.9.6b \ LIBS=-lz \ ./configure \ (etc) Hope this helps! Eric -Original Message- From: Pablo Javier Gonzalez Mateos [mailto:[EMAIL PROTECTED]] Sent: Friday, August 31, 2001 10:38 PM To: [EMAIL PROTECTED] Subject: HELP ME trouble with instalation of Apache+php4+MySQL! Hello, im in this situation: i have a PC AMD K6-2 with 64 MB RAM. SuSE Linux 6.3 OS when i compile the php with mysql everything works fine, but after y compile the apache with this command: ./configure --activate-module-=src/modules/php4/libphp4.module make and after a few seconds this error apear /usr/local/mysql-3.23.41-pc-linux-gnu-i686/lib/libmysqlclient.a(my_com press.o): In function `my_uncompress': my_compress.o(.text+0x9a): undefined reference to `uncompress' /usr/local/mysql-3.23.41-pc-linux-gnu-i686/lib/libmysqlclient.a(my_com press.o): In function `my_compress_alloc': my_compress.o(.text+0x12a): undefined reference to `compress' collect2: ld returned 1 exit status make[2]: *** [target_static] Error 1 make[2]: Leaving directory `/usr/local/apache_1.3.20/src' make[1]: *** [build-std] Error 2 make[1]: Leaving directory `/usr/local/apache_1.3.20' make: *** [build] Error 2 linux:/usr/local/apache_1.3.20 # seems to be something on the library /lib/libmysqlclient.a what can i do ??? anyone can help me ???, thanks you very much ! P.D. Excuse me for my bad english... -- Pablo Javier Gonzalez Mateos, [EMAIL PROTECTED] on 31/08/2001 - 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: HELP ME trouble with instalation of Apache+php4+MySQL!!!!!
On Fri, Aug 31, 2001 at 05:37:58PM -0300, Pablo Javier Gonzalez Mateos wrote: Hello, im in this situation: i have a PC AMD K6-2 with 64 MB RAM. SuSE Linux 6.3 OS when i compile the php with mysql everything works fine, but after y compile the apache with this command: ./configure --activate-module-=src/modules/php4/libphp4.module make and after a few seconds this error apear /usr/local/mysql-3.23.41-pc-linux-gnu-i686/lib/libmysqlclient.a(my_com press.o): In function `my_uncompress': my_compress.o(.text+0x9a): undefined reference to `uncompress' Yet another question about a missing zlib/libz. Install libz and you'll be away. On my Suse 7.1 Personal box, it's libz-1.1.3-284 but I can't imagine what it would be for 6.3 And cool it with the exclamation marks, will ya? One was more than enough. database, sql, query. -- Any technology distinguishable from magic is insufficiently advanced. [EMAIL PROTECTED] TopQuark Software Serv. Contract programmer, server bum. - 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: help me with select query
Best I can come up with is something like 2 words: SELECT c1.id FROM crossref c1, crossref c2 WHERE c1.word='word1' AND c2.word='word2' AND c1.id=c2.id 3 words: SELECT c1.id FROM crossref c1, crossref c2, crossref c3 WHERE c1.word='word1' AND c2.word='word2' AND c3.word='word3' AND c1.id=c2.id AND c1.id=c3.id - Original Message - From: Jaime Teng [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 11, 2001 13:03 Subject: help me with select query Hi, I have a table: mysql describe crossref; +---+--+--+-+ | Field | Type | Null | Key | +---+--+--+-+ | word | char(15) | | MUL | | id| int(10) unsigned | | MUL | +---+--+--+-+ +---+--+ | word | id | +---+--+ | tintin| 1604 | | registers | 1604 | | 9207844 | 1604 | | superman | 1621 | | registers | 1621 | | 4479462 | 1621 | | angelo| 1622 | | registers | 1622 | | 6330586 | 1622 | | pforshag | 1662 | | registers | 1662 | | 6344168 | 1662 | | tintin| 1689 | | authenticates | 1689 | +---+--+ How do I combine these: SELECT id FROM crossref WHERE word = 'word1'; SELECT id FROM crossref WHERE word = 'word2'; SELECT id FROM crossref WHERE word = 'word3'; such that I get the intersection of each sets? SELECT id FROM crossref WHERE word = 'word1' or word = 'word2' or word = 'word3'; is not correct because this will list down as well other numbers that does not contain all three words. example: search tintin result = 1604, 1689 search registers result = 1604, 1621, 1622, 1662 search registers, tintin result = 1604 thanks jaime - 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: help me with select query
Hi! Query: - select a.id from crossref a , crossref b , crodsref c where a.id=b.id and a.id=c.id and a.word= word1 and b.word= word2 and c.word= word3; It isn't ideal construction, but it works. For your example (two words - tintin register): select a.id from crossref a, crossref b where a.id=b.id and a.word='registers' and b.word='tintin' result: 1604 --- Dmitri Lubinski -Original Message- From: Jaime Teng [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 11, 2001 9:04 AM To: [EMAIL PROTECTED] Subject: help me with select query Hi, I have a table: mysql describe crossref; +---+--+--+-+ | Field | Type | Null | Key | +---+--+--+-+ | word | char(15) | | MUL | | id| int(10) unsigned | | MUL | +---+--+--+-+ +---+--+ | word | id | +---+--+ | tintin| 1604 | | registers | 1604 | | 9207844 | 1604 | | superman | 1621 | | registers | 1621 | | 4479462 | 1621 | | angelo| 1622 | | registers | 1622 | | 6330586 | 1622 | | pforshag | 1662 | | registers | 1662 | | 6344168 | 1662 | | tintin| 1689 | | authenticates | 1689 | +---+--+ How do I combine these: SELECT id FROM crossref WHERE word = 'word1'; SELECT id FROM crossref WHERE word = 'word2'; SELECT id FROM crossref WHERE word = 'word3'; such that I get the intersection of each sets? SELECT id FROM crossref WHERE word = 'word1' or word = 'word2' or word = 'word3'; is not correct because this will list down as well other numbers that does not contain all three words. example: search tintin result = 1604, 1689 search registers result = 1604, 1621, 1622, 1662 search registers, tintin result = 1604 thanks jaime - 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: Help me HOw to load Images or pictures into MYSQL database
Please inform me how to load IMAGES INTO MYSQL TABLES. You can do so with MySQL-Front - www.mysqlfront.de . Simply paste image from clipboard into BLOB-Editor. Greetings, Ansgar - 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: Help me HOw to load Images or pictures into MYSQL database
Please inform me how to load IMAGES INTO MYSQL TABLES. Having created several similar applications in the past I would recommend NOT storing these images IN the database; your filesystem makes for a nice blob storage device. I would instead store pointers of some sort to the files to minimize DB I/O and storage requirements. But, if you must, just write the data to a blob field. Take care, seth --- Seth Northrop Manager of Information Technology Reflectivity, Inc. 3910 Freedom Circle, Suite 103 Santa Clara, CA 95054 voice: 408-970-8881 x147 fax:408-970-8840 http://www.reflectivity.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: Help me please
Hi. On Wed, Jun 13, 2001 at 11:12:02AM +0200, [EMAIL PROTECTED] wrote: [...] Hi, I want to insatll Mysql in my station, i have Debian (linux 2.2),i installaed all the package of mysql, and after when i Write command : mysqladmin -u user create db , It ansers, mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)' Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists! If you know what's the problem, help me please thank u Well, the error message tells what to do, doesn't it? If you did follow the instructions, what where the results? I.e. do you have assured that MySQL runs? Does '/var/run/mysqld/mysqld.sock' exist? By the way, this error (as all common ones) and how to resolve it is documented and explained in detail in the fine manual: http://www.mysql.com/doc/C/a/Can_not_connect_to_server.html Bye, Benjamin. - 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: HELP ME PLEASE:MySQL said: Got error 127 from table handler
On 03-Jun-01 Derby wrote: Dear Everybody, Today I received the following error message in my web site (which is in php with mySql in UNIX environment), but it worked half a year until Today: snip MySQL said: Got error 127 from table handler I suppose too many records are in the table. 4216, but it shouldn't be the reason :-(, Nope, it's not. and unfortunately I am not allowed to delete any record from the database, because the records contain the information about subscripted users. 1, Do you know what can cause this error message? 2, What is the related exact error message text? 3, Where can I check the error message text, which related to error 127? yes, yes. localhost.dread$ perror 127 Error code 127: Unknown error: 127 127 = Record-file is crashed Something about your table is confused. 4, How can I solve this problem? copy the table files to another directory, then in order of increasing panic: A. try droping rebuilding your keys. B. mysqldump --add-drop-table DB da_table tbl.dmp if it dumps ok, then mysql DB tbl.dmp C. run repair table ... D. re-read the manual, shutdown the server then run myisamchk -r ... E. restore from backup. 5, Do I have to change the mySql query, but how? And what is the reason that it worked half a year until now? No, it's a valid query. it worked for months, right ? Besides I've never heard of a query that would crash a table. 6, It should be some change in php or UNIX, but what? No changes, always shutdown the server properly, you'll need to learn how to backup and restore. Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. - 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: HELP me!
The error log in ~mysql/var should give you a hint as to your problem. P On Fri, 6 Apr 2001, Franois Grenapin wrote: I have a little matter for begin my server mysql... When I want to connect to mysql, it happends that : RROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) and when i want to start ma server manuelly : $/mysql.server start $Starting mysqld daemon with databases from /var/lib/mysql 010406 10:59:51 mysqld ended please : give me the solution! - 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: HELP me!
the file error_lod said me that : 10406 10:59:50 mysqld started 010406 10:59:51 Can't start server : Bind on unix socket: Permission non accorde 010406 10:59:51 Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ? 010406 10:59:51 Aborting 010406 10:59:51 mysqld ended //but I have no already server turning!! - 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: HELP me!
Maybe you just have to change the PORT NUMBER in the configuration file. (I think in UNIX is my.cnf, I don't know because I work with Win NT) It seems like there's sombody listenning at this port I Hope this helps !!! good luck Santi -Mensaje original- De: Franois Grenapin [SMTP:[EMAIL PROTECTED]] Enviado el: Friday, April 06, 2001 12:22 PM Para: Peter Skipworth CC: [EMAIL PROTECTED] Asunto: Re: HELP me! the file error_lod said me that : 10406 10:59:50 mysqld started 010406 10:59:51 Can't start server : Bind on unix socket: Permission non accorde 010406 10:59:51 Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ? 010406 10:59:51 Aborting 010406 10:59:51 mysqld ended //but I have no already server turning!! - 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: HELP me!
please : give me the solution! Just a few thoughts.. you connect via PHP.. your php.ini is not pointing to the right location mysql.sock would normally live in /tmp Normally.. other location could be.. Do a ps -ax and see if mysqld shows up.. If not, it's not running at all.. otherwise it is.. Take some time and investigate.. search for mysql.sock find -name mysql.sock would do nicely. How did you install.. which OS.. RPM?? Bye, B. - 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: Help me deal with this subquery removal
On Fri, Feb 23, 2001 at 06:05:53PM -0600, Don Hosek wrote: This'd be a piece of cake with sub queries: What I have is a table with two relevant fields: iIssue and iSubId iSubId represents a magazine subscriber iIssue represents any issues that person has/had coming SELECT iSubID FROM Issues WHERE iIssue=N AND iSubID NOT IN ( SELECT iSubID FROM Issues WHERE iIssue=N+1 ) My first attempt based on the example from the documentation: SELECT i1.iSubId FROM Issue AS i1 LEFT JOIN Issue AS i2 ON i1.iSubId=i2.iSubId WHERE i2.iSubId IS NULL AND i2.iIssue=9 AND i1.iIssue=8 Your LEFT JOIN would generate rows for any combination of iIssue for an iSubId and then SELECT only those where iSubId is NULL, which I guess it never will be. You might want to check the manual for the exact workings of a LEFT JOIN. What you would need is this: SELECT i1.iSubId FROM Issue AS i1 LEFT JOIN Issue AS i2 ON i1.iSubId=i2.iSubId AND i2.iIssue = i1.iIssue + 1 WHERE i1.iIssue=8 AND i2.iIssue IS NULL The LEFT JOIN then tries to find for each record in i1, one or more records in i2 with the same iSubId as the record in i1 and an iIssue one higher than the record in i1. If no such record exists, it will generate a row anyway, but this will have NULL values for all fields in i2. So the WHERE will check for a NULL field in i2 and for the iIssue you want and return only those rows. Regards, Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - 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: Help me deal with this subquery removal
On Sat, Feb 24, 2001 at 08:56:03AM +0100, Fred van Engen wrote: On Fri, Feb 23, 2001 at 06:05:53PM -0600, Don Hosek wrote: This'd be a piece of cake with sub queries: What I have is a table with two relevant fields: iIssue and iSubId iSubId represents a magazine subscriber iIssue represents any issues that person has/had coming SELECT iSubID FROM Issues WHERE iIssue=N AND iSubID NOT IN ( SELECT iSubID FROM Issues WHERE iIssue=N+1 ) My first attempt based on the example from the documentation: SELECT i1.iSubId FROM Issue AS i1 LEFT JOIN Issue AS i2 ON i1.iSubId=i2.iSubId WHERE i2.iSubId IS NULL AND i2.iIssue=9 AND i1.iIssue=8 Your LEFT JOIN would generate rows for any combination of iIssue for an iSubId and then SELECT only those where iSubId is NULL, which I guess it never will be. You might want to check the manual for the exact workings of a LEFT JOIN. What you would need is this: SELECT i1.iSubId FROM Issue AS i1 LEFT JOIN Issue AS i2 ON i1.iSubId=i2.iSubId AND i2.iIssue = i1.iIssue + 1 WHERE i1.iIssue=8 AND i2.iIssue IS NULL The LEFT JOIN then tries to find for each record in i1, one or more records in i2 with the same iSubId as the record in i1 and an iIssue one higher than the record in i1. If no such record exists, it will generate a row anyway, but this will have NULL values for all fields in i2. So the WHERE will check for a NULL field in i2 and for the iIssue you want and return only those rows. You may also try this: SELECT iSubId FROM Issue GROUP BY iSubId HAVING MAX(iIssue) = 8 I don't use HAVING usually, so you might want to check this. It does assume that there will be no gaps in issues for a subscriber (i.e. a subscriber with issues 5,6,7,8,20,21 won't be found with this). Regards, Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - 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: help me !
Redhat comes with mysql already installed as a RPM usually. I had the same problem until I unstalled the RPM that came with Red Hat. A fix for this problem if you don't want to uninstall the original can be found at:http://www.mysql.com/doc/I/n/Installing_many_servers.html - 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: help me !
Usually means you haven't started the server. Check by doing a ps command and also check the manual if you don't know how to start it. - Original Message - From: "hocine grine" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 07, 2001 1:00 Subject: help me ! my name is grine hocine ; please help me ,i have installed mysql and php from redhat version 7 and when i run the application in the same machine , i receive this error message "MySQL connection failed can't connect to local MySQL server through socket '/var/lib/mysql.sock'(111) thank you for all ___ Do You Yahoo!? -- Pour dialoguer en direct avec vos amis, Yahoo! Messenger : http://fr.messenger.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
