RE: Need help with ledger type query
Charles: I am not quite sure by what you mean, How do I get it to show me as I like, one in and one out per line, rather then an in for every out and vice-versa? It seems for your example below you want a result set of 4 rows I don't think your design is appropriate, however your suggestion at the end is on the right track. Tbl_users is good. Now use these: Tbl_transaction Trans_Id | user | trans_type_id | trans_amt | entry_dtm -+--++---+-- 1 |1 | 1 | 20.00 | timestamp 2 |1 | 2 |100.00 | timestamp 3 |1 | 2 | 50.00 | timestamp 4 |1 | 1 | 40.00 | timestamp Tbl_transaction_type Trans_type_id | trans_nm | trans_cd --+--+- 1 | Debit | D 2 | Credit | C This provides you with a foundation for flexibility if you wish to add more transaction types in the future such as a transfer or stock option. BTW, you can use a case statement to help with signing the number properly. I just discovered this the other day and am really tickled with it! Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. Life is not a journey to the grave arriving safely in a well preserved body, but rather a skid in broadside, thoroughly used, totally worn, and loudly proclaiming: WOW! What a ride! -Original Message- From: charles kline [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 02, 2004 10:50 AM To: [EMAIL PROTECTED] Subject: Need help with ledger type query Hi all, I have 3 tables: tbl_users id | user --- 1 | charles tbl_ins id | in |date|user 1 | 100.00 |timestamp |1 2 | 50.00 |timestamp |1 tbl_outs id | out|date|user 1 | 20.00 |timestamp |1 2 | 40.00 |timestamp |1 I want this to work like a checkbook register, where by date, I display the ins and outs (one per line) with a running balance. Here is my query (I know it does not have the balance, but I can work that part out). SELECT tbl_users.id, tbl_ins.in, tbl_outs.out FROM tbl_users LEFT JOIN tbl_ins ON tbl_ins.user = tbl_users.id LEFT JOIN tbl_outs ON tbl_outs.user = tbl_users.id WHERE tbl_users.id = 1 How do I get it to show me as I like, one in and one out per line, rather then an in for every out and vice-versa? My other thought was to have one table with positive and negative values in an AMOUNT field... Thanks, Charles -- 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: Need help with ledger type query
Boyd, Thank you very much for the design help... this is just what I am looking for, just wasn't clear how to best do it :) BTW, you can use a case statement to help with signing the number properly. I just discovered this the other day and am really tickled with it! I am not very experienced with SQL, could you show an example? Thanks very much, Charles On Mar 2, 2004, at 1:24 PM, Boyd E. Hemphill wrote: Charles: I am not quite sure by what you mean, How do I get it to show me as I like, one in and one out per line, rather then an in for every out and vice-versa? It seems for your example below you want a result set of 4 rows I don't think your design is appropriate, however your suggestion at the end is on the right track. Tbl_users is good. Now use these: Tbl_transaction Trans_Id | user | trans_type_id | trans_amt | entry_dtm -+--++---+-- 1 |1 | 1 | 20.00 | timestamp 2 |1 | 2 |100.00 | timestamp 3 |1 | 2 | 50.00 | timestamp 4 |1 | 1 | 40.00 | timestamp Tbl_transaction_type Trans_type_id | trans_nm | trans_cd --+--+- 1 | Debit | D 2 | Credit | C This provides you with a foundation for flexibility if you wish to add more transaction types in the future such as a transfer or stock option. BTW, you can use a case statement to help with signing the number properly. I just discovered this the other day and am really tickled with it! Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. Life is not a journey to the grave arriving safely in a well preserved body, but rather a skid in broadside, thoroughly used, totally worn, and loudly proclaiming: WOW! What a ride! -Original Message- From: charles kline [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 02, 2004 10:50 AM To: [EMAIL PROTECTED] Subject: Need help with ledger type query Hi all, I have 3 tables: tbl_users id | user --- 1 | charles tbl_ins id | in |date|user 1 | 100.00 |timestamp |1 2 | 50.00 |timestamp |1 tbl_outs id | out|date|user 1 | 20.00 |timestamp |1 2 | 40.00 |timestamp |1 I want this to work like a checkbook register, where by date, I display the ins and outs (one per line) with a running balance. Here is my query (I know it does not have the balance, but I can work that part out). SELECT tbl_users.id, tbl_ins.in, tbl_outs.out FROM tbl_users LEFT JOIN tbl_ins ON tbl_ins.user = tbl_users.id LEFT JOIN tbl_outs ON tbl_outs.user = tbl_users.id WHERE tbl_users.id = 1 How do I get it to show me as I like, one in and one out per line, rather then an in for every out and vice-versa? My other thought was to have one table with positive and negative values in an AMOUNT field... Thanks, Charles -- 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: Need help with a SELECT statement across 3 tables
Dominique: Thanks for your suggestions/ideas. After playing with it for a little while longer of banging my head into a brick wall, I realized I was using a left join when I needed a right. You have my table structures pretty much down - here's the final SQL statement that I use to return 1 row per update per server that hasn't been applied: SELECT update_track.update_id, server.server_id, server.os, update_track.bugtraq_id FROM update_track LEFT JOIN server_update ON (update_track.update_id = server_update.update_id) RIGHT JOIN server ON (server_update.server_id = server.server_id) WHERE server.os = update_track.os AND server_update.server_id IS NULL; Which returns something easy to work with ( from a test set of 2 entries in the update_track table) +---+---+--++ | update_id | server_id | os | bugtraq_id | +---+---+--++ | 5 | 5 | Windows 2000 | 0 | | 5 | 7 | Windows 2000 | 0 | | 5 | 8 | Windows 2000 | 0 | | 5 |13 | Windows 2000 | 0 | | 5 |16 | Windows 2000 | 0 | | 5 |19 | Windows 2000 | 0 | | 5 |20 | Windows 2000 | 0 | | 5 |27 | Windows 2000 | 0 | | 5 |28 | Windows 2000 | 0 | | 5 |30 | Windows 2000 | 0 | | 5 |31 | Windows 2000 | 0 | | 5 |32 | Windows 2000 | 0 | | 5 |39 | Windows 2000 | 0 | | 5 |40 | Windows 2000 | 0 | | 5 |44 | Windows 2000 | 0 | | 5 |49 | Windows 2000 | 0 | | 5 |51 | Windows 2000 | 0 | | 4 |53 | RedHat 9 | 0 | | 5 |56 | Windows 2000 | 0 | | 5 | 104 | Windows 2000 | 0 | | 5 | 123 | Windows 2000 | 0 | | 4 | 532 | RedHat 9 | 0 | | 5 | 165 | Windows 2000 | 0 | | 4 | 295 | RedHat 9 | 0 | | 5 | 327 | Windows 2000 | 0 | | 5 | 361 | Windows 2000 | 0 | | 5 | 364 | Windows 2000 | 0 | | 5 | 388 | Windows 2000 | 0 | | 5 | 403 | Windows 2000 | 0 | | 5 | 405 | Windows 2000 | 0 | | 5 | 406 | Windows 2000 | 0 | | 5 | 407 | Windows 2000 | 0 | | 5 | 408 | Windows 2000 | 0 | | 5 | 424 | Windows 2000 | 0 | | 5 | 430 | Windows 2000 | 0 | | 5 | 455 | Windows 2000 | 0 | | 5 | 457 | Windows 2000 | 0 | | 4 | 467 | RedHat 9 | 0 | | 4 | 529 | RedHat 9 | 0 | | 4 | 512 | RedHat 9 | 0 | | 5 | 533 | Windows 2000 | 0 | | 5 | 554 | Windows 2000 | 0 | | 4 | 556 | RedHat 9 | 0 | | 4 | 558 | RedHat 9 | 0 | | 4 | 565 | RedHat 9 | 0 | | 4 | 575 | RedHat 9 | 0 | | 4 | 601 | RedHat 9 | 0 | | 5 | 614 | Windows 2000 | 0 | +---+---+--++ I think I should be able to claim SQL as a second language - you can say so much with it! Brandon Ewing -Original Message- From: Dominique Plante [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 27, 2004 4:43 PM To: 'Brandon Ewing' Subject: RE: Need help with a SELECT statement across 3 tables Brandon: I have been toying with your problem, and unfortunately, I have yet to come up with a good solution, since I am interested in seeing what the solution would be. Maybe you can confirm a few things. Do your table structures look anything like this? Server: | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | server_id | int(11) | | PRI | NULL| auto_increment | | location | varchar(30) | YES | | NULL|| | os| varchar(30) | YES | | NULL|| Server_update: | Field | Type | Null | Key | Default | Extra | ++---+--+-+-+---+ | server_id | int(11) | | | 0 | | | update_id | int(11) | | | 0 | | | updateDateTime | timestamp | YES | | NULL| | Update_track: | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | update_id
Re: Need help with a query
* Soheil Shaghaghi Hello everyone, I need help with MySQL coding in php please if anyone can help. I can try. :) I have 3 tables: -users, where the user info is stored. -awards: contains the list of all the awards for each user -award_types: contains different types of award The tables are at the bottom of the page. What I need to do is look at these tables when a user id is being viewed and display the awards image that the user has won. A user can have multiple awards. Ok... and what is the problem? The SQL could be something like this: SELECT award_type, award_image FROM award_types,awards WHERE award_types.id = awards.award_id AND awards.chosen = 'enabled' AND awards.user_id = $userid (Not sure about the chosen = 'enabled', just looked like that was what you wanted from your example data.) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a query..
Tibby wrote: ..and I want to get this with a single query: +-++--+ | key | desc| value | +-++--+ | 2 | book| 7 | | 6 | pen | 7 | +-++--+ I need to get only one row from col. DESC, the one with the highest VALUE. With one query... select `desc`, max(value) from mytable group by `desc` -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a query..
Hi, I have already tried the 'rtfm', but it just didn't help. But it's right there :) 3.5.2 The Row Holding the Maximum of a Certain Column ..and I want to get this with a single query: +-++--+ | key | desc| value | +-++--+ | 2 | book| 7 | | 6 | pen | 7 | +-++--+ select key, desc, value from your_table t1 where value = (select max(value) from your_table where desc = t1.desc) Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a query..
* Aleksandar Bradaric select key, desc, value from your_table t1 where value = (select max(value) from your_table where desc = t1.desc) Anyway, when i execute this query, i get an error near 'select max(value)'... :( It's because the subselects are supported from version 4.1. Yes. If you use older MySQL version then it's not possible to do it with a single query :( Yes, it is. :) URL: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html See the MAX-CONCAT trick. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with query. Please!
Try: SELECT C.company_id, C.company_name FROM companies C LEFT JOIN company_group_intersect CG ON (C.company_id=CG.company_id AND CG.group_id='1') WHERE C.status='1' AND CG.company_id IS NULL -- Diana Soares On Fri, 2003-12-05 at 15:08, Robert Hughes wrote: I have the following 3 tables: table 1: companies fields 1: company_id, company name, status Sample data: 1 , company 1 , 0 2 , company 2 , 1 3 , company 3 , 1 4 , company 4 , 0 5 , company 5 , 1 table 2: groups fields 2: group_id, group_name Sample data: 1 , Group 1 2 , Group 2 3 , Group 3 4 , Group 4 5 , Group 5 table 3: company_group_intersect table 3: group_id, company_id Sample data: 1 , 2 1 , 3 2 , 2 2 , 3 2 , 5 As you can see, Group 1 consists of Companies 2 and 3. And Group 2 consists of Companies 2, 3 and 5. The query I'm having trouble with is trying to get a result set of (status=1) companies that AREN'T in a particular group. group_id is my only available variable passed in from the script. I need a results set that has: * companies.company_name, companies.company_id where companies.status='1' and companies.company_id is not in intersect table next to the variable group_id. If I pass in group_id 1 the result set should be: 5 , company 5 since it's the only status='1' company that's not in group 1 Thanks in advance for your help. Robert --- At Executive Performance Group we take security very seriously. All emails and attachments are scanned for viruses prior to sending. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.544 / Virus Database: 338 - Release Date: 11/25/2003 -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help with query. Please!
That worked perfectly!!! Thanks so much :-) -Original Message- From: Diana Soares [mailto:[EMAIL PROTECTED] Sent: Friday, December 05, 2003 10:31 AM To: Robert Hughes Cc: [EMAIL PROTECTED] Subject: Re: Need help with query. Please! Try: SELECT C.company_id, C.company_name FROM companies C LEFT JOIN company_group_intersect CG ON (C.company_id=CG.company_id AND CG.group_id='1') WHERE C.status='1' AND CG.company_id IS NULL -- Diana Soares On Fri, 2003-12-05 at 15:08, Robert Hughes wrote: I have the following 3 tables: table 1: companies fields 1: company_id, company name, status Sample data: 1 , company 1 , 0 2 , company 2 , 1 3 , company 3 , 1 4 , company 4 , 0 5 , company 5 , 1 table 2: groups fields 2: group_id, group_name Sample data: 1 , Group 1 2 , Group 2 3 , Group 3 4 , Group 4 5 , Group 5 table 3: company_group_intersect table 3: group_id, company_id Sample data: 1 , 2 1 , 3 2 , 2 2 , 3 2 , 5 As you can see, Group 1 consists of Companies 2 and 3. And Group 2 consists of Companies 2, 3 and 5. The query I'm having trouble with is trying to get a result set of (status=1) companies that AREN'T in a particular group. group_id is my only available variable passed in from the script. I need a results set that has: * companies.company_name, companies.company_id where companies.status='1' and companies.company_id is not in intersect table next to the variable group_id. If I pass in group_id 1 the result set should be: 5 , company 5 since it's the only status='1' company that's not in group 1 Thanks in advance for your help. Robert --- At Executive Performance Group we take security very seriously. All emails and attachments are scanned for viruses prior to sending. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.544 / Virus Database: 338 - Release Date: 11/25/2003 -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.544 / Virus Database: 338 - Release Date: 11/25/2003 --- At Executive Performance Group we take security very seriously. All emails and attachments are scanned for viruses prior to sending. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.544 / Virus Database: 338 - Release Date: 11/25/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Help Upgrading From 4.x to 4.x
On Wed, 19 Nov 2003, Mark Marshall wrote: Hi, everyone. I have a 4.0.4 beta install of Mysql on Red Hat 7.3. I want to upgrade it to 4.0.16, and keep all the data intact. Do I just dump the databases (just in case), stop the server, then ./configure, make, make install over top of the old server and start it up again and see what happens? exactly. The dump is a good idea. Make sure that you compile with the same options to configure as your 4.0.4 build (everything should go to the same directory as it was before). This used to be a problem with SuSE distributions when installing over an old prm installation, because they used to have a different directory layout. I can't tell you how RedHat did this. Thomas Spahni Thanks, Mark As of November 1st, 2003, Brandywine Senior Care's Corporate Office new contact information is: Brandywine Senior Care, Inc. 525 Fellowship Road Suite 360 Mt. Laurel, NJ 08054 (856) 813-2000 Phone (856) 813-2020 Fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Help Upgrading From 4.x to 4.x
You could either use mysqldump or just copy the data directory to a safe place. Also, if you do not need any special build flags, you should use the official MySQL binaries, either the RPM or tarball. They optimized the binaries. -will - Original Message - From: Thomas Spahni [EMAIL PROTECTED] To: Mark Marshall [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, November 19, 2003 10:18 AM Subject: Re: Need Help Upgrading From 4.x to 4.x On Wed, 19 Nov 2003, Mark Marshall wrote: Hi, everyone. I have a 4.0.4 beta install of Mysql on Red Hat 7.3. I want to upgrade it to 4.0.16, and keep all the data intact. Do I just dump the databases (just in case), stop the server, then ./configure, make, make install over top of the old server and start it up again and see what happens? exactly. The dump is a good idea. Make sure that you compile with the same options to configure as your 4.0.4 build (everything should go to the same directory as it was before). This used to be a problem with SuSE distributions when installing over an old prm installation, because they used to have a different directory layout. I can't tell you how RedHat did this. Thomas Spahni Thanks, Mark As of November 1st, 2003, Brandywine Senior Care's Corporate Office new contact information is: Brandywine Senior Care, Inc. 525 Fellowship Road Suite 360 Mt. Laurel, NJ 08054 (856) 813-2000 Phone (856) 813-2020 Fax -- 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: Need Help Upgrading From 4.x to 4.x
What if one is using rpm - should just use the --upgrade option rpm -U MySQL-server-4.x Is this ok to do? Thanks Aman Raheja AGF Technologies http://www.agftech.com [EMAIL PROTECTED] wrote: On Wed, 19 Nov 2003, Mark Marshall wrote: Hi, everyone. I have a 4.0.4 beta install of Mysql on Red Hat 7.3. I want to upgrade it to 4.0.16, and keep all the data intact. Do I just dump the databases (just in case), stop the server, then ./configure, make, make install over top of the old server and start it up again and see what happens? exactly. The dump is a good idea. Make sure that you compile with the same options to configure as your 4.0.4 build (everything should go to the same directory as it was before). This used to be a problem with SuSE distributions when installing over an old prm installation, because they used to have a different directory layout. I can't tell you how RedHat did this. Thomas Spahni Thanks, Mark As of November 1st, 2003, Brandywine Senior Care's Corporate Office new contact information is: Brandywine Senior Care, Inc. 525 Fellowship Road Suite 360 Mt. Laurel, NJ 08054 (856) 813-2000 Phone (856) 813-2020 Fax -- Your favorite stores, helpful shopping tools and great gift ideas. Experience the convenience of buying online with [EMAIL PROTECTED] http://shopnow.netscape.com/
Re: Need help comparing MySQL to MS SQL Server
KEVIN ZEMBOWER wrote: The organization hired an outside consultant to evaluate which SQL engine to go with. This is what he sent to us: ... SQL Server 2000 is a complete Relational Database Management System (RDBMS) that also includes integrated analysis functionality for OLAP and data mining. SQL Server 2000 meets the data and analysis storage requirements of the largest data processing systems and commercial Web sites, yet at the same time can provide easy-to-use data storage services to an individual or small business. I don't know whether the consultant wrote this himself, or if it came from somewhere. Answer: It came from somewhere. The above paragraph is a word-for-word quote from this Microsoft document: http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/deploy/upgrdmigrate/mysql.asp Regards, PeterG -- Peter Gulutzan, Software Architect MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help constructing query ...
* John Kelly
I have a table of full URLs and IPs and am using the following
query to return
distinct web requests by domain. Using SUBSTRING_INDEX it only returns the
domain part of the URL:
SELECT SUBSTRING_INDEX(url, '/', 3) as topsites, count(distinct
ip) as count
from tablename WHERE SUBSTRING_INDEX(url, '/', 3) LIKE
'%mydomain%' group by
topsites order by count
Example output:
topsitescount
http://www.mydomain.com5
http://mydomain.com 3
My question is how do I modify the query to get it to merge
requests for the
same domain by ignoring the www. so that the above would return:
http://mydomain.com 8
I think it has something to do with adding
REPLACE('url', 'www.', '')
but I can't figure out where to put it to make it work.
Try either
SUBSTRING_INDEX(REPLACE(url, 'www.', ''), '/', 3)
or
REPLACE(SUBSTRING_INDEX(url, '/', 3),'www.', '')
You don't need it in the WHERE clause, only in the field list and GROUP BY:
SELECT REPLACE(SUBSTRING_INDEX(url, '/', 3),'www.', '') as topsites,
count(distinct ip) as count from tablename
WHERE SUBSTRING_INDEX(url, '/', 3) LIKE '%mydomain%'
group by topsites order by count
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help comparing MySQL to MS SQL Server
Hi Kevin, I'm a system administrator for a small (200 people) branch of a large university/medical school. I've worked with MySQL and use it as my database of choice for web-based dynamic content. I would not consider myself an experienced, professionally-trained, knowledgeable database administrator, more of a database user who's had to administer his own database systems because no one else's around. My organization is trying to decide on an SQL engine for general purpose database work within our organization. The one professional database administrator we have works mainly in MS Access, but is looking forward to building on her beginner-level understanding of SQL and becoming an SQL administrator. Right now, the largest database in our organization is a flat-file structure with less than 500,000 records in it, which could conceivably grow ten-fold in the next five years. The organization hired an outside consultant to evaluate which SQL engine to go with. This is what he sent to us: === MySQL is an open-source database management system (DBMS). It uses client/server architecture and is a multi-threaded, multi-user database server. MySQL was designed for speed; therefore, it does not provide many of the features provided by relational database systems, such as sub-queries, foreign keys, referential integrity, stored procedures, triggers, and views. In addition, it contains a locking mechanism that is not adequate for tables containing many write actions occurring simultaneously from different users. It is also lacking in reference to support for software applications and tools. SQL Server 2000 is a complete Relational Database Management System (RDBMS) that also includes integrated analysis functionality for OLAP and data mining. SQL Server 2000 meets the data and analysis storage requirements of the largest data processing systems and commercial Web sites, yet at the same time can provide easy-to-use data storage services to an individual or small business. The architecture of Microsoft SQL Server supports advanced server features, such as row-level locking, advanced query optimization, data replication, distributed database management, and Analysis Services. Transact-SQL (T-SQL) is the SQL dialect supported by SQL Server 2000. === I don't know whether the consultant wrote this himself, or if it came from somewhere. It could be Microsoft advertizement, for all I know. Most of the terms aren't familiar to me, like sub-queries or referential integrity. I feel out of my depth evaluating this comparison. Referential integrity is supported for InnoDB type tables - with MySQL, each table can have a different type, each table handler (in the MySQL system) can handle different features. With InnoDB, there's Referential integrity, transaction support and and also a different locking mechanism - which is more suited for lots of readers and concurrent writers. My questions are: 1. Is this a fair comparison of MySQL and MS SQL Server 2000? Fairly fair :-) 2. Is this up to date with the current status of MySQL? Triggers, views and Stored Procedures are expected to be included in MySQL 5. The next version of MSSQL will include a new locktype/transaction isolation, one which works pretty much the same as InnoDB - versioned locking. 3. Would the deficiencies pointed out in MySQL, if true, apply to the type of work we envision? Granted, I haven't given you all much information about what we hope to do with an SQL engine, but I don't think it will be very sophisticated. If it's not very sophisticated, MySQL will do just fine. In my opinion. There are other free and open source DBMSes as well, that do have procedures, triggers, views, subqueries and the like. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help comparing MySQL to MS SQL Server
Martijn, thank you very much for your analysis. I hope others will continue to join in. With regard to your point quoted below, are you referring to PostgreSQL, and would that be a stronger competitor to MS SQL Server 2000 than either the current version of MySQL or MySQL 5? Thanks, again, for your thoughts. -Kevin Martijn Tonies [EMAIL PROTECTED] 11/07/03 12:12PM 3. Would the deficiencies pointed out in MySQL, if true, apply to the type of work we envision? Granted, I haven't given you all much information about what we hope to do with an SQL engine, but I don't think it will be very sophisticated. If it's not very sophisticated, MySQL will do just fine. In my opinion. There are other free and open source DBMSes as well, that do have procedures, triggers, views, subqueries and the like. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help comparing MySQL to MS SQL Server
Hi Kevin, Martijn, thank you very much for your analysis. I hope others will continue to join in. So do I :-) With regard to your point quoted below, are you referring to PostgreSQL, and would that be a stronger competitor to MS SQL Server 2000 than either the current version of MySQL or MySQL 5? I have no experience with PostgreSQL - although, from what I've heard and read, it's quite capable - but not easy to get going on Windows. One other open source RDBMS would be Firebird - see www.firebirdsql.org Especially the newer release (1.5). Don't get fooled by that version number - it's a fork of the Borland InterBase code, which has been around for about 20 years now. I'm looking forward to MySQL5 to see what's new and how it's implemented. As for what engine would be the best for you - it all depends on what you're going to do. For example, I frequently use triggers and check constraints in my database applications, with MySQL, I can't do this. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help comparing MySQL to MS SQL Server
I have not work with it but postgres is supposed to work great in /BSD/Linux/Unix/solaris environment Which platform are you using? :-) Nestor A. Florez Martijn Tonies [EMAIL PROTECTED] 11/7/2003 10:08:53 AM Hi Kevin, Martijn, thank you very much for your analysis. I hope others will continue to join in. So do I :-) With regard to your point quoted below, are you referring to PostgreSQL, and would that be a stronger competitor to MS SQL Server 2000 than either the current version of MySQL or MySQL 5? I have no experience with PostgreSQL - although, from what I've heard and read, it's quite capable - but not easy to get going on Windows. One other open source RDBMS would be Firebird - see www.firebirdsql.org Especially the newer release (1.5). Don't get fooled by that version number - it's a fork of the Borland InterBase code, which has been around for about 20 years now. I'm looking forward to MySQL5 to see what's new and how it's implemented. As for what engine would be the best for you - it all depends on what you're going to do. For example, I frequently use triggers and check constraints in my database applications, with MySQL, I can't do this. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help comparing MySQL to MS SQL Server
It sounds like a copy and paste from Microsoft, but that is just my guess. An objective recommendation with show pluses and minuses of both. It most definitely does not sound like this consultant is qualified to suggest a database product. What about PostgresSQL, Oracle, Sybase, DB2? They all at least match the features of SQL Server, except maybe Postgres, and they run on more platforms and are more scalable. MySQL does have transaction support in the form of InnoDB tables. Sub-queries are now supported in v4, although not the fastest implementation. The other areas MySQL is lacking. When I first started using MySQL, subqueries were not supported and I found it frustrating. Since then I have truly acquired in depth knowledge of left and right joins and other techniques that I really didn't have before. I think I now write better, faster queries because I was forced to learn a new technique. I now have better knowledge of SQL. Not sure if that was a good or bad point. If your needs are simple, you can get by without stored procedures and triggers. Referential integrity can be enforced in your front-end code. Here is a question: how much would it cost to give everyone a copy of the database to play with? On their laptop, home computer? Nothing for MySQL or Postgres. What front-end will be used? Your options for SQL Server are kind of limited. On Friday, November 7, 2003, at 11:39 AM, KEVIN ZEMBOWER wrote: I don't know whether the consultant wrote this himself, or if it came from somewhere. It could be Microsoft advertizement, for all I know. Most of the terms aren't familiar to me, like sub-queries or referential integrity. I feel out of my depth evaluating this comparison. My questions are: 1. Is this a fair comparison of MySQL and MS SQL Server 2000? 2. Is this up to date with the current status of MySQL? 3. Would the deficiencies pointed out in MySQL, if true, apply to the type of work we envision? Granted, I haven't given you all much information about what we hope to do with an SQL engine, but I don't think it will be very sophisticated. Thank you for all your thoughts and comments. -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help comparing MySQL to MS SQL Server
Nestor, thanks for your question. The platform will actually be dictated by the SQL engine, not the other way around, which is more typically the case. If we go with MS SQL Server, we'll build a separate host, NT I would guess, to host it. I'm only responsible for Unix and Linux boxes here, so it'll be the responsibility of another group. If we go with MySQL or PostgreSQL (the only databases I have any familiarity with), I'll probably be responsible for setting up and configuring a new Linux (Debian) host, and maintaining it. The in-house database administer would be the administrator, and I would just offer any help that I could, which might not be much. Thanks, again, for writing. -Kevin Nestor Florez [EMAIL PROTECTED] 11/07/03 01:18PM I have not work with it but postgres is supposed to work great in /BSD/Linux/Unix/solaris environment Which platform are you using? :-) Nestor A. Florez Martijn Tonies [EMAIL PROTECTED] 11/7/2003 10:08:53 AM Hi Kevin, Martijn, thank you very much for your analysis. I hope others will continue to join in. So do I :-) With regard to your point quoted below, are you referring to PostgreSQL, and would that be a stronger competitor to MS SQL Server 2000 than either the current version of MySQL or MySQL 5? I have no experience with PostgreSQL - although, from what I've heard and read, it's quite capable - but not easy to get going on Windows. One other open source RDBMS would be Firebird - see www.firebirdsql.org Especially the newer release (1.5). Don't get fooled by that version number - it's a fork of the Borland InterBase code, which has been around for about 20 years now. I'm looking forward to MySQL5 to see what's new and how it's implemented. As for what engine would be the best for you - it all depends on what you're going to do. For example, I frequently use triggers and check constraints in my database applications, with MySQL, I can't do this. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help comparing MySQL to MS SQL Server
What about MySQL-max/SAPDB? I believe that it was completely omitted in the consultants report but has many of the features you need. I would also like to ask a question; do you need stored procedures, triggers or views for your application? There are a number of high volume, high quality sites that do very nicely without them. Why are you different? John Griffin -Original Message- From: KEVIN ZEMBOWER [mailto:[EMAIL PROTECTED] Sent: Friday, November 07, 2003 1:53 PM To: [EMAIL PROTECTED] Subject: Re: Need help comparing MySQL to MS SQL Server Nestor, thanks for your question. The platform will actually be dictated by the SQL engine, not the other way around, which is more typically the case. If we go with MS SQL Server, we'll build a separate host, NT I would guess, to host it. I'm only responsible for Unix and Linux boxes here, so it'll be the responsibility of another group. If we go with MySQL or PostgreSQL (the only databases I have any familiarity with), I'll probably be responsible for setting up and configuring a new Linux (Debian) host, and maintaining it. The in-house database administer would be the administrator, and I would just offer any help that I could, which might not be much. Thanks, again, for writing. -Kevin Nestor Florez [EMAIL PROTECTED] 11/07/03 01:18PM I have not work with it but postgres is supposed to work great in /BSD/Linux/Unix/solaris environment Which platform are you using? :-) Nestor A. Florez Martijn Tonies [EMAIL PROTECTED] 11/7/2003 10:08:53 AM Hi Kevin, Martijn, thank you very much for your analysis. I hope others will continue to join in. So do I :-) With regard to your point quoted below, are you referring to PostgreSQL, and would that be a stronger competitor to MS SQL Server 2000 than either the current version of MySQL or MySQL 5? I have no experience with PostgreSQL - although, from what I've heard and read, it's quite capable - but not easy to get going on Windows. One other open source RDBMS would be Firebird - see www.firebirdsql.org Especially the newer release (1.5). Don't get fooled by that version number - it's a fork of the Borland InterBase code, which has been around for about 20 years now. I'm looking forward to MySQL5 to see what's new and how it's implemented. As for what engine would be the best for you - it all depends on what you're going to do. For example, I frequently use triggers and check constraints in my database applications, with MySQL, I can't do this. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- 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: Need help comparing MySQL to MS SQL Server
kevin, i tend to think the consultant really just read something that microsoft sent him. it doesn't sound like he's qualified to suggest one database or another. We've been usinf mysql for a year now. We use InnoDB tables, which give us primary key/foreign key constraints and transactions. We've gotten around the lack of stored procedures by putting the necessary logic and checks into the application that inserts/updates the database. We have several tables with 8 million rows, and growing every day. updating rows on the big tables still shows approximately constant time performance. In general, we are extremely satisfied with the product, and have purchased a license (about $400 -- mainly so we can contribute to the cause). When 4.1 becomes stable, we will upgrade in order to get the sub-select capability. I came from an Oracle/Sybase background. Those products have features that mysql does not have, in particular DBA-specific tables, views, and triggers, but you may not need these features. happy to give you more information if you need it. jeff KEVIN ZEMBOWER wrote: Nestor, thanks for your question. The platform will actually be dictated by the SQL engine, not the other way around, which is more typically the case. If we go with MS SQL Server, we'll build a separate host, NT I would guess, to host it. I'm only responsible for Unix and Linux boxes here, so it'll be the responsibility of another group. If we go with MySQL or PostgreSQL (the only databases I have any familiarity with), I'll probably be responsible for setting up and configuring a new Linux (Debian) host, and maintaining it. The in-house database administer would be the administrator, and I would just offer any help that I could, which might not be much. Thanks, again, for writing. -Kevin Nestor Florez [EMAIL PROTECTED] 11/07/03 01:18PM I have not work with it but postgres is supposed to work great in /BSD/Linux/Unix/solaris environment Which platform are you using? :-) Nestor A. Florez Martijn Tonies [EMAIL PROTECTED] 11/7/2003 10:08:53 AM Hi Kevin, Martijn, thank you very much for your analysis. I hope others will continue to join in. So do I :-) With regard to your point quoted below, are you referring to PostgreSQL, and would that be a stronger competitor to MS SQL Server 2000 than either the current version of MySQL or MySQL 5? I have no experience with PostgreSQL - although, from what I've heard and read, it's quite capable - but not easy to get going on Windows. One other open source RDBMS would be Firebird - see www.firebirdsql.org Especially the newer release (1.5). Don't get fooled by that version number - it's a fork of the Borland InterBase code, which has been around for about 20 years now. I'm looking forward to MySQL5 to see what's new and how it's implemented. As for what engine would be the best for you - it all depends on what you're going to do. For example, I frequently use triggers and check constraints in my database applications, with MySQL, I can't do this. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help on WHERE ... LIKE Query
This should work for you: SELECT * FROM sometable WHERE surname BETWEEN 'A' AND 'D' ORDER BY surname In my quick test the first parameter is inclusive while the second is not, which is why it is D and not C. On Thursday, October 30, 2003, at 02:22 PM, Scott Brown wrote: I want to do a string comparison like this: SELECT * FROM sometable WHERE surname LIKE '[A-C]%' ORDER BY surname; -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help on WHERE ... LIKE Query
You can use RLIKE which is regular expressions then you should be able to execute SELECT * FROM sometable WHERE surname RLIKE '^[A-C]' ORDER BY surname; Kelley Scott Brown wrote: Hi, List, I looked here: http://www.mysql.com/doc/en/String_comparison_functions.html But I am not seeing what I need. I want to do a string comparison like this: SELECT * FROM sometable WHERE surname LIKE '[A-C]%' ORDER BY surname; This works in another RDBMS. It doesn't return a syntax error, either, but it returns no records. My guess is that MySQL is interpreting the whole thing literally, rather than looking for what I want. I need this to return all records where surname begins with the letters A through C (that is, all records with a surname which begins with A, B, or C). Anybody got a how-to? I'm sure there must be some way, other than to do this three times. Some of these can vary; that is, it may be 0-9, or 0-Z (show all), even, so I don't want to do a bunch of OR'ing, either. Thanks! --Scott Brown -- 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: Need help on WHERE ... LIKE Query
Thanks for all of the responses! Actually, Brent Baisley wins the syntax question of the day. The BETWEEN syntax is what I needed. REGEXP and RLIKE do not return any records, they return a count of the number of rows matching the expression. Thanks! --Scott Brown At 11:22 AM 10/30/2003, you wrote: Hi, List, I looked here: http://www.mysql.com/doc/en/String_comparison_functions.html But I am not seeing what I need. I want to do a string comparison like this: SELECT * FROM sometable WHERE surname LIKE '[A-C]%' ORDER BY surname; This works in another RDBMS. It doesn't return a syntax error, either, but it returns no records. My guess is that MySQL is interpreting the whole thing literally, rather than looking for what I want. I need this to return all records where surname begins with the letters A through C (that is, all records with a surname which begins with A, B, or C). Anybody got a how-to? I'm sure there must be some way, other than to do this three times. Some of these can vary; that is, it may be 0-9, or 0-Z (show all), even, so I don't want to do a bunch of OR'ing, either. Thanks! --Scott Brown -- 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: Need help on WHERE ... LIKE Query
Thanks so much Brent, this is what I was looking for. However, what do I do when I get to 'Z'? I looked here, and now I am really confused: http://www.mysql.com/doc/en/Comparison_Operators.html It seems to say that BETWEEN returns a rowcount as well? I am guessing that these all return records... So, what should I use, RLIKE, BETWEEN or??? Thanks, --Scott At 11:35 AM 10/30/2003, you wrote: This should work for you: SELECT * FROM sometable WHERE surname BETWEEN 'A' AND 'D' ORDER BY surname In my quick test the first parameter is inclusive while the second is not, which is why it is D and not C. On Thursday, October 30, 2003, at 02:22 PM, Scott Brown wrote: I want to do a string comparison like this: SELECT * FROM sometable WHERE surname LIKE '[A-C]%' ORDER BY surname; -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- 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: Need help on WHERE ... LIKE Query
Oh, well, chalk it up to experience. RLIKE is what works the way I want. DOH! Thanks, --Scott Brown At 12:34 PM 10/30/2003, you wrote: Thanks so much Brent, this is what I was looking for. However, what do I do when I get to 'Z'? I looked here, and now I am really confused: http://www.mysql.com/doc/en/Comparison_Operators.html It seems to say that BETWEEN returns a rowcount as well? I am guessing that these all return records... So, what should I use, RLIKE, BETWEEN or??? Thanks, --Scott At 11:35 AM 10/30/2003, you wrote: This should work for you: SELECT * FROM sometable WHERE surname BETWEEN 'A' AND 'D' ORDER BY surname In my quick test the first parameter is inclusive while the second is not, which is why it is D and not C. On Thursday, October 30, 2003, at 02:22 PM, Scott Brown wrote: I want to do a string comparison like this: SELECT * FROM sometable WHERE surname LIKE '[A-C]%' ORDER BY surname; -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- 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: Need help on WHERE ... LIKE Query
The BETWEEN operator works like and greater and less than search. So, you can do the exact same query like this: SELECT * FROM sometable WHERE surname='A' AND surname'D' MySQL may actually optimize them the same way, but using BETWEEN is more readable. To include 'Z', just do a greater than search for what you want: SELECT * FROM sometable WHERE surname='X' The reason why you would want to do it this way instead of using RLIKE is that MySQL will use an index much more efficiently. Do both queries with and explain in front of it. You'll see how MySQL performed the BETWEEN search, or greater/less than, much more efficiently. If you have an index on surname that is. On Thursday, October 30, 2003, at 03:34 PM, Scott Brown wrote: Thanks so much Brent, this is what I was looking for. However, what do I do when I get to 'Z'? I looked here, and now I am really confused: http://www.mysql.com/doc/en/Comparison_Operators.html It seems to say that BETWEEN returns a rowcount as well? I am guessing that these all return records... So, what should I use, RLIKE, BETWEEN or??? Thanks, --Scott -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help on WHERE ... LIKE Query
Hi, No, col BETWEEN 'A' AND 'D' is not the same as col = 'A' AND col 'D'. BETWEEN is equivalent to col = 'A' AND col = 'D'. One will include cols that equal 'D' and the other won't. :-) Matt - Original Message - From: Brent Baisley Sent: Thursday, October 30, 2003 3:12 PM Subject: Re: Need help on WHERE ... LIKE Query The BETWEEN operator works like and greater and less than search. So, you can do the exact same query like this: SELECT * FROM sometable WHERE surname='A' AND surname'D' MySQL may actually optimize them the same way, but using BETWEEN is more readable. To include 'Z', just do a greater than search for what you want: SELECT * FROM sometable WHERE surname='X' The reason why you would want to do it this way instead of using RLIKE is that MySQL will use an index much more efficiently. Do both queries with and explain in front of it. You'll see how MySQL performed the BETWEEN search, or greater/less than, much more efficiently. If you have an index on surname that is. On Thursday, October 30, 2003, at 03:34 PM, Scott Brown wrote: Thanks so much Brent, this is what I was looking for. However, what do I do when I get to 'Z'? I looked here, and now I am really confused: http://www.mysql.com/doc/en/Comparison_Operators.html It seems to say that BETWEEN returns a rowcount as well? I am guessing that these all return records... So, what should I use, RLIKE, BETWEEN or??? Thanks, --Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help constructing query ...
Try the following: SELECT ip_address, url, count(distinct ip_address) FROM tablename GROUP BY ip_address, url At 09:48 PM 10/21/2003, John Kelly wrote: : -Original Message- : From: John Kelly [mailto:[EMAIL PROTECTED] : Sent: Tuesday, October 21, 2003 3:45 PM : To: [EMAIL PROTECTED] : Cc: [EMAIL PROTECTED] : Subject: Re: Need help constructing query ... : : : - Original Message - : From: Daniel Clark [EMAIL PROTECTED] : To: [EMAIL PROTECTED] : Cc: [EMAIL PROTECTED] : Sent: Tuesday, October 21, 2003 2:33 PM : Subject: Re: Need help constructing query ... : : : : Hi, I have a table full of logged urls and ip addresses. : The following : : query returns all the urls and the number of requests. How would I : : modify it to return unique requests based on distinct ip : addresses? : : : : select url, count(*) as pageviews from table group by url order by : : pageviews desc : : : : How about: : : : : SELECT ip_address, url, count(*) : : FROM tablename : : GROUP BY ip_adress, url : : : Thanks but I could not get that to work. It does not appear : to count the number of page requests by distinct IPs anyway : does it? Don't you need something like a : count(distinct(ip_address)) somewhere in there? : : -- - Original Message - From: Kevin Fries [EMAIL PROTECTED] To: 'John Kelly' [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 3:57 PM Subject: RE: Need help constructing query ... : Then I think you want : SELECT url, COUNT(DISTINCT ip_address) : FROM tablename : GROUP BY url; Thanks, this must be a resource intensive query as it works in a few seconds on a small table but takes 6+ minutes when done on a table with just 100,000 records. Anyway, thanks again. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help constructing query ...
Hi, I have a table full of logged urls and ip addresses. The following query returns all the urls and the number of requests. How would I modify it to return unique requests based on distinct ip addresses? select url, count(*) as pageviews from table group by url order by pageviews desc How about: SELECT ip_address, url, count(*) FROM tablename GROUP BY ip_adress, url -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help constructing query ...
- Original Message - From: Daniel Clark [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 2:33 PM Subject: Re: Need help constructing query ... : Hi, I have a table full of logged urls and ip addresses. The following : query returns all the urls and the number of requests. How would I : modify it to return unique requests based on distinct ip addresses? : : select url, count(*) as pageviews from table group by url order by : pageviews desc : : How about: : : SELECT ip_address, url, count(*) : FROM tablename : GROUP BY ip_adress, url : Thanks but I could not get that to work. It does not appear to count the number of page requests by distinct IPs anyway does it? Don't you need something like a count(distinct(ip_address)) somewhere in there? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help constructing query ...
Then I think you want SELECT url, COUNT(DISTINCT ip_address) FROM tablename GROUP BY url; -Original Message- From: John Kelly [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 3:45 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Need help constructing query ... - Original Message - From: Daniel Clark [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 2:33 PM Subject: Re: Need help constructing query ... : Hi, I have a table full of logged urls and ip addresses. The following : query returns all the urls and the number of requests. How would I : modify it to return unique requests based on distinct ip addresses? : : select url, count(*) as pageviews from table group by url order by : pageviews desc : : How about: : : SELECT ip_address, url, count(*) : FROM tablename : GROUP BY ip_adress, url : Thanks but I could not get that to work. It does not appear to count the number of page requests by distinct IPs anyway does it? Don't you need something like a count(distinct(ip_address)) somewhere in there? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help constructing query ...
: -Original Message- : From: John Kelly [mailto:[EMAIL PROTECTED] : Sent: Tuesday, October 21, 2003 3:45 PM : To: [EMAIL PROTECTED] : Cc: [EMAIL PROTECTED] : Subject: Re: Need help constructing query ... : : : - Original Message - : From: Daniel Clark [EMAIL PROTECTED] : To: [EMAIL PROTECTED] : Cc: [EMAIL PROTECTED] : Sent: Tuesday, October 21, 2003 2:33 PM : Subject: Re: Need help constructing query ... : : : : Hi, I have a table full of logged urls and ip addresses. : The following : : query returns all the urls and the number of requests. How would I : : modify it to return unique requests based on distinct ip : addresses? : : : : select url, count(*) as pageviews from table group by url order by : : pageviews desc : : : : How about: : : : : SELECT ip_address, url, count(*) : : FROM tablename : : GROUP BY ip_adress, url : : : Thanks but I could not get that to work. It does not appear : to count the number of page requests by distinct IPs anyway : does it? Don't you need something like a : count(distinct(ip_address)) somewhere in there? : : -- - Original Message - From: Kevin Fries [EMAIL PROTECTED] To: 'John Kelly' [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 3:57 PM Subject: RE: Need help constructing query ... : Then I think you want : SELECT url, COUNT(DISTINCT ip_address) : FROM tablename : GROUP BY url; Thanks, this must be a resource intensive query as it works in a few seconds on a small table but takes 6+ minutes when done on a table with just 100,000 records. Anyway, thanks again. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help writing query
Patrick Shoaf [EMAIL PROTECTED] wrote: I have a table name product defined as follows: Item_Code Item_Size Item_Color Item_img Description Cost Retail_Price Category and other non-essential items, such as qty based on code,size,color The data is as follows: j2400 S BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 M BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 L BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 4XL BLK j2400blk.jpgBlack Jacket18.00 36.00 Jacket p2400 S BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 M BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 L BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 4XL BLK p2400blk.jpgBlack Pants 24.00 48.00 Pants I need the result to show: j2400 S,M,L,4XL BLK j2400blk.jpgBlack Jacket12,12,12,18 p2400 S,M,L,4XL BLK p2400blk.jpgBlack Pants 16,16,16,24 How would you write this query? I used initially SELECT Item_Code,Item_Size,Item_Color,Item_Img,Description,Cost FROM product WHERE Category=Jacket or Category=Pants GROUP BY Item_code This resulted in only retrieving the first item in the list for size price info. There is a GROUP_CONCAT() function, that is available from version 4.1: http://www.mysql.com/doc/en/GROUP-BY-Functions.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: Need help writing query
I loaded MySQL 4.1, but now the text is gibberish in the Group_Concat field SELECT Cat_Item_Img,Cat_Price,Product.Item_Code,GROUP_CONCAT(Item_Size SEPARATOR ;) as sizes,Item_Img, Description,Category,Retail_Price,Short_Desc,Product.Item_Color FROM Cat_Items,Product WHERE cat_code =BoltTech and Cat_Items.Item_Code = Product.Item_Code and Category =Casual Wear GROUP BY Product.Item_Code RESULTS: | Cat_Item_Img | Cat_Price | Item_Code | sizes | Item_Img | Description | Category| | | 19.00 | 288 | ?L.MYI | 288 | bPromenade/b Blended Knit Shirt | Casual Wear | | | 28.00 | 71080 | ?L.MYI | 71080| bLee Denim Shirt/bbr | Casual Wear | | | 24.00 | 77123 | ?L.MYI | 77123| bLee Golf Shirt/bbr | Casual Wear | The data should read S;M;L;XL;2XL;3XL not ?L.MYI (?L.MYI is translation when using copy/paste, there were other characters in sizes field.) Is this a 4.1 Bug? I loaded from the 4.1.0-0 Linux x86 RPM files. So far nothing else appears broken. Any suggestions? Patrick At 09:11 AM 9/18/2003, you wrote: Patrick Shoaf [EMAIL PROTECTED] wrote: I have a table name product defined as follows: Item_Code Item_Size Item_Color Item_img Description Cost Retail_Price Category and other non-essential items, such as qty based on code,size,color The data is as follows: j2400 S BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 M BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 L BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 4XL BLK j2400blk.jpgBlack Jacket18.00 36.00 Jacket p2400 S BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 M BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 L BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 4XL BLK p2400blk.jpgBlack Pants 24.00 48.00 Pants I need the result to show: j2400 S,M,L,4XL BLK j2400blk.jpgBlack Jacket12,12,12,18 p2400 S,M,L,4XL BLK p2400blk.jpgBlack Pants 16,16,16,24 How would you write this query? I used initially SELECT Item_Code,Item_Size,Item_Color,Item_Img,Description,Cost FROM product WHERE Category=Jacket or Category=Pants GROUP BY Item_code This resulted in only retrieving the first item in the list for size price info. There is a GROUP_CONCAT() function, that is available from version 4.1: http://www.mysql.com/doc/en/GROUP-BY-Functions.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] Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with the download of the Mysql GUI
- Original Message - I would use MySQL CC (command center, I think...)... I seem to remember someone mentioning that mysql gui is discontinued... CC is available from the mysql.org site, and is very easy to install on windows xp (it's on my laptop...) /originalmessage I second that. It's easy on Linux too. Licks SQL Server 2000 Enterprise manager into a cocked hat (as my mother used to say) Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help with the download of the Mysql GUI
I would use MySQL CC (command center, I think...)... I seem to remember someone mentioning that mysql gui is discontinued... CC is available from the mysql.org site, and is very easy to install on windows xp (it's on my laptop...) -Original Message- From: Liwen Han [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 2:34 PM To: [EMAIL PROTECTED] Subject: Need help with the download of the Mysql GUI To whom it may concerned, I am a web development support person working for North Carolina AT State University. I am involved in a library project right now which requires a MySQL free software installed on my computer so I can create a mysql database for the project. By searching the internet I found that MySQL GUI would be the perfect software for me to try. But I am not quite sure how to download it on my computer which has windows XP operating system. Could you give me instructions on how to download the software so I can start using it as soon as possible because the project deadline is getting closer. Thanks a lot! Looking forward to hearing from you! Liwen -- 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: Need help with the download of the Mysql GUI
Dan Greene wrote: I would use MySQL CC (command center, I think...)... I seem to remember someone mentioning that mysql gui is discontinued... You remember right, the MySQL GUI IS discontinued. Why it is still availiable for download, ask MySQL Jakob ^-- To Unix or not to Unix. That is the question whether 'tis nobler in the mind to suffer slings and arrows of vast documentation or to take arms against a sea of buggy OS and by raping the support lines end then? ; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with oracledump (contributed program)
On Mon, Sep 01, 2003 at 11:09:02AM -0700, Martin Gainty wrote: Doug I copied this from an SAP integration with Orace site http://saphelp.cob.csuchico.edu/OraHelp/Darwin36/inst36fhp/html/5-odbc.htm 5.5.3 tsnames.ora File the file ORACLE_HOME/network/admin/tnsnames.ora must point to the listener (i.e., the TCP/IP port) on the database server. (ORACLE_HOME is the directory on Unix where Oracle is installed.) For example, ora_db0_net= (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=test-console.think.com) (PORT=1521) ) (CONNECT_DATA= (SID=ORCL) ) ) Note: Do not use tabs in the file tnsnames.ora. *OracleDump is performed by (SID=ORCL)* Keep us apprised to your progress... Hi Marty, Thanks for following up. I've created a tnsnames.ora file in /usr/local/oracle/9.0.1/network/admin with the settings appropriate to my host. When I run the following commands: % setenv ORACLE_HOME /usr/local/oracle/9.0.1 % setenv ORACLE_SID VAPDEV % oracledump -c -u myUser -p myPassword I get the error message... % Can't call method do on an undefined value at /home/doug/bin/oracledump line 95. When I run it with the -x switch I see... Configuration: (remove --explain/-x option to run with this configuration) Database SID: VAPDEV Database user: myUser Database password: myPassword Tables: All tables Options: --default-databaseUse default database (VAPDEV) --with-table-comments Include table comments --with-column-commentsInclude column comments --default-precision Set to 18 --default-scale Set to 0 --complete-insert Includes list of column names in insert statements I'm not a perl guy and I'm not sure what to make of it other than the variables $nls_date_format, $nls_time_format, $nls_timestamp_format have data at run-time. Thanks again for your help so far... -- Regards, Doug Marty Gainty - Original Message - From: Doug Poland [EMAIL PROTECTED] To: Jim Smith [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, September 01, 2003 6:18 AM Subject: Re: Need help with oracledump (contributed program) On Mon, Sep 01, 2003 at 01:03:48PM +0100, Jim Smith wrote: I'm trying to figure out how to use the contributed program oracledump in an environment where I don't have a login to the *nix host running Oracle. All my connectivity to the Oracle host is via port 1521 and JDBC. The oracle dump command seems to be looking for a SID in a file called tnsnames.ora. Those do not exist on the system I am using. Is it possible to still use oracledump in this case? I doubt it. It looks as if oracledump requires Oracle's network layer (SQL*Net) and unless you have that you can't do anything. The oracle thin JDBC driver implements SQL*Net for java only, but the other Oracle jdbc drivers require SQL*Net Thanks for the reply. Looks like I'll have to roll my own in Java. -- Regards, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help with oracledump (contributed program)
A TNSNAMES file isn't going to help unless you have the Oracle client software installed. If you had the software, you would already have a tnsnames file. -Original Message- From: Doug Poland [mailto:[EMAIL PROTECTED] Sent: 02 September 2003 02:58 To: Martin Gainty Cc: [EMAIL PROTECTED] Subject: Re: Need help with oracledump (contributed program) On Mon, Sep 01, 2003 at 11:09:02AM -0700, Martin Gainty wrote: Doug I copied this from an SAP integration with Orace site http://saphelp.cob.csuchico.edu/OraHelp/Darwin36/inst36fhp/htm l/5-odbc.htm 5.5.3 tsnames.ora File the file ORACLE_HOME/network/admin/tnsnames.ora must point to the listener (i.e., the TCP/IP port) on the database server. (ORACLE_HOME is the directory on Unix where Oracle is installed.) For example, ora_db0_net= (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=test-console.think.com) (PORT=1521) ) (CONNECT_DATA= (SID=ORCL) ) ) Note: Do not use tabs in the file tnsnames.ora. *OracleDump is performed by (SID=ORCL)* Keep us apprised to your progress... Hi Marty, Thanks for following up. I've created a tnsnames.ora file in /usr/local/oracle/9.0.1/network/admin with the settings appropriate to my host. When I run the following commands: % setenv ORACLE_HOME /usr/local/oracle/9.0.1 % setenv ORACLE_SID VAPDEV % oracledump -c -u myUser -p myPassword I get the error message... % Can't call method do on an undefined value at /home/doug/bin/oracledump line 95. When I run it with the -x switch I see... Configuration: (remove --explain/-x option to run with this configuration) Database SID: VAPDEV Database user: myUser Database password: myPassword Tables: All tables Options: --default-databaseUse default database (VAPDEV) --with-table-comments Include table comments --with-column-commentsInclude column comments --default-precision Set to 18 --default-scale Set to 0 --complete-insert Includes list of column names in insert statements I'm not a perl guy and I'm not sure what to make of it other than the variables $nls_date_format, $nls_time_format, $nls_timestamp_format have data at run-time. Thanks again for your help so far... -- Regards, Doug Marty Gainty - Original Message - From: Doug Poland [EMAIL PROTECTED] To: Jim Smith [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, September 01, 2003 6:18 AM Subject: Re: Need help with oracledump (contributed program) On Mon, Sep 01, 2003 at 01:03:48PM +0100, Jim Smith wrote: I'm trying to figure out how to use the contributed program oracledump in an environment where I don't have a login to the *nix host running Oracle. All my connectivity to the Oracle host is via port 1521 and JDBC. The oracle dump command seems to be looking for a SID in a file called tnsnames.ora. Those do not exist on the system I am using. Is it possible to still use oracledump in this case? I doubt it. It looks as if oracledump requires Oracle's network layer (SQL*Net) and unless you have that you can't do anything. The oracle thin JDBC driver implements SQL*Net for java only, but the other Oracle jdbc drivers require SQL*Net Thanks for the reply. Looks like I'll have to roll my own in Java. -- Regards, Doug -- 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: Need help with oracledump (contributed program)
On Tue, Sep 02, 2003 at 12:25:35PM +0100, Jim Smith wrote: A TNSNAMES file isn't going to help unless you have the Oracle client software installed. If you had the software, you would already have a tnsnames file. Thanks all for your help. I've found a free java-based application (JOracleDump) and am modifying that to do what I need. -- Regards, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help with oracledump (contributed program)
I'm trying to figure out how to use the contributed program oracledump in an environment where I don't have a login to the *nix host running Oracle. All my connectivity to the Oracle host is via port 1521 and JDBC. The oracle dump command seems to be looking for a SID in a file called tnsnames.ora. Those do not exist on the system I am using. Is it possible to still use oracledump in this case? I doubt it. It looks as if oracledump requires Oracle's network layer (SQL*Net) and unless you have that you can't do anything. The oracle thin JDBC driver implements SQL*Net for java only, but the other Oracle jdbc drivers require SQL*Net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with oracledump (contributed program)
On Mon, Sep 01, 2003 at 01:03:48PM +0100, Jim Smith wrote: I'm trying to figure out how to use the contributed program oracledump in an environment where I don't have a login to the *nix host running Oracle. All my connectivity to the Oracle host is via port 1521 and JDBC. The oracle dump command seems to be looking for a SID in a file called tnsnames.ora. Those do not exist on the system I am using. Is it possible to still use oracledump in this case? I doubt it. It looks as if oracledump requires Oracle's network layer (SQL*Net) and unless you have that you can't do anything. The oracle thin JDBC driver implements SQL*Net for java only, but the other Oracle jdbc drivers require SQL*Net Thanks for the reply. Looks like I'll have to roll my own in Java. -- Regards, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with oracledump (contributed program)
Doug I copied this from an SAP integration with Orace site http://saphelp.cob.csuchico.edu/OraHelp/Darwin36/inst36fhp/html/5-odbc.htm 5.5.3 tsnames.ora File the file ORACLE_HOME/network/admin/tnsnames.ora must point to the listener (i.e., the TCP/IP port) on the database server. (ORACLE_HOME is the directory on Unix where Oracle is installed.) For example, ora_db0_net= (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=test-console.think.com) (PORT=1521) ) (CONNECT_DATA= (SID=ORCL) ) ) Note: Do not use tabs in the file tnsnames.ora. *OracleDump is performed by (SID=ORCL)* Keep us apprised to your progress... Marty Gainty - Original Message - From: Doug Poland [EMAIL PROTECTED] To: Jim Smith [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, September 01, 2003 6:18 AM Subject: Re: Need help with oracledump (contributed program) On Mon, Sep 01, 2003 at 01:03:48PM +0100, Jim Smith wrote: I'm trying to figure out how to use the contributed program oracledump in an environment where I don't have a login to the *nix host running Oracle. All my connectivity to the Oracle host is via port 1521 and JDBC. The oracle dump command seems to be looking for a SID in a file called tnsnames.ora. Those do not exist on the system I am using. Is it possible to still use oracledump in this case? I doubt it. It looks as if oracledump requires Oracle's network layer (SQL*Net) and unless you have that you can't do anything. The oracle thin JDBC driver implements SQL*Net for java only, but the other Oracle jdbc drivers require SQL*Net Thanks for the reply. Looks like I'll have to roll my own in Java. -- Regards, Doug -- 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: Need help optimizing query, awfully slow on only 20000 records
Are your tables indexed? http://www.mysql.com/doc/en/MySQL_indexes.html Saqib Ali - http://www.xml-dev.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help optimizing query, awfully slow on only 20000 records
Absolutely! I have multiple indexes. I think it might be a problem with ODBC Are your tables indexed? http://www.mysql.com/doc/en/MySQL_indexes.html Saqib Ali - http://www.xml-dev.com - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help optimizing query, awfully slow on only 20000 records
When following query is pulled up, it takes about whole 1-2 minutes to come up. inquiries table has 17000 records, contacts has about 7000, individual_contacts has about 16000. It has gotten worse once I upgraded to 4.0 and latest MyODBC. Clients are separate machines (mix of Win98 and WinXP). Those 20K records is what feeds the Access97 form, pull down list filters out some and pulls up about 3K and people just start typing a name and then (since there multiple inquiries for some clients) pull down the list to pick inquiry they want. What are you defining as a huge performance hit? Is the result set 20K records, or the base tables? How big are the base tables? Are the client and server on the same machine? Pulling 20K records across the network could take some time. Formatting 20K records into a pull -down list in access will also take a long time. Anyway who reads a 20K list? Which parts of the process are slow? How does the query perform from the mysql command line? -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: 19 August 2003 17:29 To: [EMAIL PROTECTED] Subject: Need help optimizing query, awfully slow on only 2 records 1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0 I have about 20K records that result from the following query. Front end for the database is ACCESS97 and pulling up 20K records makes a huge performance hit. For the form in question I am using PASSTHROUGH type query (the one that just passes everything straight to server without ODBC). NOTE: souce_for_inquiries_form is the join table and is searchable in the from (it feeds a pull-down list). SELECT inquiries.inquiry_id, inquiries.contact_id, inquiries.indiv_contact_id, inquiries.phone, inquiries.fax, inquiries.agent_id, inquiries.inquiry_date, inquiries.event_type, inquiries.letter_type, inquiries.event_date, inquiries.event_date_general, inquiries.event_location, inquiries.guests, inquiries.hours, inquiries.budget, inquiries.event_description, inquiries.talent_description, inquiries.past_use, inquiries.referred_by, inquiries.date_sent, inquiries.end_user, inquiries.event_id, inquiries.notes, source_for_inquiries_form.organization, source_for_inquiries_form.mailing_address_1, source_for_inquiries_form.mailing_address_2, source_for_inquiries_form.city, source_for_inquiries_form.state, source_for_inquiries_form.zip, source_for_inquiries_form.contact_type, individual_contacts.contact_name_first, individual_contacts.contact_name_last, individual_contacts.contact_prefix, individual_contacts.contact_title, individual_contacts.email FROM inquiries LEFT JOIN individual_contacts ON inquiries.indiv_contact_id = individual_contacts.indiv_contact_id INNER JOIN contacts AS source_for_inquiries_form ON inquiries.contact_id = source_for_inquiries_form.contact_id ORDER BY inquiries.inquiry_id DESC; - - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help optimizing query, awfully slow on only 20000 records
Result of EXPLAIN is: table|type|possible_keys|key|key_len|ref|rows|Extra inquiries|ALL|contact_id| | | |8253|Using filesort individual_contacts|eq_ref|PRIMARY,indiv_contact_id|PRIMARY|3|inquiries.indiv_contact_id|1 source_for_inquiries_form|eq_ref|PRIMARY,contact_id|PRIMARY|3|inquiries.contact_id|1 What does EXPLAIN SELECT query show? Have you read the chapter in the manual on optimizing queries? Do you have all the proper indices set up? --Michael -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 2:52 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Need help optimizing query, awfully slow on only 2 records When following query is pulled up, it takes about whole 1-2 minutes to come up. inquiries table has 17000 records, contacts has about 7000, individual_contacts has about 16000. It has gotten worse once I upgraded to 4.0 and latest MyODBC. Clients are separate machines (mix of Win98 and WinXP). Those 20K records is what feeds the Access97 form, pull down list filters out some and pulls up about 3K and people just start typing a name and then (since there multiple inquiries for some clients) pull down the list to pick inquiry they want. What are you defining as a huge performance hit? Is the result set 20K records, or the base tables? How big are the base tables? Are the client and server on the same machine? Pulling 20K records across the network could take some time. Formatting 20K records into a pull -down list in access will also take a long time. Anyway who reads a 20K list? Which parts of the process are slow? How does the query perform from the mysql command line? -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: 19 August 2003 17:29 To: [EMAIL PROTECTED] Subject: Need help optimizing query, awfully slow on only 2 records 1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0 I have about 20K records that result from the following query. Front end for the database is ACCESS97 and pulling up 20K records makes a huge performance hit. For the form in question I am using PASSTHROUGH type query (the one that just passes everything straight to server without ODBC). NOTE: souce_for_inquiries_form is the join table and is searchable in the from (it feeds a pull-down list). SELECT inquiries.inquiry_id, inquiries.contact_id, inquiries.indiv_contact_id, inquiries.phone, inquiries.fax, inquiries.agent_id, inquiries.inquiry_date, inquiries.event_type, inquiries.letter_type, inquiries.event_date, inquiries.event_date_general, inquiries.event_location, inquiries.guests, inquiries.hours, inquiries.budget, inquiries.event_description, inquiries.talent_description, inquiries.past_use, inquiries.referred_by, inquiries.date_sent, inquiries.end_user, inquiries.event_id, inquiries.notes, source_for_inquiries_form.organization, source_for_inquiries_form.mailing_address_1, source_for_inquiries_form.mailing_address_2, source_for_inquiries_form.city, source_for_inquiries_form.state, source_for_inquiries_form.zip, source_for_inquiries_form.contact_type, individual_contacts.contact_name_first, individual_contacts.contact_name_last, individual_contacts.contact_prefix, individual_contacts.contact_title, individual_contacts.email FROM inquiries LEFT JOIN individual_contacts ON inquiries.indiv_contact_id = individual_contacts.indiv_contact_id INNER JOIN contacts AS source_for_inquiries_form ON inquiries.contact_id = source_for_inquiries_form.contact_id ORDER BY inquiries.inquiry_id DESC; - - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] Apolinaras Apollo Sinkevicius Carmel Music Entertainment, LLC 701 Main Street Evanston, IL 60202 Phone: (847) 864-5969 X110 Fax: (847) 864-6149 Toll-free: 800-276-5969 X110 e-mail: [EMAIL PROTECTED] web-site: http://carmelme.com Having an event in Chicago, or would you like to bring Chicago entertainment to your event? Give Carmel Music Entertainment a call for the finest entertainment available in Chicago. - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL
Re: Need help optimizing query, awfully slow on only 20000 records
Query takes 3.4 seconds to run on the server, but it takes 1-2minutes to run via MyODBC 3.51.06 using passthrough (Access97 is the front end, but it has query type that allows bypass of Access interpretation. Two Questions: Is the same query running directly on the linux server thru mysql is also very slow ? Have you done a explain plan on the query ? Marc. - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help optimizing query, awfully slow on only 20000 records
When I send the query throuh comman line, it works perfect 3-4 seconds, but when I do Access97 pass-through query, that is when it runs into 1-2 minutes. It is almost as slow as using Access97 native query that goes through MyODBC, so... That doesn't look too bad. Is the query that slow when you use the command-line client alone (connected directly to the server), or is it just when accessing the database through the ODBC tunnel? --Michael -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 3:14 PM To: Michael S. Fischer Cc: [EMAIL PROTECTED] Subject: RE: Need help optimizing query, awfully slow on only 2 records Result of EXPLAIN is: table|type|possible_keys|key|key_len|ref|rows|Extra inquiries|ALL|contact_id| | | |8253|Using filesort individual_contacts|eq_ref|PRIMARY,indiv_contact_id|PRIMARY|3| inquiries.indiv_contact_id|1 source_for_inquiries_form|eq_ref|PRIMARY,contact_id|PRIMARY|3| inquiries.contact_id|1 What does EXPLAIN SELECT query show? Have you read the chapter in the manual on optimizing queries? Do you have all the proper indices set up? --Michael -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 2:52 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Need help optimizing query, awfully slow on only 2 records When following query is pulled up, it takes about whole 1-2 minutes to come up. inquiries table has 17000 records, contacts has about 7000, individual_contacts has about 16000. It has gotten worse once I upgraded to 4.0 and latest MyODBC. Clients are separate machines (mix of Win98 and WinXP). Those 20K records is what feeds the Access97 form, pull down list filters out some and pulls up about 3K and people just start typing a name and then (since there multiple inquiries for some clients) pull down the list to pick inquiry they want. What are you defining as a huge performance hit? Is the result set 20K records, or the base tables? How big are the base tables? Are the client and server on the same machine? Pulling 20K records across the network could take some time. Formatting 20K records into a pull -down list in access will also take a long time. Anyway who reads a 20K list? Which parts of the process are slow? How does the query perform from the mysql command line? -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: 19 August 2003 17:29 To: [EMAIL PROTECTED] Subject: Need help optimizing query, awfully slow on only 2 records 1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0 I have about 20K records that result from the following query. Front end for the database is ACCESS97 and pulling up 20K records makes a huge performance hit. For the form in question I am using PASSTHROUGH type query (the one that just passes everything straight to server without ODBC). NOTE: souce_for_inquiries_form is the join table and is searchable in the from (it feeds a pull-down list). SELECT inquiries.inquiry_id, inquiries.contact_id, inquiries.indiv_contact_id, inquiries.phone, inquiries.fax, inquiries.agent_id, inquiries.inquiry_date, inquiries.event_type, inquiries.letter_type, inquiries.event_date, inquiries.event_date_general, inquiries.event_location, inquiries.guests, inquiries.hours, inquiries.budget, inquiries.event_description, inquiries.talent_description, inquiries.past_use, inquiries.referred_by, inquiries.date_sent, inquiries.end_user, inquiries.event_id, inquiries.notes, source_for_inquiries_form.organization, source_for_inquiries_form.mailing_address_1, source_for_inquiries_form.mailing_address_2, source_for_inquiries_form.city, source_for_inquiries_form.state, source_for_inquiries_form.zip, source_for_inquiries_form.contact_type, individual_contacts.contact_name_first, individual_contacts.contact_name_last, individual_contacts.contact_prefix, individual_contacts.contact_title, individual_contacts.email FROM inquiries LEFT JOIN individual_contacts ON inquiries.indiv_contact_id = individual_contacts.indiv_contact_id INNER JOIN contacts AS source_for_inquiries_form ON inquiries.contact_id = source_for_inquiries_form.contact_id ORDER BY inquiries.inquiry_id DESC; - - Visit
RE: Need help optimizing query, awfully slow on only 20000 records
That doesn't look too bad. Is the query that slow when you use the command-line client alone (connected directly to the server), or is it just when accessing the database through the ODBC tunnel? --Michael -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 3:14 PM To: Michael S. Fischer Cc: [EMAIL PROTECTED] Subject: RE: Need help optimizing query, awfully slow on only 2 records Result of EXPLAIN is: table|type|possible_keys|key|key_len|ref|rows|Extra inquiries|ALL|contact_id| | | |8253|Using filesort individual_contacts|eq_ref|PRIMARY,indiv_contact_id|PRIMARY|3| inquiries.indiv_contact_id|1 source_for_inquiries_form|eq_ref|PRIMARY,contact_id|PRIMARY|3| inquiries.contact_id|1 What does EXPLAIN SELECT query show? Have you read the chapter in the manual on optimizing queries? Do you have all the proper indices set up? --Michael -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 2:52 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Need help optimizing query, awfully slow on only 2 records When following query is pulled up, it takes about whole 1-2 minutes to come up. inquiries table has 17000 records, contacts has about 7000, individual_contacts has about 16000. It has gotten worse once I upgraded to 4.0 and latest MyODBC. Clients are separate machines (mix of Win98 and WinXP). Those 20K records is what feeds the Access97 form, pull down list filters out some and pulls up about 3K and people just start typing a name and then (since there multiple inquiries for some clients) pull down the list to pick inquiry they want. What are you defining as a huge performance hit? Is the result set 20K records, or the base tables? How big are the base tables? Are the client and server on the same machine? Pulling 20K records across the network could take some time. Formatting 20K records into a pull -down list in access will also take a long time. Anyway who reads a 20K list? Which parts of the process are slow? How does the query perform from the mysql command line? -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: 19 August 2003 17:29 To: [EMAIL PROTECTED] Subject: Need help optimizing query, awfully slow on only 2 records 1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0 I have about 20K records that result from the following query. Front end for the database is ACCESS97 and pulling up 20K records makes a huge performance hit. For the form in question I am using PASSTHROUGH type query (the one that just passes everything straight to server without ODBC). NOTE: souce_for_inquiries_form is the join table and is searchable in the from (it feeds a pull-down list). SELECT inquiries.inquiry_id, inquiries.contact_id, inquiries.indiv_contact_id, inquiries.phone, inquiries.fax, inquiries.agent_id, inquiries.inquiry_date, inquiries.event_type, inquiries.letter_type, inquiries.event_date, inquiries.event_date_general, inquiries.event_location, inquiries.guests, inquiries.hours, inquiries.budget, inquiries.event_description, inquiries.talent_description, inquiries.past_use, inquiries.referred_by, inquiries.date_sent, inquiries.end_user, inquiries.event_id, inquiries.notes, source_for_inquiries_form.organization, source_for_inquiries_form.mailing_address_1, source_for_inquiries_form.mailing_address_2, source_for_inquiries_form.city, source_for_inquiries_form.state, source_for_inquiries_form.zip, source_for_inquiries_form.contact_type, individual_contacts.contact_name_first, individual_contacts.contact_name_last, individual_contacts.contact_prefix, individual_contacts.contact_title, individual_contacts.email FROM inquiries LEFT JOIN individual_contacts ON inquiries.indiv_contact_id = individual_contacts.indiv_contact_id INNER JOIN contacts AS source_for_inquiries_form ON inquiries.contact_id = source_for_inquiries_form.contact_id ORDER BY inquiries.inquiry_id DESC; - - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] Apolinaras Apollo Sinkevicius Carmel Music Entertainment, LLC 701 Main Street Evanston, IL 60202
RE: Need help optimizing query, awfully slow on only 20000 records
What does EXPLAIN SELECT query show? Have you read the chapter in the manual on optimizing queries? Do you have all the proper indices set up? --Michael -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 2:52 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Need help optimizing query, awfully slow on only 2 records When following query is pulled up, it takes about whole 1-2 minutes to come up. inquiries table has 17000 records, contacts has about 7000, individual_contacts has about 16000. It has gotten worse once I upgraded to 4.0 and latest MyODBC. Clients are separate machines (mix of Win98 and WinXP). Those 20K records is what feeds the Access97 form, pull down list filters out some and pulls up about 3K and people just start typing a name and then (since there multiple inquiries for some clients) pull down the list to pick inquiry they want. What are you defining as a huge performance hit? Is the result set 20K records, or the base tables? How big are the base tables? Are the client and server on the same machine? Pulling 20K records across the network could take some time. Formatting 20K records into a pull -down list in access will also take a long time. Anyway who reads a 20K list? Which parts of the process are slow? How does the query perform from the mysql command line? -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: 19 August 2003 17:29 To: [EMAIL PROTECTED] Subject: Need help optimizing query, awfully slow on only 2 records 1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0 I have about 20K records that result from the following query. Front end for the database is ACCESS97 and pulling up 20K records makes a huge performance hit. For the form in question I am using PASSTHROUGH type query (the one that just passes everything straight to server without ODBC). NOTE: souce_for_inquiries_form is the join table and is searchable in the from (it feeds a pull-down list). SELECT inquiries.inquiry_id, inquiries.contact_id, inquiries.indiv_contact_id, inquiries.phone, inquiries.fax, inquiries.agent_id, inquiries.inquiry_date, inquiries.event_type, inquiries.letter_type, inquiries.event_date, inquiries.event_date_general, inquiries.event_location, inquiries.guests, inquiries.hours, inquiries.budget, inquiries.event_description, inquiries.talent_description, inquiries.past_use, inquiries.referred_by, inquiries.date_sent, inquiries.end_user, inquiries.event_id, inquiries.notes, source_for_inquiries_form.organization, source_for_inquiries_form.mailing_address_1, source_for_inquiries_form.mailing_address_2, source_for_inquiries_form.city, source_for_inquiries_form.state, source_for_inquiries_form.zip, source_for_inquiries_form.contact_type, individual_contacts.contact_name_first, individual_contacts.contact_name_last, individual_contacts.contact_prefix, individual_contacts.contact_title, individual_contacts.email FROM inquiries LEFT JOIN individual_contacts ON inquiries.indiv_contact_id = individual_contacts.indiv_contact_id INNER JOIN contacts AS source_for_inquiries_form ON inquiries.contact_id = source_for_inquiries_form.contact_id ORDER BY inquiries.inquiry_id DESC; - - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help in querying two tables
SELECT [field list] FROM archivetable,currenttable WHERE archivetable.username=currenttable.username notes: can use join,left,right,select inside select aka subselect check the manual for detail Regards, -- Aftab Jahan Subedar Software Engineer Subedar Technologies Subedar Baag Bibir Bagicha #1 81/1-A North Jatrbari Dhaka 1204 Bangladesh sms://+447765341890 sms://+880171859159 http://www.ceobangladesh.com http://www.DhakaStockExchangeGame.com http://www.geocities.com/jahan.geo Fred van Engen wrote: Hi, On Fri, Aug 01, 2003 at 11:55:36PM +0800, Jaime Teng wrote: Now, I have two of these tables (archivetable,currenttable). My problem is how do I perform a single query such that I get results from these two tables: mysql select * from archivetable,currenttable; +++-+-+ | sessionid | username | logon | logoff | +++-+-+ | 03 | dangco77 | 1996-09-25 20:51:59 | 1996-09-25 21:07:00 | | 06 | mccarthy | 1996-09-26 06:15:35 | 1996-09-26 06:20:00 | | 07 | sigmaph| 1996-09-26 06:25:48 | 1996-09-26 06:28:00 | | 09 | sigmaph| 1996-09-26 08:31:53 | 1996-09-26 08:51:00 | | 1000265891 | okame | 2003-08-01 13:38:24 | 2003-08-01 13:43:42 | | 1000265893 | kbs| 2003-08-01 13:38:30 | 2003-08-01 13:38:48 | | 1000265897 | bdo-albaro | 2003-08-01 13:38:54 | 2003-08-01 14:07:06 | +++-+-+ of course that last query isnt correct but thats the result I want. You can use a UNION to do this, but you need MySQL 4.x. It won't work in 3.23.x or before. Look in the manual for details. any suggestion? I read about using JOIN but I have no idea how to\ make it work for my need. Joins are used for combining records from multiple tables, which is not what you seem to want to do. Regards, Fred. Regards, -- Aftab Jahan Subedar Software Engineer Subedar Technologies Subedar Baag Bibir Bagicha #1 81/1-A North Jatrbari Dhaka 1204 Bangladesh sms://+447765341890 sms://+880171859159 http://www.ceobangladesh.com http://www.DhakaStockExchangeGame.com http://www.geocities.com/jahan.geo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help in querying two tables
Hi, On Fri, Aug 01, 2003 at 11:55:36PM +0800, Jaime Teng wrote: Now, I have two of these tables (archivetable,currenttable). My problem is how do I perform a single query such that I get results from these two tables: mysql select * from archivetable,currenttable; +++-+-+ | sessionid | username | logon | logoff | +++-+-+ | 03 | dangco77 | 1996-09-25 20:51:59 | 1996-09-25 21:07:00 | | 06 | mccarthy | 1996-09-26 06:15:35 | 1996-09-26 06:20:00 | | 07 | sigmaph| 1996-09-26 06:25:48 | 1996-09-26 06:28:00 | | 09 | sigmaph| 1996-09-26 08:31:53 | 1996-09-26 08:51:00 | | 1000265891 | okame | 2003-08-01 13:38:24 | 2003-08-01 13:43:42 | | 1000265893 | kbs| 2003-08-01 13:38:30 | 2003-08-01 13:38:48 | | 1000265897 | bdo-albaro | 2003-08-01 13:38:54 | 2003-08-01 14:07:06 | +++-+-+ of course that last query isnt correct but thats the result I want. You can use a UNION to do this, but you need MySQL 4.x. It won't work in 3.23.x or before. Look in the manual for details. any suggestion? I read about using JOIN but I have no idea how to\ make it work for my need. Joins are used for combining records from multiple tables, which is not what you seem to want to do. 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: Need help in querying two tables
* Jaime Teng [...] Now, I have two of these tables (archivetable,currenttable). My problem is how do I perform a single query such that I get results from these two tables: mysql select * from archivetable,currenttable; +++-+-+ | sessionid | username | logon | logoff | +++-+-+ | 03 | dangco77 | 1996-09-25 20:51:59 | 1996-09-25 21:07:00 | | 06 | mccarthy | 1996-09-26 06:15:35 | 1996-09-26 06:20:00 | | 07 | sigmaph| 1996-09-26 06:25:48 | 1996-09-26 06:28:00 | | 09 | sigmaph| 1996-09-26 08:31:53 | 1996-09-26 08:51:00 | | 1000265891 | okame | 2003-08-01 13:38:24 | 2003-08-01 13:43:42 | | 1000265893 | kbs| 2003-08-01 13:38:30 | 2003-08-01 13:38:48 | | 1000265897 | bdo-albaro | 2003-08-01 13:38:54 | 2003-08-01 14:07:06 | +++-+-+ of course that last query isnt correct but thats the result I want. any suggestion? I read about using JOIN but I have no idea how to\ make it work for my need. Like Fred said, UNION if you use 4.x, but if you use 3.23.x, you could use the MERGE tables option: URL: http://www.mysql.com/doc/en/MERGE.html You basically create a 'wrapper' table around your existing two (or more) identically strucured tables, and can query them all in a single SELECT like if it was a single table. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help: cannot connect on localhost (10061)
At 15:15 -0500 7/18/03, Kyle Goetz wrote: hey, i'm new to mySQL...this meaning that i have tried ~1 times to install it and get it working over the past few weeks...and it always gives me the same error (scroll further down to see it) despite following the manual's windows installation exactly i have version 4.0.13 for windows and i used the installer once i was done, i followed the instructions in the manual exactly i have no firewall running i am on windows XP i am running the client and server on the same computer i ran winmysqladmin and it starts up fine the 'mysqld-nt' service is in my processes tab of task manager i go to dos prompt and type net start and mysql is there running fine You're sure? Look in C:\mysql\data for the .err file and take a look at it. Does it indicate any problems starting the server? The error you show below is typical for a situation in which the MySQL server is not actually running. however, when in c:\mysql\bin and i run 'mySQL' i get this error: ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) i have tried devshed, mysql help, etc. and cannot figure out why mine doesn't work...it seems most people just reboot and it voile! works... but i've rebooted over and over and over, and nothing changes...same error... when i use mySQL Control Center v0.9.2-beta i get the same error when i click Action-Connect all i can say is ARGH! all i can ask is does anyone have any idea why this happens? or how to stop it? i can telnet to port 3306, and get this readout (i have no idea how really to use telnet...) c:\telnet localhost 3306 * 4.0.13-nt?;[EMAIL PROTECTED],? ??ܶBad handshake Connection to host lost. (this when i press backspace, so i understand you don't do that in telnet...) i guess it is getting to mySQL, cuz the 4.0.13 at the beginning is my version of mySQL i'll paste my my.ini (which is in c:\windows) thanks so much, \Kyle, who has been frustrated for a while now... --beginning of my.ini #This File was made using the WinMySQLAdmin 1.4 Tool #7/18/2003 3:06:59 PM #Uncomment or Add only the keys that you know how works. #Read the MySQL Manual for instructions [mysqld] basedir=C:/mysql #bind-address=127.0.0.1 datadir=C:/mysql/data #language=C:/mysql/share/your language directory #slow query log#= #tmpdir#= #port=3306 #set-variable=key_buffer=16M [WinMySQLadmin] Server=C:/mysql/bin/mysqld-nt.exe user=sakurak password=sfzero2 --end of my.ini -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help
At 17:42 -0500 7/6/03, Greg Donald wrote: my coulmn (id) is an auto_increment coulmn and lets say that i have 100 entries, so the problem is that when i delete all entries on my table, the column (id) starts from 101 ,shouldn't is starts from 1 again! please i need help with this Use truncate table, it will reset the auto_incement. Always? :-) DROP TABLE IF EXISTS t; CREATE TABLE t (i INT NOT NULL AUTO_INCREMENT PRIMARY KEY) TYPE = INNODB; INSERT INTO t SET i = NULL; INSERT INTO t SET i = NULL; INSERT INTO t SET i = NULL; SELECT * FROM t; TRUNCATE TABLE t; INSERT INTO t SET i = NULL; SELECT * FROM t; Here's what I get: +---+ | i | +---+ | 1 | | 2 | | 3 | +---+ +---+ | i | +---+ | 4 | +---+ -- Greg Donald http://destiney.com/ -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with select statement
At 4:20 -0500 7/7/03, Anthony Scism wrote: I am relatively new at this, but is there any way to perform the following: | obs_date | date | YES | | NULL | | | obs_time | time | YES | | NULL | | | object | varchar(40) | YES | | NULL | | | observing_site | varchar(40) | YES | | NULL | | | constellation | varchar(40) | YES | | NULL | | | seeing | varchar(80) | YES | | NULL | | | magnitude | decimal(6,2) | YES | | NULL | | | temperature| decimal(6,2) | YES | | NULL | | | size | decimal(6,2) | YES | | NULL | | | telescope | varchar(40) | YES | | NULL | | | r_a| varchar(20) | YES | | NULL | | | d_e_c | varchar(20) | YES | | NULL | | | eyepiece | varchar(20) | YES | | NULL | | | magnification | int(11) | YES | | NULL | | | filter | varchar(40) | YES | | NULL | | | observer | varchar(40) | YES | | NULL | | | notes | varchar(254) | YES | | | | | imgurl | varchar(254) | YES | | NULL | | | key| int(11) | | PRI | NULL | auto_increment | | key_pls_email | varchar(100) | | | key+observer | | ++--+--+-+--+--- -+ select * from table where key = 1; or select * from table where key = '1'; both of these return an error of: mysql select * from observ_log where key=1; ERROR 1064: You have an error in your SQL syntax near 'key=1' at line 1 mysql select * from observ_log where key='1'; ERROR 1064: You have an error in your SQL syntax near 'key='1'' at line 1 I can not find anything regarding using the primary key in the where expression. key is a reserved word, so write your WHERE clauses like this ... WHERE `key` = 1; ... WHERE `key` = '1'; any help would be greatly appreciated. A Scism [EMAIL PROTECTED] -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with select statement
Hi Antony, Eventually key is a reserved word ?? ;-) nice one !! Best regards Nils Valentin Tokyo/Japan 2003 7 7 18:20Anthony Scism : I am relatively new at this, but is there any way to perform the following: | obs_date | date | YES | | NULL | | | obs_time | time | YES | | NULL | | | object | varchar(40) | YES | | NULL | | | observing_site | varchar(40) | YES | | NULL | | | constellation | varchar(40) | YES | | NULL | | | seeing | varchar(80) | YES | | NULL | | | magnitude | decimal(6,2) | YES | | NULL | | | temperature| decimal(6,2) | YES | | NULL | | | size | decimal(6,2) | YES | | NULL | | | telescope | varchar(40) | YES | | NULL | | | r_a| varchar(20) | YES | | NULL | | | d_e_c | varchar(20) | YES | | NULL | | | eyepiece | varchar(20) | YES | | NULL | | | magnification | int(11) | YES | | NULL | | | filter | varchar(40) | YES | | NULL | | | observer | varchar(40) | YES | | NULL | | | notes | varchar(254) | YES | | | | | imgurl | varchar(254) | YES | | NULL | | | key| int(11) | | PRI | NULL | auto_increment | | key_pls_email | varchar(100) | | | key+observer | ++--+--+-+--+--- -+ select * from table where key = 1; or select * from table where key = '1'; both of these return an error of: mysql select * from observ_log where key=1; ERROR 1064: You have an error in your SQL syntax near 'key=1' at line 1 mysql select * from observ_log where key='1'; ERROR 1064: You have an error in your SQL syntax near 'key='1'' at line 1 I can not find anything regarding using the primary key in the where expression. any help would be greatly appreciated. A Scism [EMAIL PROTECTED] -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with select statement
No, no you are welcome. I made a reserved word list and some other stuff, which is not complete yet. I concentrate on topics which I (personally) would like to be covered more detailed than it is done in the original MySQLmanual. http://www.knowd.co.jp/staff/nils Feel free to browse around, but be aware that the one or the other statement made by me on the page might be incomplete yet or contains perhaps even wrong information. If this would be the case than I would certainly appreciate any comment or feedback. Best regards Nils Valentin Tokyo/Japan 2003 7 7 18:49Anthony Scism : Thank you very much, I should have thought of that. -Original Message- From: Nils Valentin [mailto:[EMAIL PROTECTED] Sent: Monday, July 07, 2003 4:34 AM To: Anthony Scism; [EMAIL PROTECTED] Subject: Re: need help with select statement Hi Antony, Eventually key is a reserved word ?? ;-) nice one !! Best regards Nils Valentin Tokyo/Japan 2003 7 7 18:20Anthony Scism : I am relatively new at this, but is there any way to perform the following: | obs_date | date | YES | | NULL | | | obs_time | time | YES | | NULL | | | object | varchar(40) | YES | | NULL | | | observing_site | varchar(40) | YES | | NULL | | | constellation | varchar(40) | YES | | NULL | | | seeing | varchar(80) | YES | | NULL | | | magnitude | decimal(6,2) | YES | | NULL | | | temperature| decimal(6,2) | YES | | NULL | | | size | decimal(6,2) | YES | | NULL | | | telescope | varchar(40) | YES | | NULL | | | r_a| varchar(20) | YES | | NULL | | | d_e_c | varchar(20) | YES | | NULL | | | eyepiece | varchar(20) | YES | | NULL | | | magnification | int(11) | YES | | NULL | | | filter | varchar(40) | YES | | NULL | | | observer | varchar(40) | YES | | NULL | | | notes | varchar(254) | YES | | | | | imgurl | varchar(254) | YES | | NULL | | | key| int(11) | | PRI | NULL | auto_increment | | key_pls_email | varchar(100) | | | key+observer | ++--+--+-+--+--- -+ select * from table where key = 1; or select * from table where key = '1'; both of these return an error of: mysql select * from observ_log where key=1; ERROR 1064: You have an error in your SQL syntax near 'key=1' at line 1 mysql select * from observ_log where key='1'; ERROR 1064: You have an error in your SQL syntax near 'key='1'' at line 1 I can not find anything regarding using the primary key in the where expression. any help would be greatly appreciated. A Scism [EMAIL PROTECTED] -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a 1005 error (errno: 150), please...
You must create the key (sku), it is not created for you.
Tom Gazzini wrote:
I would appreciate some help with a problem. I'm trying to create two
tables that have referential integrity.
If I try and create the following table it works fine:
CREATE TABLE book
(
sku INT
) TYPE=INNODB;
However, if I creating this table, I get an error:
CREATE TABLE book
(
sku INT,
FOREIGN KEY (sku) REFERENCES stock_item (sku) ON DELETE CASCADE
) TYPE=INNODB;
The error is: ERROR 1005: Can't create table './shop/book.frm' (errno:
150)
The parent table is as follows:
CREATE TABLE stock_item
(
sku CHAR(14) NOT NULL,
PRIMARY KEY (sku),
description TEXT,
publisher_id INT DEFAULT NULL,
pub_date DATE DEFAULT NULL,
type ENUM('OT','BK','CD') NOT NULL,
availability_id TINYINT DEFAULT NULL,
image_id INT DEFAULT NULL,
buy_price FLOAT UNSIGNED,
list_price FLOAT UNSIGNED,
sell_price FLOAT UNSIGNED,
discount TINYINT UNSIGNED,
stock_count MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
display ENUM('no','yes') DEFAULT 'no'
) TYPE=INNODB;
Any suggestions would be welcome.
Thanks
Tom
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help with a 1005 error (errno: 150), please...
Please ignore my last email. I made a nauseatingly dumb error in the book table (sku should be CHAR(14), not INT). The only help I need is to get some sleep. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help
my coulmn (id) is an auto_increment coulmn and lets say that i have 100 entries, so the problem is that when i delete all entries on my table, the column (id) starts from 101 ,shouldn't is starts from 1 again! please i need help with this Use truncate table, it will reset the auto_incement. -- Greg Donald http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help
Is there any way to seed a column set to autoincrement? Say I wanted it to begin at 1. - Original Message - From: Greg Donald [EMAIL PROTECTED] To: wael fareed [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, July 06, 2003 4:42 PM Subject: Re: need help my coulmn (id) is an auto_increment coulmn and lets say that i have 100 entries, so the problem is that when i delete all entries on my table, the column (id) starts from 101 ,shouldn't is starts from 1 again! please i need help with this Use truncate table, it will reset the auto_incement. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help
Is there any way to seed a column set to autoincrement? Say I wanted it to begin at 1. alter table table_name auto_increment = 1; -- Greg Donald http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with subselect workaround
I got it to work, if anyone's interested (see message below). Here is the SQL: DROP TABLE IF EXISTS ProgramRoyalties; CREATE TEMPORARY TABLE ProgramRoyalties SELECT UtilityID, max(Royalty) as MaxRoyalty FROM royalties GROUP BY UtilityID; SELECT u.UtilityID, u.UtilityIcon, u.UtilityName, u.Version, u.ShortDescription, u.MinorReleaseDate, a.LastName, r.UtilityID, r.AuthorID, r.Royalty, pr.UtilityID, pr.MaxRoyalty FROM utilities u, authors a, royalties r, ProgramRoyalties pr WHERE u.UtilityID = r.UtilityID AND r.UtilityID = pr.UtilityID AND a.AuthorID = r.AuthorID AND r.Royalty = pr.MaxRoyalty; What I didn't understand was that the temporary table is simply a look-up table for the max royalty for each utility. I use this in the WHERE clause of the SELECT statement. Sheryl Canter Permutations Software www.permutations.com - Original Message - From: Sheryl Canter [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, June 22, 2003 2:46 PM Subject: need help with subselect workaround This is a reprise of a question I asked some months ago. Bruce Feist and Tore Bostrup gave me some untested code to try. I've only now been able to try it, and it's not quite working right. Here's a recap of the problem I'm trying to solve: My Web host is running MySQL 3.23, which doesn't support subselects. I have a Web site that displays a list of programs for sale (shareware). The list displays the authors' names. In some cases, more than one author works on a program. In this case, I want to display the name of the lead author. I define this programmatically as the author who earns the highest royalty rate. I have tables like this: royalties table (primary key is a combination of AuthorID and UtilityID): AuthorIDUtilityIDRoyalty -- Author1 Utility1 0.15 Author2 Utility1 0.10 Author3 Utility2 0.25 Author4 Utility3 0.05 Author5 Utility3 0.20 authors table: AuthorIDFirstNameLastName Author1 Joe Smith Author2 BrianJones Author3 Jeff Tucker Author4 MichaelMoore Author5 MarkMann utilities table: UtilityIDProgramName Utility1 ProgName1 Utility2 ProgName2 Utility3 ProgName3 This is my SQL code: DROP TABLE IF EXISTS ProgramRoyalties; CREATE TEMPORARY TABLE ProgramRoyalties SELECT AuthorID, UtilityID, Royalty, max(Royalty) as MaxRoyalty FROM royalties WHERE Royalty = MaxRoyalty GROUP BY UtilityID; Unfortunately, the above produces a table with nothing in it. If I take out the WHERE clause, I get one line per group (per utility), but the author isn't necessarily the one with the highest royalty rate. It seems like the first author in the list is being selected. The SELECT statement for using the above table (once it's properly populated) is: SELECT u.UtilityID, u.UtilityIcon, u.UtilityName, u.Version, u.ShortDescription, u.MinorReleaseDate, a.LastName, pr.UtilityID, pr.AuthorID FROM utilities u, authors a, ProgramRoyalties pr $WhereClause u.UtilityID = pr.UtilityID AND a.AuthorID = pr.AuthorID; How do I get the correct data into the temporary table?? Using the above sample data, I'd want it to look like this: ProgramRoyalties table (primary key is a combination of AuthorID and UtilityID): AuthorIDUtilityIDRoyalty -- Author1 Utility1 0.15 Author3 Utility2 0.25 Author5 Utility3 0.20 Thanks in advance for your help. Sheryl Canter Permutations Software www.permutations.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help with an update
On Mac OS X (also FreeBSD Unix), a new installation installs the new version in another folder and changes the /usr/local/mysql symbolic link to point to the new installation (in the same folder) but the old installation is intact there aswell. So navigate to /usr/local and see what you have there. When I upgraded to 4.0.12 I just moved the data folder from the old installation to the new installation. -Original Message- From: Becoming Digital [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2003 1:53 AM To: [EMAIL PROTECTED] Subject: Re: Need help with an update The OS upgrade probably just over-wrote the old MySQL install. Hopefully it left the files intact and you can import them as described in the manual. FWIW, MySQL is at v4.0.13 and some major improvements came with v4. You might want to consider reading the upgrade section of the manual, too. ;) Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: System Administrator a.k.a. The Root of the Problem [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, 11 June, 2003 19:15 Subject: Need help with an update Here is the scenario. I was running 3.23.39 that came with BSD/OS 4.3 , All the databases were running active. I upgraded to BSD/OS 5.0 which has Mysql 3.23.49 and suddenly NO databases are seen. Everything is where is is supposed to be, but the mysqld is not seeing the DBs that were running with 3.23.39. What do I need to do to correct this?? -- 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: need help
I have no idea. sorry. Sibananda Sahoo wrote: Thanks for your reply. Could you please tell me is it possible to achieve foreign key constraint in MySQL 3.23.56. Rgds, Sibananda --- Jeff Mathis [EMAIL PROTECTED] wrote: i'm not an expert by any means :) but, you do need indexes on both the primary key and the foreign key. you've got one on foo_id, but you also need on on foo_value. check the lists for more information. there's been plenty of discussion lately ... good luck Sibananda Sahoo wrote: Dear Sir I am using MySQL 3.23.56. I want to achieve foreign key constraints. But not able to achieve. Right now status of table is : MyISAM. So I tried to convert it to InnoDB in the following way but not able to convert. 1. ALTER TABLE mytableName Type=InnoDB; Then I tried to create a table create table foo ( foo_idint unsigned auto_increment, foo_value int, primary key(foo_id) ) type=innodb; For the above table it also shows the status as MyISAM. Could you pls suggest how can I change and to achieve foreign key constraints. Rgds, Sibananda __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Jeff Mathis, Ph.D.505-955-1434 The Prediction Company[EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6 http://www.predict.com Santa Fe, NM 87505 __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: need help
Do a show variables like '%inno%'; This should tell you if you have innodb enabled or not. If not you will need to configure your cnf file. -Original Message- From: Jeff Mathis [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 10:07 AM To: Sibananda Sahoo; [EMAIL PROTECTED] Subject: Re: need help I have no idea. sorry. Sibananda Sahoo wrote: Thanks for your reply. Could you please tell me is it possible to achieve foreign key constraint in MySQL 3.23.56. Rgds, Sibananda --- Jeff Mathis [EMAIL PROTECTED] wrote: i'm not an expert by any means :) but, you do need indexes on both the primary key and the foreign key. you've got one on foo_id, but you also need on on foo_value. check the lists for more information. there's been plenty of discussion lately ... good luck Sibananda Sahoo wrote: Dear Sir I am using MySQL 3.23.56. I want to achieve foreign key constraints. But not able to achieve. Right now status of table is : MyISAM. So I tried to convert it to InnoDB in the following way but not able to convert. 1. ALTER TABLE mytableName Type=InnoDB; Then I tried to create a table create table foo ( foo_idint unsigned auto_increment, foo_value int, primary key(foo_id) ) type=innodb; For the above table it also shows the status as MyISAM. Could you pls suggest how can I change and to achieve foreign key constraints. Rgds, Sibananda __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Jeff Mathis, Ph.D.505-955-1434 The Prediction Company[EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6 http://www.predict.com Santa Fe, NM 87505 __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- 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: Need help with an update
The OS upgrade probably just over-wrote the old MySQL install. Hopefully it left the files intact and you can import them as described in the manual. FWIW, MySQL is at v4.0.13 and some major improvements came with v4. You might want to consider reading the upgrade section of the manual, too. ;) Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: System Administrator a.k.a. The Root of the Problem [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, 11 June, 2003 19:15 Subject: Need help with an update Here is the scenario. I was running 3.23.39 that came with BSD/OS 4.3 , All the databases were running active. I upgraded to BSD/OS 5.0 which has Mysql 3.23.49 and suddenly NO databases are seen. Everything is where is is supposed to be, but the mysqld is not seeing the DBs that were running with 3.23.39. What do I need to do to correct this?? -- 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: Need Help About mysql.sock ERROR
:-) I think the first issue is to make sure that the server is running. Typically the 'sock' file issue will solve itself if the server is running. -Original Message- From: Ernesto Silva [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 10, 2003 12:38 AM To: [EMAIL PROTECTED] Subject: Re: Need Help About mysql.sock ERROR You have several options, here is, I think, the easiest: First you need to locate the sock file. The location depends on the distribution (in my system is in /tmp). Use find / -name mysql.sock. On some distributions the name can be slightly different, for example mysqld.sock. Then create a symbolic link in '/var/lib/mysql/' pointing mysql.sock Ernesto - Original Message - From: Pratchaya Chatuphian [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 09, 2003 11:50 PM Subject: Need Help About mysql.sock ERROR ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) I use MDK 8.2 and MySQL 4.0.13 i found this error ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) Can anyone help me to solve this problem ? Thank u for advance -- 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: need help
i'm not an expert by any means :) but, you do need indexes on both the primary key and the foreign key. you've got one on foo_id, but you also need on on foo_value. check the lists for more information. there's been plenty of discussion lately ... good luck Sibananda Sahoo wrote: Dear Sir I am using MySQL 3.23.56. I want to achieve foreign key constraints. But not able to achieve. Right now status of table is : MyISAM. So I tried to convert it to InnoDB in the following way but not able to convert. 1. ALTER TABLE mytableName Type=InnoDB; Then I tried to create a table create table foo ( foo_idint unsigned auto_increment, foo_value int, primary key(foo_id) ) type=innodb; For the above table it also shows the status as MyISAM. Could you pls suggest how can I change and to achieve foreign key constraints. Rgds, Sibananda __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Help About mysql.sock ERROR
You have several options, here is, I think, the easiest: First you need to locate the sock file. The location depends on the distribution (in my system is in /tmp). Use find / -name mysql.sock. On some distributions the name can be slightly different, for example mysqld.sock. Then create a symbolic link in '/var/lib/mysql/' pointing mysql.sock Ernesto - Original Message - From: Pratchaya Chatuphian [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 09, 2003 11:50 PM Subject: Need Help About mysql.sock ERROR ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) I use MDK 8.2 and MySQL 4.0.13 i found this error ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) Can anyone help me to solve this problem ? Thank u for advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help to control relay-bin log size
On Fri, Mar 28, 2003 at 06:10:55PM +0100, Vidar wrote: Hi I know that you may set max binlog size with the max_binlog_size variable. However, is it possible to control the size of the relay-bin logs on the slave servers as well? I don't believe it's documented (yet?) but I seem to remember running across it one. I'll see if I can't dig it up again. Will the relay-bin logs be deleted automaticly by mysqld or are there a manuall procedure for doing this ? Slaves will remove relay logs when they're not longer needed. On a healthy, nearly up-to-date slave, you probably won't have more than 2 relay logs--one begin read from and another written to. But even that shouldn't last for very long if the slave is nearly up-to-date. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 53 days, processed 1,824,340,757 queries (396/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Need Help Setting Relationships
On Friday 07 March 2003 00:43, Chris Montgomery wrote: I have been using MS Access for 5+ years and am new to MySQL. I understand the theory behind setting relationships between tables, but am trying to get up to speed in how to do it in MySQL. My environment: Win2k and MySQL 3.23.54 Here's what I want to do: I have two tables, categories and businesses, and I want to create a relationship between them based on the Node field (primary key in the categories table, foreign key in the businesses table). I guess what I fully don't understand yet is the order to do this: create indexes on the key/foreign key fields and then create foreign key constraints or vice versa? [skip] I've tried using a couple of GUI front-ends to manage this but keep getting errors when setting up the foreign key relationships. Can someone please point me in the right direction? Many thanks in advance. Both columns Node are indexed, so just add foreign key constraint. You can find examples in the InnoDB manual: http://www.innodb.com/ibman.html#InnoDB_foreign_keys -- 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 - 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: Need Help Setting Relationships
Howdy Egor, Friday, March 7, 2003, 7:36:00 AM, Egor Egorov wrote: Both columns Node are indexed, so just add foreign key constraint. You can find examples in the InnoDB manual: http://www.innodb.com/ibman.html#InnoDB_foreign_keys I've looked at the examples, but one thing it doesn't mention is whether both indexes need to be unique. In the primary table they are, but does the index in the table with the foreign key need to be a unique index also? Seems that no matter what I try I can't set the foreign keys. I've tried doing this at the cmd line and using GUI's (DBManager, SQLYog, etc.). TIA. -- Chris Montgomery - 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: Need Help Setting Relationships
Disregard my previous msg. It looks like I have it figured out. Friday, March 7, 2003, 5:31:38 PM, Chris Montgomery wrote: I've looked at the examples, but one thing it doesn't mention is whether both indexes need to be unique. In the primary table they are, but does the index in the table with the foreign key need to be a unique index also? Seems that no matter what I try I can't set the foreign keys. I've tried doing this at the cmd line and using GUI's (DBManager, SQLYog, etc.). Cheers. -- Chris Montgomery - 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: Need help about SQL sintax
This should do what you want: UPDATE Table2, Table1 SET Table2.DataDestination=Table1.DataSource WHERE Table1.col1=Table2.col1 AND Table1.col2=Table2.col2 AND Table1.col3=Table2.col3; --- PandaCoop-Krasimir_Slaveykov [EMAIL PROTECTED] wrote: Hello mysql, I have 2 tables : Table1: col1 col2 col3 DataSource Table2: col1 col2 col3 DataDestination How to update Table2.DataDestination with value of Table1.DataSourse where table1.col1=table2.col2 and table1.col2=table2.col2 and table1.col3=table2.col3 -- Best regards, PandaCoop-Krasimir_Slaveykov mailto:[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 __ Post your free ad now! http://personals.yahoo.ca - 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: Need help in Mysql
On Thursday 20 February 2003 09:28, saravanan saravanan wrote: I am using mysql for my project.I am finding problems of using FOREIGN KEY and STORED procedures in version 4.0.please help me and send the details as earlier as possible Stored Procedures are not supported in MySQL yet. Foreign key constraints are supported on InnoDB tables: http://www.mysql.com/doc/en/SEC455.html -- 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: Need Help With MySQL Query
Veysel Harun Sahin wrote:
select vanNumber, sum(grossPay) from usertableDaily group by vanNumber;
The above is the correct query, to save yourself some time. As for your
problem:
But when I execute I get this:
Resource id#3
Resource id#4
This means you're using a resource response from a query, not the data
in the query. Don't forget to do a mysql_fetch_array or mysql_fetch_row
on the resource before using it.
As a test:
$res = mysql_query(...);
print $res;
while ($row = mysql_fetch_array($res)) {
print $row;
print $row['id'];
}
--
Michael T. Babcock
C.T.O., FibreSpeed Ltd.
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: Need Help With MySQL Query
select vanNumber, sum(grossPay) from usertableDaily group by vanNumber; [EMAIL PROTECTED] wrote: hello, I've performed searches on this site and php.net to try and figure out why this is occuring. I can't find any instance in my searches that helped me. So, I'm posting my very first question to this list Here's my table ($usertableDaily): vanNumber|grossPay - | 1000 | 500 |100 |100 Here's my query: SELECT SUM(grossPay) FROM $usertableDaily WHERE vanNumber = $vanList[$count] The query appears inside a loop hence the $vanList[$count] variable. I have a txt file that contains the van numbers. I read that file and then run through the loop and for each van number I execute the above statement. Yes, I've trimmed off any extra blank spaces before and after the 'read' van numbers... I want to SUM the grossPay for each van number. Here's what it should be: $1500 $200 But when I execute I get this: Resource id#3 Resource id#4 Anyone got any idea why this is happening? Thanks in advance, 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 -- Veysel Harun Sahin [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: Need Help With MySQL Query
Hi Interesting problem, normally to get a total by type, you would have a query like, select van, sum(pay) from ($usertableDaily) group by van; However you are individually quering each total, your approach is correct, but slower. The problem you have though is the return of Resource id#3 Resource id#4 So, to begin - What version of MySQL - What OS - What table type - Have you checked the table for corruption I would guess that you have some sort of table corruption. Please try repair table ($usertableDaily) and if that does fix it, email back with the rest of the data. All the best Simon On Saturday 15 February 2003 10:09 am, Guru Geek wrote: hello, I've performed searches on this site and php.net to try and figure out why this is occuring. I can't find any instance in my searches that helped me. So, I'm posting my very first question to this list Here's my table ($usertableDaily): vanNumber|grossPay - | 1000 | 500 |100 |100 Here's my query: SELECT SUM(grossPay) FROM $usertableDaily WHERE vanNumber = $vanList[$count] The query appears inside a loop hence the $vanList[$count] variable. I have a txt file that contains the van numbers. I read that file and then run through the loop and for each van number I execute the above statement. Yes, I've trimmed off any extra blank spaces before and after the 'read' van numbers... I want to SUM the grossPay for each van number. Here's what it should be: $1500 $200 But when I execute I get this: Resource id#3 Resource id#4 Anyone got any idea why this is happening? Thanks in advance, 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 -- Simon Windsor Email: [EMAIL PROTECTED] Tel: 01454 617689 Mob: 07720 447385 - 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: Need Help With MySQL Query
Are you referencing the result set correctly in php ? How are you dealing with what MySQL returns ? Looks like the info is there, you just not getting it out of the result set. - Jerry @ MetalCat.Net - - Original Message - From: Guru Geek [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, February 15, 2003 10:09 AM Subject: Need Help With MySQL Query hello, I've performed searches on this site and php.net to try and figure out why this is occuring. I can't find any instance in my searches that helped me. So, I'm posting my very first question to this list Here's my table ($usertableDaily): vanNumber|grossPay - | 1000 | 500 |100 |100 Here's my query: SELECT SUM(grossPay) FROM $usertableDaily WHERE vanNumber = $vanList[$count] The query appears inside a loop hence the $vanList[$count] variable. I have a txt file that contains the van numbers. I read that file and then run through the loop and for each van number I execute the above statement. Yes, I've trimmed off any extra blank spaces before and after the 'read' van numbers... I want to SUM the grossPay for each van number. Here's what it should be: $1500 $200 But when I execute I get this: Resource id#3 Resource id#4 Anyone got any idea why this is happening? Thanks in advance, 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 - 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: Need help with JOIN
Lars, if I understand you correctly: create table table1 ( hotel_number int ); create table table2 ( hotel_number int, free_day datetime ); insert into table1 values(1); insert into table1 values(2); insert into table1 values(3); insert into table2 values(1,Jan 1 2003); insert into table2 values(1,Jan 2 2003); insert into table2 values(3,Jan 1 2003); Then: select distinct t1.hotel_number, count(t2.free_date) from table1 t1, table2 t2 where t2.hotel_number = t1.hotel_number group by t1.hotel_number; will give: hotel_number free_date -- 1Jan 1 2003 12:00AM 1Jan 2 2003 12:00AM 3Jan 1 2003 12:00AM Is this what you are looking for? Keith Extranet [EMAIL PROTECTED] - 08/02/2003 17:59 Please respond to [EMAIL PROTECTED] To:mysql cc: Subject:Need help with JOIN Heyho folks, I am sitting now for a few weeks on a tricky problem. I don't get it - maybe anyone of you could give me a hint. Let's say I have two tables : Table One has a lot of information about travels, hotels, flights etc. Table Two has an entry for each hotel and each day where I store information about price and most important if this hotel is free on this specific day. Now I want to gather a list of all hotels I could book in a certain time-range. For that I need to issue some quite complex query in table One which is not at all a problem. But now I need to know if all days in my timeframe are bookable. The easiest way to do this is to query table Two for the timeframe I am looking for and issuing a count() - and this I compare with the days needed and - voila I know if this hotel has a free room for each day. This works - but I do have a serious performance problem here. I need to issue this second SELECT on my Table Two for each hotel I found in table one - and as this are easily more than 2000 I get 2000 additional queries. Now I thought about doing another join on my first select - but the problem here is, that I get a huge amount of rows then. Quite clear :) - I have a record for each day in table Two - so I get hotels * days rows - and this easily exceeds half a million rows and more. The best solution I am searching for is now a possibility that I get the count() of rows in tabel Two somehow in my select on table One. OK maybe it's a little bit confusing :) I try to resume the problem : I do a select on table One. And I need to know the count() of rows in table Two with my ID which I get from the first select. I cant do a join as I then get NrofFoundRows in Table One * NrofFoundRows in Table Two entries. Any ideas ? dodger - 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 This message and any attachments (the message) is intended solely for the addressees and is confidential. If you receive this message in error, please delete it and immediately notify the sender. Any use not in accord with its purpose, any dissemination or disclosure, either whole or partial, is prohibited except formal approval. The internet can not guarantee the integrity of this message. BNP PARIBAS (and its subsidiaries) shall (will) not therefore be liable for the message if modified. - Ce message et toutes les pieces jointes (ci-apres le message) sont etablis a l'intention exclusive de ses destinataires et sont confidentiels. Si vous recevez ce message par erreur, merci de le detruire et d'en avertir immediatement l'expediteur. Toute utilisation de ce message non conforme a sa destination, toute diffusion ou toute publication, totale ou partielle, est interdite, sauf autorisation expresse. L'internet ne permettant pas d'assurer l'integrite de ce message, BNP PARIBAS (et ses filiales) decline(nt) toute responsabilite au titre de ce message, dans l'hypothese ou il aurait ete modifie. - 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: need help with GROUP BY
At 9:24 -0600 2/5/03, Jaime Teng wrote: I have a MySQL table: ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | account| varchar(10) | | MUL | || | detail | text | YES | | NULL|| ++--+--+-+-++ I would like to perform a search, SELECT id,account FROM tablename WHERE detail LIKE '%pattern%'; However, this would produce several hits for a single account. I'd like it to produce only one hit *per* account and give me id where '%pattern%' was found and account where it was found on. It should only return *one* result per account. Hmm... Either I am not understanding what you are saying, or what you are saying makes no sense: If there are two id values for a given account that have detail values matching the pattern, which id do you want it to display? regards, Jaime 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: need help with GROUP BY
DISTINCT? -Original Message- From: Tab Alleman [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 05, 2003 12:31 PM To: Jaime Teng; [EMAIL PROTECTED] Subject: RE: need help with GROUP BY SELECT id,account FROM tablename WHERE detail LIKE '%pattern%' GROUP BY Account; -Original Message- From: Jaime Teng [mailto:[EMAIL PROTECTED]] I have a MySQL table: ... It should only return *one* result per account. - 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: need help with GROUP BY
SELECT id,account FROM tablename WHERE detail LIKE '%pattern%' GROUP BY Account; -Original Message- From: Jaime Teng [mailto:[EMAIL PROTECTED]] I have a MySQL table: ... It should only return *one* result per account. - 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: need help with GROUP BY
Jaime, You should be using the DISTINCT function. SELECT DISTINCT account, id, FROM etc. This should give you one instant of 'account' in your SELECT output. At 09:24 AM 2/5/03 +, Jaime Teng wrote: I have a MySQL table: ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | account| varchar(10) | | MUL | || | detail | text | YES | | NULL|| ++--+--+-+-++ I would like to perform a search, SELECT id,account FROM tablename WHERE detail LIKE '%pattern%'; However, this would produce several hits for a single account. I'd like it to produce only one hit *per* account and give me id where '%pattern%' was found and account where it was found on. It should only return *one* result per account. regards, 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: need help with GROUP BY
Jaime, You should be using the DISTINCT function. SELECT DISTINCT account, id, FROM etc. This should give you one instant of 'account' in your SELECT output. At 09:24 AM 2/5/03 +, Jaime Teng wrote: I have a MySQL table: ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | account| varchar(10) | | MUL | || | detail | text | YES | | NULL|| ++--+--+-+-++ I would like to perform a search, SELECT id,account FROM tablename WHERE detail LIKE '%pattern%'; However, this would produce several hits for a single account. I'd like it to produce only one hit *per* account and give me id where '%pattern%' was found and account where it was found on. It should only return *one* result per account. regards, 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: Need Help!!!
* [EMAIL PROTECTED] I've been a member of this mailing list for quite a long time and very thankful that most of my difficulty in creating queries are being answered by just reading responses to questions. Right now I have a problem in creating a query (in fact I'm not sure if this is possible) that will get data from two tables and display its row from one table followed by the same row number of the other table. ...same row number...? There is no such thing as a row number in mysql, unless you have an integer column you can use for this, like an id field. (The mysql _rowid is just an alias for any existing integer primary/unique key.) It is however possible to do it without such a column, using multiple statements, a temporary table and a user variable. See below. Table1 col_1 col_2col_3col_4 AAAA AAAA AAAA AAAA AAAA AAAA AAAA . . . Table2 col_1 col_2col_3col_4 B BBB B BBB B BBB B BBB B BBB B BBB B BBB . . . Query result col_1 col_2col_3col_4 AAAA B BBB AAAA B BBB AAAA B BBB AAAA . . . I will really appreciate any of your help in accomplishing this. OK, here we go: set @a:=0; create temporary table tmp1 select *,@a:=@a+1 id,'1' tabno from Table1; set @a:=0; insert into tmp1 select *,@a:=@a+1 id,'2' tabno from Table2; select * from tmp1 order by id,tabno; Using a user variable (@a) as a counter, we get the wanted row number. This is saved in the temporary table, and later used for the sorting. The 'tabno' column is used to sort all items from Table1 before items from Table2. The values '1' and '2' could be any values giving the correct sort order, like 'a'/'b' or 'tab1'/'tab2'. Read about user variables: URL: http://www.mysql.com/doc/en/Variables.html URL: http://www.mysql.com/doc/en/example-user-variables.html Beware that this is not replication safe, as user variables are not (yet) correctly replicated in mysql: URL: http://www.mysql.com/doc/en/Replication_Features.html If you use a newer version of mysql (4.0 or later), you may be able to use UNION instead of the temporary table: URL: http://www.mysql.com/doc/en/UNION.html 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: Need Help
On Wednesday 22 January 2003 12:10, Haydar KOCAK wrote: ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) [1]+ Exit 1 ./bin/mysqld_safe --user=mysql Check with ps ax | grep mysqld that MySQL server is running. -- 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: Need help installing mysql - Thanks a lot
What does mysqld.log say? Manuel Velasco wrote: I'm getting the following messages after running safe_mysqld (as root): leo:/usr/bin # ./safe_mysqld [1] 4798 leo:/usr/bin # Starting mysqld daemon with databases from /var/lib/mysql 030121 19:38:15 mysqld ended [1]+ Done./safe_mysqld leo:/usr/bin # cd /var/lib/mysql leo:/var/lib/mysql # ls .leo-bin.003 leo-bin.007mysql.sock .. leo-bin.004 leo-bin.008mysqld.log leo-bin.001 leo-bin.005 leo-bin.index mysqld.log-20030114.gz leo-bin.002 leo-bin.006 mysql test leo:/var/lib/mysql # mysql ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) __ 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: Need help with UNION
What version of MySQL are you using? UNION is implemented in MySQL 4.0.0. Check out http://www.mysql.com/doc/en/UNION.html for more information. Victor Pendleton -Original Message- From: Garry Rothert [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 16, 2003 8:11 AM To: [EMAIL PROTECTED] Subject: Need help with UNION Hi I am attempting a simple union. select cnumber from spouse where fd_status = A union select cnumber from members where fd_status = A When I run this SQL statement I get the following error. ERROR 1064 : You have are an error in your SQL syntax near 'union select cnumber from members where fd_status = A' at line 2. CNUMBER is smallint in both tables. I know this seems like a silly union to try but I've simplified a more complex statement to troubleshoot, I can't get any union to work. Thanks This email and any files transmitted with it are privileged, confidential, subject to copyright and intended solely for the use of the individual or entity to whom they are addressed. Views expressed are those of the user and not necessarily those of DPH Engineering Inc.. Any unauthorized use, copying, review or disclosure is prohibited. Please notify the sender immediately if you have received this communication in error. Thank you for your assistance and co- operation. - 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: Need help with UNION
Garry, You are using mysql version 4? Unions are only supported in version 4. If so, the only difference I can see from your example to the manual is that each select is in brackets in the manual. Try the query: (select cnumber from spouse where fd_status = A) union (select cnumber from members where fd_status = A); Andy -Original Message- From: Garry Rothert [mailto:[EMAIL PROTECTED]] Sent: 16 January 2003 14:11 To: [EMAIL PROTECTED] Subject: Need help with UNION Hi I am attempting a simple union. select cnumber from spouse where fd_status = A union select cnumber from members where fd_status = A When I run this SQL statement I get the following error. ERROR 1064 : You have are an error in your SQL syntax near 'union select cnumber from members where fd_status = A' at line 2. CNUMBER is smallint in both tables. I know this seems like a silly union to try but I've simplified a more complex statement to troubleshoot, I can't get any union to work. Thanks This email and any files transmitted with it are privileged, confidential, subject to copyright and intended solely for the use of the individual or entity to whom they are addressed. Views expressed are those of the user and not necessarily those of DPH Engineering Inc.. Any unauthorized use, copying, review or disclosure is prohibited. Please notify the sender immediately if you have received this communication in error. Thank you for your assistance and co- operation. - 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: need help on ODBC
Hello Dolly, Kindly state and share with us the error encountered if there were any. Your source code would also be of help :-) Fred. dolly wrote: Hi, I installed MySQL 4.0 on Red Hat 8.0 server and able to connect from mysqlgui application. I couldn't connect from ODBC ver 2.50 nor 3.51. Any sugestion would be appriciated. - 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: Need help connecting to local database with mysql
Hi. I noticed a couple of weird things with your script:
- The error DBI-connect(myd) failed: Couldn't connect to... is
happening I think because 'DBI:mysqlPP:myd' is in single quotes when you
call DBI-connect, and the syntax seems off. Maybe you'll have better
success if you follow the syntax shown on this page:
http://search.cpan.org/author/OYAMA/DBD-mysqlPP-0.03/mysqlPP.pm#EXAMPLE
- You have this line:
my $sth = $dbh-prepare($sql)
Before you've defined the $sql variable. The usual procedure is to
define your sql first, then prepare it if necessary (which I don't think
you need to in this case), execute the query, and then loop through the
results.
- You have
my @row;
$event=($row[0]);
Before you do an database stuff, so all those variables are just being
set to empty strings.
Lastly, do you really need to use mysqlPP? My understanding of that
module is that it's more situations when you can't get regular DBI to
work. If DBI is an option, you might be better off with that:
http://search.cpan.org/author/TIMB/DBI-1.32/DBI.pm
-bill
Melissa Stranzl wrote:
Hi all:
I am sending a perl program I am trying to run which
doesn't connect to the database. The error message is
at the bottom of the e-mail. I think the program is
going to the sock file and not TCP/IP and I don't know
how to change this, but I'm not sure whether this is
the error, so any help is much appreciated.
Happy holidays.
MVS.
#!/usr/bin/perl -w
package DBD::mysqlPP;
use CGI':standard','-noDebug';
#feo4.pl
my @row;
$event=($row[0]);
$location=($row[1]);
$time=param($row[2]);
$price=param($row[3]);
$description=param($row[4]);
$contact=param($row[5]);
$day=param($row[6]);
use DBI;
my $dbh= DBI-connect ('DBI:mysqlPP:myd', 'umvs',
'umvs') ||die Could not connect to database:
.DBI-errstr;
print hello;
my $sth = $dbh-prepare($sql)
or die Can't prepare $sql: $dbh-errstr\n;
my $sql=INSERT INTO event VALUES ('$location',
'$time', '$description', '$price', '$day', '$event',
'$contact');
$sth-execute();
if (my $sth-SQL($sql)){
print SQL failed;
exit();
}
#my @row;
while(@row = my $sth-fetchrow_array()) {
print
qw($row[0]\t$row[1]\t$row[2]\n\t$row[3]\n\t$row[4]\n\t$row[5]\n\t$row[6]\n);
}
$sth-close();
$sth-finish();
$dbh-disconnect();
This is the error message I get when I compile the
program:
DBI-connect(myd) failed: Couldn't connect to
/tmp/mysql.sock: at C:/Perl/site/
lib/DBD/mysqlPP.pm line 109
at feo4.pl line 19
Could not connect to database: Couldn't connect to
/tmp/mysql.sock: at C:/Perl/
site/lib/DBD/mysqlPP.pm line 109
(in cleanup) Can't call method close on an
undefined value at C:/Perl/
site/lib/DBD/mysqlPP.pm line 274.
-
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: Need Help phpMyAdmin !!
I Installed succefully MySQL, but i want to have a graphical interface to create a new databases and manage my existing database. i readed in a asp-php.net (french web site) http://www.asp-php.net/tutorial/asp-php/iis_et_mysql.php?page=1 the steps for installing phpmyadmin, you cas see the web site and if you have an idea for me thanks a lot -- |-Rachid Abdelkhalak |-Account Manager |-MTDS S.A. |-tel +212.3.767.4861 |-fax +212.3.767.4863 |-14, rue 16 novembre |-Rabat, Kingdom of Morocc On Mon, 16 Dec 2002, Adolfo Bello wrote: I guess your question is more about configuring IIS (or PWS) than about MySQL. Anyway, you have to create a web site in IIS pointing to your phpMyAdmin directory and define in it the default document (index.html. default.php or whatever) Adolfo -Original Message- From: Rachid Abdelkhalak [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 1:28 PM To: [EMAIL PROTECTED] Subject: Need Help phpMyAdmin !! Hello every one At the first, I'm Sorry for my bad english. I want to install phpMyAdmin for managing My MySql databases, I Downloaded ths zip file frome here: http://sourceforge.net/project/showfiles.php? group_id=23067 the file name is phpMyAdmin-2.3.3pl1-php3.zip I unzip this file on my Mysql Default folder: C:\inetpub\Mysql but I dont know how to make after for display the MySQL Admin HTML page: http://localhost/phpmyadmin/index.php Some one have a proposition for me. I am just a newbbie in MySQL and PHP development. Thanks. -- |-Rachid Abdelkhalak |-Account Manager |-MTDS S.A. |-tel +212.3.767.4861 |-fax +212.3.767.4863 |-14, rue 16 novembre |-Rabat, Kingdom of MoroccO - 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: Need Help phpMyAdmin !!
I guess your question is more about configuring IIS (or PWS) than about MySQL. Anyway, you have to create a web site in IIS pointing to your phpMyAdmin directory and define in it the default document (index.html. default.php or whatever) Adolfo -Original Message- From: Rachid Abdelkhalak [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 1:28 PM To: [EMAIL PROTECTED] Subject: Need Help phpMyAdmin !! Hello every one At the first, I'm Sorry for my bad english. I want to install phpMyAdmin for managing My MySql databases, I Downloaded ths zip file frome here: http://sourceforge.net/project/showfiles.php? group_id=23067 the file name is phpMyAdmin-2.3.3pl1-php3.zip I unzip this file on my Mysql Default folder: C:\inetpub\Mysql but I dont know how to make after for display the MySQL Admin HTML page: http://localhost/phpmyadmin/index.php Some one have a proposition for me. I am just a newbbie in MySQL and PHP development. Thanks. -- |-Rachid Abdelkhalak |-Account Manager |-MTDS S.A. |-tel +212.3.767.4861 |-fax +212.3.767.4863 |-14, rue 16 novembre |-Rabat, Kingdom of MoroccO - 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: Need help installing PHP (Linux) with MySQL and Apache
I have been successful (after days of trying) with Apache 2.0.43 , PHP 4.2.3 on Redhat 7.3 I compiled php with ./configure --with-mysql --with-apxs2=/apth/to/apxs (apxs is under /path/to/apache2/bin) After 'make' and 'make install' you will find libphp4.so in /path/to/apache2/modules Then I added the following lines in httpd.conf LoadModule php4_module modules/libphp4.so AddType application/x-httpd-php php FilesMatch \.php(\..+)?$ SetOutputFilter PHP /FilesMatch Have fun Andrea Forghieri - 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
