Re: Select: JOIN magic?
try aliasing your right table twice and join it both ways. something like: select award.*, a.name, b.name from ( award left join user a on award.uid = a.uid) left join user b on award.uid = b.uid if you have a windows box to use as a front end, download a copy of corereader to help you develop queries. it uses point and click, so you can quickly work them out. http://corereader.com i'm not sure about the parentheses in the above, but that's the way that corereader did it with the tables that i was experimenting on. it does seem logical, i suppose, but i'm pretty sure of the idea involved: alias the table twice so you can use it both ways. Hello. I'm trying to select and display from a couple tables I have. I have an awards table, which has info on an award a user has earned. I've also a user table which lists all the users. What I'm trying to do is display information about an award, but can't find the right SELECT syntax to make it work the way I want. The award table has two associations to the user table: the nominee and the proposer: What I want: Award | Nominee | Proposer | Awarded on | 1 | Joe Shmoe | Fred Smith | 2002-11-29 | What I get: Award | Nominee | Proposer | Awarded on | 1 | Joe Shmoe | 3 | 2002-11-29 | The (simplified) tables look like this: +---+ +-+ | award | | user| +---+ +-+ | uid (int) | | uid (int PRI) | | nominee (int) | | name (varchar) | | proposer (int)| +-+ | awarded (Date)| +---+ I've been doing the following to get the basic info: SELECT * FROM award LEFT JOIN user ON award.nominee=user.uid; This gets me the info for the nominee, including name and everything else. I'd like to spare making a whole other query to the DB to get the name for the Proposer, but I can't think of a way to make it work! Any help would be appreciated. :) Regardez, Michael Carter Pilot/Programmer -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ado , blob reading problem
you won't get a large blob back in one piece. to retrieve blobs, you need to use the getchunk method. refer to getchunk in the vb documentation. it also has an excellent example. i am using vb,ado,myodbc,mysql i have some values in longblob,mblob fields when i read it with ado , if the data is less than 1o kb then the field having the value otherwise it shows the null for ex. select image from stumaster where id=12 if the image is of = 10 kb then it returns value otherswise shows null in fieldvalue please help __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: hello to new guy
there may be some willing to help, but having trouble understanding. i don't know about anybody else, but i've been following this since his first posting, and my simple mind is having trouble identifying his problem. is it technical, organizational, or sociological? mysql From: Norm Lamoureux [EMAIL PROTECTED] ...700 individual web pages that EXCITE does a search for... Are these web pages actual individual files, or are they simply information in a database someplace that needs to get displayed? I don't know whether my server has these programs, I can check. I would doubt that the server is going to have any of your DOS software installed. I'd be willing to bet money that you are going to have to port this data yourself. Since you aren't funded, you may want to just take a brute force aproach. That is, maintain a list of all the information yourself. Say on your home computer. Then, update the web pages by hand as needed. I may be missing what you need, so let me know. Hey, everybody else. Any input? --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ sql -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [MySQL v3.23.53a] BLOB Data and Table Size
no database manager that i know of automatically packs the database after every record deletion. it would be too costly. that is one of the reasons that most of us do not store blobs in databases, but store them externally. I have two tables containing a BLOB cloumn as; -- supposed to be able to handle up to about 16MB CRETAE TABLE gallery1 ( idINTEGER, titleVARCHAR(255), mediumimageMEDIUMBLOB ); -- supposed to be able to handle up to about 4GB CRETAE TABLE gallery2 ( idINTEGER, titleVARCHAR(255), largeimageLONGBLOB ); Both table show the size about 48KB initially. And I added a 16MB(about 7,863KB) image into the table, gallery1 and a 4GB(about 11,620KB) into the table, gallery2, respectively. The size of gallery1, i,e, gallery1.db, shows 8,232KB and the size of gallery2, i.e., gallery2.db, shows 11,712KB. Each table contains only one entry. And I deleted both entries from two tables. So both tables contain no entry. However, the size of tables remain same as after insertion made. Q: How come the size of table does not decrease after deletion? Thank you. And regards, Pae P.S.: MySQL, SQL, and Query(to clarify that it's not spam nor OT). -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possible to export data from access to MySQL?
you're gonna get lots of neat high tech answers to your problem, so here's a low tech answer just because it's fun. construct the database in mysql. copy the access database to a working copy to be safe. in the working copy, link to the mysql tables. open the access table a and copy the data. open the mysql table a and paste the data. : ) the kid in me loves doing that. copy and paste is the most powerful concept in windows. the only reason that i don't use it in corereader is that it would permanently tie corereader into ms. windows. Is it possible to export data from MS Access into MySQL? If so how? I have a huge amount of data that will take weeks to input and I've convinced my boss to use MySQL rather than any other DB because of it's compatibility with PHP. However I (probably hastily) *assumed* that there would be a way to export the data. Any ideas? Steve Jackson Web Developer Viola Systems Ltd. http://www.violasystems.com [EMAIL PROTECTED] Mobile +358 50 343 5159 -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: hello to new guy
so one of your questions is, if i may paraphrase, what database manager should i use? i believe that you said that it has only eight hundred records? and it's only a single table? you can manage it manually with little trouble? and it's a low volume web site? pardon my sacrilege, but if that's correct, i would be tempted to merely keep it in a flat file and access it through my code. after decades in this profession, i firmly believe in simplicity. i'm not being flippant; the corereader configuration tables in my test environment contain thousands of records, and they are all in flat files. i've actually been considering converting coremodel to use flat files. however, if your data is subject to some growth, then perhaps a simple database manager like ms. access, to future-proof it. if you just want the fun of managing a database manager, then of course, mysql would work on your windows box. hope that i underestood the issue. Hi, Ok, I guess I was confusing everyone. I thought this was a simple problem, we need a search engine to search through our data, I might be able to convert to csv, or I found another way... example... -NO- SAR1100 -COMMENTS- 999 try6 -ORG_LEVEL_1- SARNIA COMPUTER USERS' GROUP -ALT_ORG- SCUG -STREET_BUILDING- St. Bartholomew's Church -STREET_ADDRESS- 718 Cathcart Boulevard -STREET_CITY- Sarnia, ON N7V 2N5 -ACCESSIBILITY- Steps to go down to basement -LOCATION- St. Bartholomew's Church basement -FAX- (519) 542-4566 -DESCRIPTION- Since 1982, our group consists of members who join together each month to learn about computers and solve the problems that come with them Both beginners and advanced users watch and participate in demonstrations of hardware and software and work together to help solve individual problems. Our General Meetings are held once a month we also have Special Interest Groups, which focus on specific computer topics such as Windows, Graphics and Genealogy. -FEES- $35.00/YEAR -MEETINGS- Main meeting - last Wednesday of month (except July, August and December). -PRINT_MATERIAL- SCUG pamphlets -ESTABLISHED- 1982 -ELECTIONS- Held in June of each year. -SUBJECTS- COMPUTERS -WWW- http://www.sarnia.com/scug -EMAIL- [EMAIL PROTECTED] -CREATED- 1996-04-13 -UPDATE_DATE- 2001-05-17 -UPDATE_SCHEDULE- 2001-05-17 -MODIFIED- 1996-04-13; 1998-08-04; 1999-05-03 -FIN- all entries can be like that for exporting, I just need a database program that I can use offline to FTP to a search engine. We might have to have someone else host the data too?? Confused as always, Thanks, Norm Rodney Broom wrote: From: Norm Lamoureux [EMAIL PROTECTED] Sounds like I am confusing everyone... I only have one database with over 800 records The data on the website is the result of my database. I wish to search on words in each record. Search with what? Do you mean the page at: http://www.informsarnialambton.org/search.htm --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ sql - 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 -- - Visit me - http://www2.ebtech.net/~nlamoure/index.htm - Information Sarnia Lambton - http://www.informsarnialambton.org - Sarnia Computer Users' Group - http://www.sarnia.com/scug - Lawrence House Centre for the Arts - http://www.lawrencehouse.ca - Good computer website - http://www.techtv.com Looking for Star Wars and View Masters ! Also any U.S. state quarters. -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: join with OR and LIMIT fails to return result
interesting. of course, removing one of the limiting selection criteria would increase the number of possible selected records. and the limit clause does not seem to be correctly constucted. but forgive me if i just don't understand. if you have a windows box for a front end, download corereader. it will let you quickly experiment with point-and-click queries to debug query logic.http://corereader.com Description: join with OR and LIMIT fails to return result How-To-Repeat: This query fails to return a result: SELECT Subscriptions.UID FROM Subscriptions, Accounts WHERE Accounts.UID = Subscriptions.User AND (Accounts.Email = 'bill' OR Accounts.UName = 'bill') ORDER BY Subscriptions.Created DESC limit 1; Empty set (0.02 sec) But, if I remove one of the parenthesized OR tokens, it works: SELECT Subscriptions.UID FROM Subscriptions, Accounts WHERE Accounts.UID = Subscriptions.User AND Accounts.UName = 'bill' ORDER BY Subscriptions.Created DESC limit 1; +-+ ID | +-+ | 255 | +-+ 1 row in set (0.00 sec) Or, if I remove the LIMIT, it works: mysql SELECT Subscriptions.UID FROM Subscriptions, Accounts WHERE Accounts.UID = Subscriptions.User AND (Accounts.Email = 'bill' OR Accounts.UName = 'bill') ORDER BY Subscriptions.Created DESC; +-+ | UID | +-+ | 255 | +-+ 1 row in set (0.11 sec I've already worked around this, I'm just letting you guys know. Fix: -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL subquery that works
The question is what does Access do in order to perform subqueries on MySQL via MyODBC driversince it is obvious that they work? access contains its own sql engine, so when you link the mysql tables into it, you were then running access queries. What can I do, but I do not want to use access database as a layer between my application and mysql server? I use Visual Basic for accessing mysql server. you may need to do what you need in part sql and part vb code instead of entirely sql. i'm kindof surprised that you're getting such good results from access. i've seen access corrupt databases after they exceeded a gig. also, in stress testing coremodel, access immediately trashed its own database at the beginning of every test, so i recommend caution with access in production. to stress coremodel, i had to move its database into a server. Greetings! I am aware that MySQL does not support subqueries. I have a 3 gb large database of bibliographic records. Database is designed by a model that is accepted by almost every library system, but there is one problem. When searching that database, nested queries are needed and there's a difference between mysql and every other database system. Now, when I linked these tables to Access database and used JET SQL everything was working very nice and nested queries worked too because Access Database supports them. Speed is impressive. The question is what does Access do in order to perform subqueries on MySQL via MyODBC driver since it is obvious that they work? What can I do, but I do not want to use access database as a layer between my application and mysql server? I use Visual Basic for accessing mysql server. Thank You very much Bruno -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: help for image into database
since i've not done much with blobs, i hesitate
to respond, but since nobody else has, maybe some
observations might help.
first of all, you're coding to an oledb provider,
but specifying an odbc driver. you can use both
if you feed the provider from the driver.
otherwise, you need to use one or the other. if
the microsoft documentation is not clear, take a
look at corereader's documentation
http://corereader.com/document.htm i sure don't
know that i did a better job than macrosoft, but
maybe it'll help. but at least you can be
certain that i'm not trying to confuse you to
make money from you.
i notice also that you've not positioned the
cursor in your code. insure that you're pointing
to a record before you try an update.
i notice also that you've not declared a cursor
to the odbc driver. the mysql driver gave me
some problems when i first started using it
because i sometimes needed to declare a client
side cursor. you might fiddle with that.
step back and look at what your code is doing.
hope that this helps some.
Hi, mysql
I want to put a image into mysql database,but when I run program listed
below
%
formsize=Request.TotalBytes
formdata=Request.BinaryRead(formsize)
set rs=server.CreateObject(adodb.recordset)
strconn=driver={mysql};dbq=server.MapPath(test.mdb);uid=root;pwd=l05m;
sql=SELECT * FROM imgtable
rs.Open sql,strconn,1,3
rs.AddNew
rs(img).appendchunk formdata
rs.Update
rs.Close
%
the system says:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
ODBC Drivers can't support this attribute
/upload.asp, line 7
I want to know what is the problem.
I have the Mysql 3.23.52-max-nt, ODBC2.50, windows XP and ASP.
Can anyone help?
Regards,
luoya
_
The new MSN 8: advanced junk mail protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
--
John Ragan
[EMAIL PROTECTED]
http://www.CoreReader.com/
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: why so slow?
some of the gurus (which i'm not) on the list will help you find some of the problems. however, before you jump into them, remember that ms. access is not a database server; it is a desktop database manager. as such, it runs without much of a server's overhead even when the server is running locally. (this is not necessarily a derogatory observation because i use access for a lot of minor chores, but it's not a server.) Hi,all: I am running my project based on the MySql ODBC connection. Now the operations including select,update and delete to the tables are so slow compared with the former MS Access ODBC connection. COULD U TELL ME HOW TO FIX THIS? thanks a million!! weiwei -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: The dreaded move from Oracle to MySQL
: ) excellent. i envy you. i'd noticed the same thing about oracle. seems to be a resource hog. the only thing worse that i've seen is ms access. they make mysql look very efficient. hate to say much, since it appears that you know what you're doing, but you might also take a look at rdo. it has an extremely small footprint within the infrastructure compared to ado. if you do, you may want to refer to the oracle notes in the corereader documentation (and oracle feedback outside this list would be appreciated). i'm running an oracle enterprise server in a test environment for corereader, and i've noticed that, although oracle sometimes takes a while to wake up, after you have its attention, it throws data at you very fast. sometimes a developer does not use connections properly. in your case, i would create a single connection and keep it open for the duration of the 45 million record move. Currently I open a connection and keep it open during the table move only. After the whole table is moved it destroys the object and checks to see if there is another table running. I wrote the app to spawn up to 10 clients one pIII550 w/256meg ram can handle 2 clients due to the large overhead. What I am seeing is on the very large tables, we have really three or four tables that make up the bulk of all the data, the connection eventually times out or has an error if the server has any kind of other load on it. records. instead, i would ask oracle for the biggest record set that the infrastructure can handle. it will come back to you very fast. The problem is the production machine is old and weak I had them beef it up to two whole gigs of ram this thing at idle sits at a 2 load rating or better. log into a local disk file. if the process crashes, you pick up from where it went down. I have written some error checking into the app including error logging but I don't want to spend another week writing an app just to move data and test. The load will only go as fast as the largest table in the Oracle database with 10 loaders the other tables get chewed through pretty quick transaction logging going on. There is no indexes on the mysql box and no logging of selects or the like on the oracle side. glad to hear that you had no errors before, but be careful of oracle's data typing. Thats part of what makes the app slow I have very strict data typing and conversions happening on very large text fields. additional boxes. run all of them simultaneously against the servers. they'll bump into each if you run multiple apps, increase the query timeout of all of the connections. that's the way that i would do it. Lol, that is the way I did it. I may finish the app in general it will move data from ms-sql, oracle, and my-sql into ms-sql, mysql, or a flat file. I though about setting up a couple of REAL beefy boxes with a couple of gig of ram a piece and have them store the recordset in a disconnected method, so once oracle is done tossing records its out of the loop completly. Right now using LOAD has been by far the fastest method multiple dataloaders has only yeilded about 2000 records a second and LOAD does almost 12k even on the big big tables it doesn't slow down to below 5 or 6k. Cheers, Wes -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help me optimize my query
Im running the following query: SELECT user.nickname, user.id, user_detail.points FROM user, user_detail WHERE user.details = user_detail.id AND user.id 101 AND user.language = 'en' ORDER BY user_detail.points DESC LIMIT 5; I don't really know how LEFT JOINS, INNER JOINS and so on work, but I want to increase the performance of the query. This is the result of the EXPLAIN for the query: if you would like to learn more about queries and if you have a windows box for a front end, download corereader from http://corereader.com it will let you quickly do and re-do join queries just by clicking on your database objects, so you can concentrate on developing logic without worrying about syntax. when you get the query that you like, copy the sql code into your app. -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: The dreaded move from Oracle to MySQL
since you're comfortable with rolling your own tools, here are some observations about the ado approach, and which could be applied to odbc as well. i'm running an oracle enterprise server in a test environment for corereader, and i've noticed that, although oracle sometimes takes a while to wake up, after you have its attention, it throws data at you very fast. sometimes a developer does not use connections properly. in your case, i would create a single connection and keep it open for the duration of the 45 million record move. after the connection is open, i would not move records. instead, i would ask oracle for the biggest record set that the infrastructure can handle. it will come back to you very fast. then i would do the inserts into mysql. when that data set is done, i would retrieve another. i would log the operation. i always expect a an operation of that size to overwhelm the infrastructure, so i would keep a running check of where the operation is. i would write that log into a local disk file. if the process crashes, you pick up from where it went down. be sure that oracle's nolog is set for every table. in fact, turn off all logging everywhere except for your app's log. also, insure that there are no indexes in the mysql side until you're finished. i'm still becoming familiar with mysql, but insure that there's no transaction logging going on. glad to hear that you had no errors before, but be careful of oracle's data typing. if you have the resources, copy the app to additional boxes. run all of them simultaneously against the servers. they'll bump into each other, but there's enough latency in all the systems to let them all operate. (hopefully, you're running on at least a hundred meg network for this.) for your operation, it would be worth it to borrow five or ten machines from the rest of the organization for a couple of days. if you run multiple apps, increase the query timeout of all of the connections. that's the way that i would do it. Well, This is my first attempt at moving from Oracle to MySQL on such a large scale and so far it isn't going well at all. We are running oracle 7.3.4.0.1 and MySQL 4.0.4-0 standard. We are moving 101 tables with a total of 45 millon records over. I have tried several diffrent methods including using a MS-SQL server to act as go between using DTS services. We have run into two problems, one, we don't get all the data over we loose rows and the like. Two it is super slow on the order of 18 to 30 hours to do a pull. We need a way to move the data in a resonable amount of time around 8 hours is the goal. Things I have tried so far include using MS-SQL as a go between fast but with data loss, using a custom ADO application to move the data and handle any transforms we get the data but it REAL slow, and trying to get the oracledump.pl perl script to run with no success. If anyone has had a chance to do this type of migration please point me in the right direction. I would really hate to stay with Oracle for the only reason that we can't get the data off of it and on to another box :) Thanks, Wes -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT Statement Question
thanks for the chuckle. we've all been beginners and i still do dumb things with sql. download corereader from http://corereader.com it will let you click on the database objects to build your queries. when you click on your table name and your field name, it will return the data to you and, if you care to look at it, it will show you the correct query of select * from table_name instead of your select * from postcode Hi I am a beginner in using mysql on Windows platform, and I have a question regarding the use of SELECT * from TABLE_NAME statement within the mysql prompt. I have a table of consisting of three columns POSTCODE, SUBURB, STATE where the primary key is a composite of all three fields. When I run SELECT * FROM POSTCODE; in mysql prompt I get garbled display of data and some of my data is not shown. However when viewed from within a tool like PHP MYADMIN all the data is displayed correctly. Is this a bug or a limitation of mysql? Or am I doing something wrong? Any advice/help would be most appreciated. Thanks Best regards Boris -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL on windows
my experience is rather limited since i use linux only as a file server. after installations of mysql on win2k and nt4.0 sp6, i can say that it came up without incident, and has been running for months in the corereader test environment. it was far far easier to install than oracle, and easier to install than mssql. when i reboot the windows box, mysql has always popped back up. my philosophy with tools is if it doesn't keep running after i install it, i don't need it. so far, mysql seems to be a keeper. TERRIBLE! at least with ver mysql-3.23.53 on Win98. It crashes my machine after a while and when I close it, it sends the hadr drive into an infinite loop doing who knows what. On 20 Nov 2002, at 11:02, Simon Windsor wrote: Hi I have been asked to support/bug-fix a Windows based web site using MySQL. I have used MySQL on Linux/FreeBSD for 3/4 years, but never on Windows. How does MySQL compare on Linux and Windows for stability and performance ? Simon -- Simon Windsor mail: [EMAIL PROTECTED] Regards Mike -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Left Join Query
' and decplacement.product = '1029105') or (decheader.code = '5' and decplacement.product = '1029106') or (decheader.code = '4' and decplacement.product = '1029104') or (decheader.code = '4' and decplacement.product = '1029105') In the above example, all of the codes exist in the decheader table but only code 7 has a corresponding product in the decplacement table. I would wish to return all the decheader details for the above codes and the decplacement details for code 7 as well. This statement actually only gives me the details for code 0007 and nothing else. I should add that for each record in the decheader table, there may be one, none or many recordsh in the decplacement table but I will only be interested in a specific one. I hope this makes some sort of sense!! -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQl LEFT JOIN Syntax
suggestion: if you have a windows machine that you can use as a front end, download a copy of corereader. http://corereader.com it lets you create joins by clicking on objects in your database. you can quickly experiment with the logic instead of worrying about syntax. let corereader take care of the details as you develop the query that you want. i use it, but it seems to be especially beneficial to newcomers. Hi there, My MySQL LEFT JOIN Syntax as following is wrong? I cannot get the right result. SELECT name, email form T1 LEFT JOIN T2 ON T1.Snum=T2.Bnum T1(Snum,name,email,address) T2(Bnum, notes, time) I wnat ot get the result(name, email, notes). Any suggestion, comments? Thanks Yan -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re[2]: delete where column = null
just what the world needs: more non-standard sql syntax. Ok, so it seems to be a bug. What's the procedure for reporting it (or do the developers read this list?) -David A minimal test case that demonstrates the bug: create table t ( id integer, index id_idx(id) ); insert into t(id) values(null); select * from t; delete from t where id = 1; select * from t; AB I get the same behaviour with mysql 3.23.47 (not max) and with mysql v AB 4.0.4-beta AB Without the index it works fine. Must be a bug AB Cheers, AB Andrew -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Newbie: interfacing to MySQL?
not sure what you're after, but here's one: if you have windows boxes for frontends, take a look at http://corereader.com it's a free query tool that's designed to simplify the query operation for administrators and users. after you connect, it's entirely point and click. it'll query anything, but likes mysql a lot. Hi all, Brand new to these parts so go easy on me! :) Right - I want to use MySQL - but doing things backward. What are you people using as a front end? I mean MySQL just sits in the background churning out stuff - but obviously the instructions have to come from somewhere. Someone suggested using Java (I want to use this using a browser over my intranet) but Java seems pretty tricky. Sorry if this is a how-long-is-a-piece-of-string type questions. If you need more detail just ask. All comments appreciated. Thanks for reading. Regards Brynley -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql SELECT question (LEFT JOIN?)
by now, i hope, you've gotten answers from the sql gurus on the list, so i won't clutter with my humble attempts. your comment about problems with joins indicates that corereader might be of some help to you if you have a windows box for a front end. it will let you do quick point-and-click queries, so you can experiment with fairly complex joins. download it from http://corereader.com it's intended to be a production system, but it's a great teaching tool. it connects to anything, but it especially likes mysql. Assume two tables: CREATE TABLE block_ip ( datestamp int(11) NOT NULL default '0', remote_addr char(15) NOT NULL default '', PRIMARY KEY (remote_addr), KEY datestamp (datestamp) ) TYPE=MyISAM; CREATE TABLE brute_force ( datestamp int(11) NOT NULL default '0', remote_addr char(15) NOT NULL default '', remote_user char(35) NOT NULL default '', KEY remote_addr (remote_addr), KEY datestamp (datestamp), KEY remote_user (remote_user) ) TYPE=MyISAM; Contents of the 'brute_force' table (remote_addr): 1.2.3.4 2.3.4.5 3.4.5.6 4.5.6.7 5.6.7.8 6.7.8.9 Contents of the 'block_ip' table (remote_addr): 2.3.4.5 4.5.6.7 Can someone help me with the query that will select all the 'remote_addr' rows from 'brute_force' that are NOT in the 'block_ip' table? Something like: select brute_force.* from brute_force, block_ip where brute_force.remote_addr != block_ip.remote_addr maybe? I have a feeling it's some sort of left join, and I was never very good at those. :-/ -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Fw: SCHEMA, ARCHITECTURE ???
if you can affect closure without artificial constructs, then it probably belongs in a single database. if closure can't be affected in the logical design, then you probably need multiple databases. if you don't understand the concept of a relational database closure, refer to http://www.coremodel.com mysql - Original Message - From: William Martell [EMAIL PROTECTED] To: MySQL Main List [EMAIL PROTECTED] Sent: Monday, November 11, 2002 2:02 PM Subject: SCHEMA, ARCHITECTURE ??? Hello All, How do I determine the best architecture for my MySQL database? Do I create multiple databases or do I create only one database with multiple tables? (Any general rules of thumb?) How do I know if I need to use Keys, Indexes or other features in order to optimize the database? -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: limited queries to one returned row
see if the limit keyword will do what you want. select * from table limit 0, 2000 Hi all, is it possible to limit a result so that only the first record in a query can be returned for a specific user? I want to do this so that someone who does not have intimate knowledge of a large (10 records) table cannot issue commands to grab *all* of the data. Thanks ahead of time for any info! --Raj -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to link tables in MySQL
macrosoft misleads thousands of people by the way they designed access. in your case, they've confused relations with referential integrity. we cringe when that question hits this list, as it does periodically. relational databases don't have hard coded relations. NEVER. furthermore, relational database managers should NEVER attempt to enforce such a thing, so of course, mysql does not. the relation of one table to another is a concept which is inherent and subsumed in the very concept and design of a relational database. there are other things that you may need to overcome as you move from access to a server. the people who watch this list are great at helping newcomers but you might also appreciate corereader. it's a free tool at http://corereader.com . check the documentation to see if it has anything for you. 1 - any way to search archives of MySQL list? 2 - In MS Access you have to graphically connect the table id fields to tell Access how the tables relate. How do you do this In MySQL... from the command line I'm sure... just a code snippit or reference to one would be nice... Thanks for any help - tmb __ Do you Yahoo!? U2 on LAUNCH - Exclusive greatest hits videos http://launch.yahoo.com/u2 -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: RE: MySql 4.1 Sub Selects and not stored procedures
the EXISTS logic is ugly in as sql statement. does that count? seems like ugly should count. hey, can i hijack my message back?...this thread is about the performance of subselects, not stored procedures. go write your own message :-)... sothe original question is if someone would be nice enough to answer.. Do any MySql coders writing subselects in 4.1 know whether EXISTS will outperform an equivalent query written as a join. = Typically, db vendors recommend you use an exists clause, not a join when testing for the presence of child data because it's faster. e.g. 4.1 version (faster?) - select person.person_id, person.name from person where exists ( select 1 from invoice where invoice.paid is null and invoice.person_id = person.person_id) vs 4.0 version (slower?) -- select DISTINCT person.person_id, person.person_name from person, invoice where person.person_id = invoice.person_id and invoice.paid is null Thanks, Greg. - Original Message - From: Dan Rossi [EMAIL PROTECTED] To: Victoria Reznichenko [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, November 11, 2002 11:04 PM Subject: RE: RE: MySql 4.1 Sub Selects damn , i read it was 4.1 i guess we have to wait a bit then, i wish i could program some c ++ to hurry it along a bit, i dont really have access to DB's like oracle to learn stored procedure stuff -Original Message- From: Victoria Reznichenko [mailto:victoria.reznichenko;ensita.net] Sent: Monday, November 11, 2002 11:00 PM To: [EMAIL PROTECTED] Subject: re: RE: MySql 4.1 Sub Selects daniel, Monday, November 11, 2002, 3:51:25 AM, you wrote: d will 4.1 hopefully have stored procedure functionality ? Nope. Stored procedures will be implemented around v5.0 -- 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 - 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 -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problems with simple(?) query
Hi, I'm trying to write what I am sure should be a simple query, but just can't get it to work! If I create a table called foo like this: create table foo ( id1 int(11) , id2(int 11) ); populate it with data and then try: SELECT * FROM foo WHERE id2=1 AND id2=2 (for example), I just get an empty set returned. An explain on the query gives an 'Impossible WHERE' message. Both id1 and id2 can have duplicate values in them, though the same combination of id1 and id2 cannot appear in the data (ie: id1=1, id2=1 ; id1=1, id2=2 is possible, id1=1, id2=1 ; id1=1, id2=1 is not possible), and it is possible that the values I search for will not be in the table at all. All I really want is to get the value for id1 where there are corresponding records which match all of my search parameters for id2. Anyone got any ideas how to manage this? It's beginning to drive me mad! Thanks, James to give the man a fish, or to teach him to fish. if you have a windows box for a front end, download corereader from http://corereader.com it connects to any data source, and it likes mysql a lot. it does quick point and click queries so you can, hopefully, find for yourself the logic errors such as has been pointed out in your query. have fun. -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: I: How to port MS Access to MySQL ??
although i make a living from their products and corereader runs under windows, i really really hate to give macrosoft a plug. but i keep seeing this kind of question here, so here's what i do, if you have the tools. 1. i fire up enterprise manager for ms sql server, because it has a pretty slick dts mechanism. (man, i hate writing that.) 2. then i suck the access database into ms sql. 3. then i alter whatever needs to be cleaned up inside the server for whatever server it's going to. 4. then, still inside enterprise manager, i send it to mysql, or oracle, or whatever. super simple and easy. argh! gotta wash my mouth out with soap. I use navicat http://www.mysqlstudio.com/. ing. stefano scattini -Messaggio originale- Da: tmb [mailto:topmailbox;yahoo.com] Inviato: venerdì 8 novembre 2002 13.18 A: [EMAIL PROTECTED] Oggetto: How to port MS Access to MySQL ?? Is there a tool for doing a quick port from MS Access to MySQL? Or must you manually create all the tables sql statements in MySQL and then export the MS Access data to a comma delimited file... then import it into MySQL? Thanks for any help - tmb __ Do you Yahoo!? U2 on LAUNCH - Exclusive greatest hits videos http://launch.yahoo.com/u2 - 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 -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Question regarding nested select
if you have a windows box that you can use as a front end, download corereader from http://www.corereader.com it lets you do point and click queries, so you can quickly experiment with simple queries. In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: Hi, Does version 2.23.52 supports the use of nested select? I want to write a query based on a group where I do a count of something for each group. In SQL Server 7 it would look something like this, select a.department ,(select count(*) from empdb where gender='m' and department=a.department) as male ,(select count(*) from empdb where gender='f' and department=a.department) as female from empdb a group by department No need for a nested SELECT: SELECT department, SUM(IF(gender = 'm', 1, 0)) AS male, SUM(IF(gender = 'f', 1, 0)) AS female FROM empdb GROUP BY department -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Newbie: Intersecting entities
aha! sure. you weren't being vague. you just needed to educate me. some of us have used intersecting entities through the years, but didn't know that there was a fancy name for what we were doing. if my memory weren't so bad, i'd use it to impress people in the next interview. in this case, simply do a three way multiple join with the intersecting entity in the middle. something like this: select table1.col2, table 3.col2 from table1 left join table2 on table1.col1 = table2.col1 left join table3 on table2.col2 = table3.col1 or you could use an equal join with the intersecting entity in the middle. if you have a windows box that you can use as a front end, i strongly recommend that you download corereader because its point and click queries can make a six way join look simple. then, it'll show you the sql code. best of all is that it's free. http://corereader.com Sorry for being vague. I think the closest thing I can find to it is, possibly, a three-way join. I'm trying to figure out how to write the query. I've got the following tables: systems sys_nameID Abrams 202 Patriot 544 Stinger 229 ... and ... new_req proj_name ID Test Bed Alpha 344 Tracked Wheels 989 Battle Monitor 823 Shoulder Harness654 Ammo Flask 454 Spotter 773 There may be multiple new requirements for a single system. To accommodate them, I created a new table -- an intersecting entity is what I recall it being called -- to bridge the two. I can't keep the multiple references to new_req in the systems table, nor can I keep them in the new_req table. As best I can figure, I need to have a bridge table ... e.g., ie_sys_req sys_id proj_name_id 202344 202989 202823 544654 544454 544773 If this seems peculiar, have patience with me I'm new at this. If it's right, let me know so I can breath a sigh of relief. And if the structure is right, could someone offer some guidance on how a query that allows me to query new_req using the intersecting entity (or three-way join, whatever it's called) would be written. Thanks, Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 -Original Message- From: John Ragan [mailto:jragan;arkansas.net] Sent: Wednesday, November 06, 2002 6:55 PM To: [EMAIL PROTECTED]; Richard Forgo Subject: Re: Newbie: Intersecting entities if a succinct statement is possible, perhaps you could give us an idea of its nature? somebody may be familiar with the concept under a different name. I'm trying to find some documentation on creating intersecting entities in MySQL, but haven't been able to track any info down on the MySQL site or on the web. At least we called them intersecting entities while I was in Oracle training (which was some time ago). I think I remember how to set them up, but I'd love to have something to refer to quickly before I start. Can anyone point me in the right direction? Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Access replacement...
i've been hesitant to respond because i'm not sure that this is a head on solution for you, but take a look at http://corereader.com it's certainly easier to use than access queries. simple point and click to build complex output. however, it's not designed for the fancy formatted output to which your folks are accustomed. it'll do text, browser, schema xml, and html, but it's all (intentionally) plain vanilla. Brad wrote: A programming environment; Python comes to mind immediately; very easy to learn, especially compared to VB, and its even Windows compatible (MFC even). A database maintenance utility; To what end? A database query tool; Visual query tools exist for MySQL already; someone keeps posting the name of one from time to time ... A report builder Any report program that works from ODBC will work. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Newbie: Intersecting entities
if a succinct statement is possible, perhaps you could give us an idea of its nature? somebody may be familiar with the concept under a different name. I'm trying to find some documentation on creating intersecting entities in MySQL, but haven't been able to track any info down on the MySQL site or on the web. At least we called them intersecting entities while I was in Oracle training (which was some time ago). I think I remember how to set them up, but I'd love to have something to refer to quickly before I start. Can anyone point me in the right direction? Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem importing data from Access
try this: 1. export the data from ms access to a file on the windows box. 2. fire up ftp from the command line and set it to ascii mode. 3. ftp the file from the windows box to the linux box. if the eol is the problem, the ftp protocol will insure that the eol is correctly translated when moved to the new system. When I import data from the text file I exported out of Access it works correctly except that MySql seems to append and/or prepend some sort of mystery character to the fields it imports. So, I can get the data into the database, but I can't get it out (since the SQL statement can't match the mystery character). I'm using the graphical interface to import data since it fails at the command line even though my version of MySql is a later one than the required 3.22.15. The GUI shows the mystery character as a bold pipe |. The command line seems to show a problem with the size of the field. The select * from table statements look like this on the command line: +--+ | Col 1 | +--+ | data | |ta| |dat| +--+ instead of this: +--+ | Col 1 | +--+ | data | | data | | data | +--+ My guess is that there is a problem with the end of line character being exported to the text file from Access. I chose \n as the end of line character to separate records, but maybe it is something else. Any ideas? Thanks, Brad -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL - Windows vs MySQL Linux
you've insured that you're not simply encountering a problem with case sensitivity ? windows is not case sensitive. Hi all, I have a problem - I've built some JSP which runs ok on a windows install of MySQL, but on a Linux install of MySQL only the update part works. Here is what it does - SELECT date, clicks FROM bsafeLinks WHERE date = DATE INSERT INTO bsafeLinks (date,clicks) VALUES (CURDATE(),'0') UPDATE bsafeLinks SET clicks = clicks + 1 WHERE date = CURDATE() The insert is conditioned on the select returning no data. As I said it works on windows but not on Linux. Can anybody point me at anything obvious that I have missed. Thanks in advance. Kevin Passey -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL - Windows vs MySQL Linux
you're quite welcome, sir. i've been doing this for decades, and i still overlook the simple things. i recommend that you develop a database naming convention, since it appears that your organization does not enforce one. mine helps overcome my poor memory, so i appear more capable than i really am. when i don't forget it. :) a well-thought naming convention allows a database to be moved between servers and systems with minimal hassle. Hi John, Thanks for that - I am new to MySQL - but I've found out two things here; 1. Case sensitivty is an issue with Linux/Windows 2. Beware of special keywords (I called a field in my database date - that was also wrong -) Since I've checked the case and prefixed my field names it works fine on both platforms Thanks for your help Regards Kevin -Original Message- From: John Ragan [mailto:jragan;arkansas.net] Sent: 05 November 2002 19:22 To: Mysql (E-mail); Kevin Passey Subject: Re: MySQL - Windows vs MySQL Linux you've insured that you're not simply encountering a problem with case sensitivity ? windows is not case sensitive. Hi all, I have a problem - I've built some JSP which runs ok on a windows install of MySQL, but on a Linux install of MySQL only the update part works. Here is what it does - SELECT date, clicks FROM bsafeLinks WHERE date = DATE INSERT INTO bsafeLinks (date,clicks) VALUES (CURDATE(),'0') UPDATE bsafeLinks SET clicks = clicks + 1 WHERE date = CURDATE() The insert is conditioned on the select returning no data. As I said it works on windows but not on Linux. Can anybody point me at anything obvious that I have missed. Thanks in advance. Kevin Passey -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Difficult query
you might also try this: select substring(someField, 1, 6), count(substring(somefield,1,6 from theTable group by substring(someField, 1,6) i tried it in corereader and it seemed to do what you want. Hello, I have a table with only ips in it. I want to get all networks the ips belong to: Example: 123.123.123.1 123.123.132.2 123.123.123.3 1.1.1.4 1.1.1.5 1.1.1.6 12.12.12.1 12.12.12.2 12.12.12.10 The result should be: 123.123.123.0 1.1.1.0 12.12.12.0 Instead of converting on matching row (for example 123.123.123.2) to 123.123.123.0 it can be left as 123.123.123.2 or even can be truncated to 123.123.123 The table is stored on a 3.23.43 mySQL Server. The table type is varchar(15) but this can be changed. Thanks, Alexander Newald -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Total newbie asking for guidance
if you have a windows box that you can use as a front end, get a copy of corereader. i recommend it to you because, among other things, it's a teaching tool. it allows you to do (genuine) point and click queries. you can do complex queries simply by clicking on selections. furthermore, you'll want to embed sql statements in your code. when you get a query that you like in corereader, you can open its sql statement to cut and paste. it will query any data source, and so far seems to like mysql particularly well. corereader is totally free from http://corereader.com/ . since it's for a non-profit organization, i'll correspond directly to help you get started. data connections can be frustrating. please respond directly to me to avoid cluttering this list. tell me what organization it is to insure that i am not opposed to its objectives. Hello. I am starting a project that requires a simple database for use as a back-end to a web-accessible look-up facility. When I say simple I mean that the data is static and that the queries are done on single keys. The catch is that I have zero experience in any kind of database work. I have been doing software development for 20 years and am proficient in several programming languages but have never had the occasion to do any database programming. I've got some latitude as to what tools are use to achieve this web-accessible database goal, so I'm going with Apache + MySQL in a Linux (Red Hat 7.3) environment. (Cost is a high priority as this is being done for a poverty-stricken non-profit.) I'm very familiar with Linux, somewhat familiar with Apache (v1.3.x) and have no experience whatsoever with MySQL. Would some kind soul(s) please suggest books and/or websites for the MySQL newbie? I've perused the book list at www.mysql.com, but don't have a feel for the experience level that these books are oriented toward. Any pointers that you could provide to this beginner would be very appreciated. Thank you. -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Alternative for NOT EXISTS
ok. At 10:10 -0400 10/11/02, Michael T. Babcock wrote: John Ragan wrote: try corereader. it will run queries against your mysql, oracle, and ms sql server (and anything else laying around). you can switch between your servers with a click. Am I the only one getting tired of these non-SQL related advertising responses? Feel free to put this in your tagline or something, but please actually bother to answer the question or be otherwise useful to the poster instead of _only_ advertising a product in every post. I agree. The product is free, which is a plus, but since these numerous responses don't answer the questions posed, it'd be more appropriate for John to reply onto to the poster rather than to the list. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help with Sum(), newbie
if you have a windows machine for a front end, download corereader from http://corereader.com/ it's free. it will connect to your mysql server (and anything else). it lets you do point and click queries, so you can very quickly experiment to get the results that you want. it also builds a sql statement in case you need it. it installs in novice mode, so change it to the proficient level to do the functions. fair warning: at the proficient level, it can do complex queries, so pay attention to your logic. OK. Maybe I'm expecting too much of myself, but I can't figure out what I am doing wrong here: mysql select * from example; +---++ | first | second | +---++ | 1 | 2 | | 2 | 3 | +---++ 2 rows in set (0.00 sec) mysql select first, second, SUM(first) from example group by first; +---+++ | first | second | SUM(first) | +---+++ | 1 | 2 | 1 | | 2 | 3 | 2 | +---+++ 2 rows in set (0.00 sec) If it is not obvious, I want the sum of first, which, by my calculations, should be 3. What am I doing wrong? Thanks! --kevin -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Alternative for NOT EXISTS
try corereader. it will run queries against your mysql, oracle, and ms sql server (and anything else laying around). you can switch between your servers with a click. peter brawley gives your answer in another message, but you can quickly work out those simple queries with point and click in corereader. it's a free download at http://corereader.com/ Hi, I have two tables, one for user details and another to indicate membership of some team. The later has user_id and team_id. I want to select all users that are NOT in a particular team. After a lot of effort (my sql skills are almost no existent) I have the following that works with Oracle. SELECT u.uname FROM users u WHERE NOT EXISTS (SELECT * FROM team_members WHERE team_id = 7 AND user_id=u.user_id); This doesn't work with MySQL :-( Can anyone help me? Ideally I would like something that would be portable to oracle/sql server. Efficiency is not an issue. Vic -- This message, including attachments, is intended only for the use by the person(s) to whom it is addressed. It may contain information which is privileged and confidential. Copying or use by anybody else is not authorised. If you are not the intended recipient, please contact the sender as soon as possible. The views expressed in this communication may not necessarily be the views held by Serco Integrated Transport. -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Alternative for NOT EXISTS
i appologize for sending unnecessary mail, but after hurrying off that last response, i realized that you are running oracle. please be aware that you have not encountered a shortcoming in mysql. i refer you to the section in corereader's documentation concerning oracle's non- standard characteristics and behaviours. http://corereader.com/document.htm/ although i'm not familiar with all versions of oracle, oracle has trouble with joins. to create a truly portable statement, out of deference to oracle's shortcomings, you need to use an equal join for this select. try this form: SELECT user.name FROM teams where team.team_id = users.team_id and users.team_id 7; although i created the algorithms in corereader, i don't write freehand sql, but i think that you'll find that this statement will run on all major extant servers. again, i strongly recommend that you get corereader. Hi, I have two tables, one for user details and another to indicate membership of some team. The later has user_id and team_id. I want to select all users that are NOT in a particular team. After a lot of effort (my sql skills are almost no existent) I have the following that works with Oracle. SELECT u.uname FROM users u WHERE NOT EXISTS (SELECT * FROM team_members WHERE team_id = 7 AND user_id=u.user_id); This doesn't work with MySQL :-( Can anyone help me? Ideally I would like something that would be portable to oracle/sql server. Efficiency is not an issue. Vic -- This message, including attachments, is intended only for the use by the person(s) to whom it is addressed. It may contain information which is privileged and confidential. Copying or use by anybody else is not authorised. If you are not the intended recipient, please contact the sender as soon as possible. The views expressed in this communication may not necessarily be the views held by Serco Integrated Transport. -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: query help
| varchar(9) | | MUL | 0 | | | product_name| varchar(64) | | MUL | | | | product_description | varchar(255) | | | | | +-+--+--+-+-++ 4 rows in set (0.00 sec) mysql describe specs; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | id | int(12) | | PRI | NULL| auto_increment | | fg_number | varchar(8) | | MUL | || | spec_name | varchar(255) | | | || | spec_value | varchar(255) | | | || ++--+--+-+-++ 4 rows in set (0.00 sec) Man is this sql, query stuff fun ;-) Michael -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql: multiple SELECTs with INSERT or UPDATE??
corereader contains safeguards to prevent updates, but its design helps quick development of queries such as yours. you can do a multiple join with multiple selects just by pointing and clicking to try out various ideas. it's free at http://corereader.com/ it installs at the novice skill level, but you will need to set it to the proficient level. (it queries all servers and runs from an ms. windows front end.) Hi, i am inserting data in a mysql table like this: INSERT INTO mytable1 (bu_main_id) SELECT main_id FROM mytable2 WHERE username=\$username\ that's ok. but now i would like to insert 2 values (bu_main_id and status), and i would like to know, is it possible to use 2 or more SELECTs together with an INSERT, for example: INSERT INTO mytable1 (bu_main_id, status) SELECT main_id FROM mytable2 WHERE username=$username, SELECT config_mode FROM mytable3 WHERE config_value=status my 2nd question, is it possible to use UPDATE and (mulstiple) SELECTs together i.e.: UPDATE mytable SET xxx = (SELECT xxx from mytable2 WHERE config=yyy) ... thanx Mirza -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is there Examples and Documents on joining tables?
right. corereader is designed to query all platforms from an ms. windows frontend. Thanks John, I looked through your web site, it seems to me that corereader is microsoft based product. I'm on Linux RedHat 7.2. thanks [EMAIL PROTECTED] wrote: corereader will help you do a pointclick join of up to four tables. you can operate corereader without it, but reading the documentation is strongly recommended. Is there a document that compiles examples on different ways of joining two or more tables? I found a few on http://www.mysql.com/doc/en/JOIN.html but they do not cover joining more than two tables. Thanks for any suggestions. -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is there Examples and Documents on joining tables?
right. it also won't run on mainframes. :) corereader is designed to query all platforms from an ms. windows frontend. Thanks John, I looked through your web site, it seems to me that corereader is microsoft based product. I'm on Linux RedHat 7.2. thanks [EMAIL PROTECTED] wrote: corereader will help you do a pointclick join of up to four tables. you can operate corereader without it, but reading the documentation is strongly recommended. Is there a document that compiles examples on different ways of joining two or more tables? I found a few on http://www.mysql.com/doc/en/JOIN.html but they do not cover joining more than two tables. Thanks for any suggestions. -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select Query
if you have an ms. windows machine for a front end, i recommend that you download corereader from http://CoreReader.com/ . ( it's free. ) it does pointclick queries, so you can quickly experiment with them until you get what you want. it installs at the novice level, so set it to the proficient skill level. in the where clause frame, you'll find drop-down lists for the selects. i believe that the one that you want is the is in which will produce the ansi92 in select for you. when it's returning what you want, open the sql frame, which will show you the sql statement that it built for you. (fair warning: not even corereader can make the data connection simple. ) Query ( that gets past the anti spam ) Question is.. I have a select where I want to get ID 15 id 25 Can I do something like WHERE ID = 15,25 Or do I have to do WHERE ID = 15 ID = 25 - Chris Kay Techex Communications Website: www.techex.com.au Email: [EMAIL PROTECTED] Telephone: 1300 88 111 2 - Fax: (02) 9970 5788 - -- John Ragan [EMAIL PROTECTED] 501-228-0317 http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Select Query
i just tried, and it came right up. the server is located in the southern u.s., so perhaps the hurricane is effecting it. I seem to be having problems trying to get to http://www.corereader.com ... Anyone else? +--+ | ___ ___ | | //_/_/_/_/_/ //_/_/_/ | |//_/ //_/ _/ | | //_/ //_/ _/| | //_/ //_/_/_/ | |--| | Tony Devlin - [EMAIL PROTECTED] | | Airewaves Broadband, Systems Admin | | Website - www.airewaves.com | | Atlanta, Ga - 678.522.3923 | | Beaufort, SC - 843.379.AIRE(2473)| +--+ -Original Message- From: John Ragan [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 03, 2002 10:47 AM To: [EMAIL PROTECTED]; Chris Kay Subject: Re: Select Query if you have an ms. windows machine for a front end, i recommend that you download corereader from http://CoreReader.com/ . ( it's free. ) it does pointclick queries, so you can quickly experiment with them until you get what you want. it installs at the novice level, so set it to the proficient skill level. in the where clause frame, you'll find drop-down lists for the selects. i believe that the one that you want is the is in which will produce the ansi92 in select for you. when it's returning what you want, open the sql frame, which will show you the sql statement that it built for you. (fair warning: not even corereader can make the data connection simple. ) Query ( that gets past the anti spam ) Question is.. I have a select where I want to get ID 15 id 25 Can I do something like WHERE ID = 15,25 Or do I have to do WHERE ID = 15 ID = 25 - Chris Kay Techex Communications Website: www.techex.com.au Email: [EMAIL PROTECTED] Telephone: 1300 88 111 2 - Fax: (02) 9970 5788 - -- John Ragan [EMAIL PROTECTED] 501-228-0317 http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- John Ragan [EMAIL PROTECTED] 501-228-0317 http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is there Examples and Documents on joining tables?
corereader will help you do a pointclick join of up to four tables. you can operate corereader without it, but reading the documentation is strongly recommended. Is there a document that compiles examples on different ways of joining two or more tables? I found a few on http://www.mysql.com/doc/en/JOIN.html but they do not cover joining more than two tables. Thanks for any suggestions. -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mixing Linux and Windows and paying for it
the answer to your question is yes. however, what you're considering is a complex technical exercise while it's being set up. after you get it going, it'll work like a charm, but your technical people will need to know what they are doing. corereader is a ms. windows app and one of its purposes is to reduce some of the pain involved in experimenting with data connections and queries. it will query any data source on any platform with simple point and click queries, but making the connection is difficult. if you want it, it's free from its own site, http://www.CoreReader.com/ the documentation on the site will give you some idea of a few of the problems involved. it's easy to operate, but the concepts involved are complex, so please read the documentation. William R. Mussatto [EMAIL PROTECTED] On Mon, 30 Sep 2002, Drulli B wrote: 1. Can I run a mySQL server on a Linux computer, and query it through myODBC running on a Windows computer? Why not query it directly on its std port? What language are you using? Perl (including ISAPI version .plx) can use DBI and connect directly. Fact is that I'm not using any particular language, but rather Other People's programs - statistical and data mining software that only supports ODBC. So what I'm wondering is: if I get the (not at all knowledgeable about mysql or any other database) computer department to install linux on a computer and install mysql on that computer, can I just enter its ip address in the myodbc dialog, and be amazed at how well it works? Bjarki -- John Ragan [EMAIL PROTECTED] 501-228-0317 http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SHAPE command for Hierarchical Recordset
try corereader at http://ww.corereader.com/ it's certainly not designed to create reports with much of eye candy. however, it queries mysql and access (and any other data source), lets you create queries with point and click, stores queries and connections, and gives you a few report headers. it's mainly a query tool, but it outputs to text, schema based xml, etc, so you can feed its output into a word processor or whatever for eye candy. Where can I get those tools? With MS access, my queries work fine after importing the data from MySQL Thanks Emery - Original Message - From: CP [EMAIL PROTECTED] To: NZEYIMANA Emery Fabrice [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, September 27, 2002 10:47 AM Subject: Re: SHAPE command for Hierarchical Recordset I would suggest you use MS Access report with MyTool-DLL For VB and MySQL. - Original Message - From: NZEYIMANA Emery Fabrice [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, September 27, 2002 4:40 PM Subject: SHAPE command for Hierarchical Recordset I have created a MySQL database that will help me manage my Invoicing system but I have been stack when I tried to print a report using a VB application. I have read from Help that I will need to use a hierarchical recordset to achieve what I want to. You know an Invoice has some headers like: Customer names, Invoice number, ... It also has details of ordered products/services Like: Item ID, Description, ... So my invoice should look like: ++ NZEYIMANA (Customer name) Some Addresses Invoice No.: 2002-09/5 -- -- -+ Item IDDescriptionQtyUPTotal -- -- -+ 12dfhsdfdsfjsd525 125 ... ... _ Total 125 VAT: - ++ To build such an invoice using ADO, VB, DataReport you need that Hierarchical recordset because DataReport won't accept if it is not a hierarchical recordset. How do I achieve this? Thanks Emery -- John Ragan [EMAIL PROTECTED] 501-228-0317 http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Win32::ODBC Question (Oracle)
, is strictly prohibited. Please notify the sender by return e-mail and delete this e-mail from your system. Unless explicitly and conspicuously designated as E-Contract Intended, this e-mail does not constitute a contract offer, a contract amendment, or an acceptance of a contract offer. This e-mail does not constitute a consent to the use of sender's contact information for direct marketing purposes or for transfers of data to third parties. Francais Deutsch Italiano Espanol Portuges Japanese Chinese Korean http://www.DuPont.com/corp/email_disclaimer.html ___ Perl-Win32-Users mailing list [EMAIL PROTECTED] To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs ___ Perl-Win32-Users mailing list [EMAIL PROTECTED] To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs ___ Perl-Win32-Users mailing list [EMAIL PROTECTED] To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs -- John Ragan [EMAIL PROTECTED] 501-228-0317 http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL as a desktop DB
if your users need queries only, you might check out corereader. it's a free download from http:/www.corereader.com/ . it's a point and click solution for any data source, so the only hard part is the initial data connection. it has built-in security to prevent updates, so if your people need more than queries, it won't work. the nice part, in addition to the point and click queries, is that it'll work with any of your data sources, including access. Hi David, Adam, On Wed, 2002-09-25 at 10:37, David Lloyd wrote: Obviously MySQL would have advantages if I intended to use it as a server database with concurrent users. But is it faster than Access in the single-user environment, when dealing with large databases? I'd think so, yes. Different design. Of course it will depend on your table layout and query phrasing. MySQL in a single user environment is a pain in the arse because of its lack of an Access Like front end that is actually useful and featureful (Access is crap, but it's a better database frontend than currently exists). You can use MS Access as a front-end for MySQL, via MyODBC. This is particularly useful when migrating data from Access to MySQL, or when existing users are used to the MS Access interface. On a related note, have you looked at MyCC (MySQL Control Center), downloadable from www.mysql.com. A graphical client for both Linux and Windows. It can't do everything yet, but users say it has a very nice intuitive interface and it's already got quite a following! Regards, Arjen. -- MySQL Training in Auckland and Sydney, http://www.mysql.com/training/ Purchase Training, Support, Licenses @ https://order.mysql.com/?marl __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia ___/ www.mysql.com -- John Ragan [EMAIL PROTECTED] 501-228-0317 http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Basic SQL join question
if you want a way to quickly experiment with sql joins, try corereader. it's a free download from http://www.corereader.com/ after you make a data connection, you press the load button to load the metadata. after that, everything is point and click to select from drop-down lists. that makes it super quick and easy to experiment with joins until you get a feel for them. when you find the query that you want, you can open the sql frame to see the sql statement that it built. you can also save the statements into a library for future use. the negative is that it runs only under ms windows. the positive is that it will query any data source on any platform from mainframes to spreadsheets, including mysql. ( ok, so i'm proud of my work. :) ) SELECT * FROM Projects INNER JOIN KeywordLink ON Projects.Id = KeywordLink.Pid INNER JOIN Keywords KeywordLink.Kid = Keywords.Id WHERE Keyword LIKE '%historical%' AND Keyword like '%scenic%'; Out of curiousity, does the order matter? I have a JOIN with about 6 tables, some are very small, some are quite large. I know with LEFT JOIN if I switched the order of the tables around, the queries could speed up or slow down dramatically. Is the same true with INNER JOIN? Still trying to completely grasp JOINs, getting closer though... Also, shouldn't the second INNER JOIN have an ON keyword? Josh 1. Note the single quotes. 2. You can place the join logic in the WHERE clause but I prefer the clarity obtained by keeping it in JOIN clauses and using the WHERE only to contain the include if logic. hth, Arthur -Original Message- From: Adam Randall [mailto:[EMAIL PROTECTED]] Sent: Saturday, September 21, 2002 5:18 AM To: [EMAIL PROTECTED] Subject: Basic SQL join question Okay, I've thought about this every way I can conceive of, but I cannot figure out the sql query logic involved in joining three tables together. Here is what I am trying to do: I have three tables: keywords keywordlink projects keywords has these fields: id keyword keywordlink has these fields: id pid kid projects has a lot of fields, but it's primary key is ID What keywords holds is the keywords used in the various different tables in the database. keywordlink associates a project with several keywords: example keywords: id | keyword 1 | landscape 2 | historical 3 | scenic example keywordlink: id | pid | kid 1 | 1 | 2 2 | 1 | 3 3 | 2 | 1 4 | 2 | 3 example projects: id | name 1 | example 2 | extra Now, what I am trying to do is basically search the keywords database for keyword names, and then return a list of project names that are associated with those keywords. If the keywords were stored in the projects database, this is basically what I would want it to do (assume all the keywords are stored in a field called keywords in the projects table): SELECT * FROM projects where keywords like %historical% and keywords like %scenic%; This would return to me the projects that have historical and scenic in the keywords field. Now, how do I do this same operation with it broken out like I have above. The reason I am not storing the keywords in the projects table is that it would be quite a chore in changing the keywords in the system if I did that (modify one keyword, modify all the projects, etc). Anyone have any words of advice for me? Adam. -- John Ragan [EMAIL PROTECTED] 501-228-0317 http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
CoreReader
I am the creator of CoreReader and have done rudimentary testing against MySql. However, not being familiar with that data source, I hesitate to state that CoreReader runs against it without problems. Has anybody who knows what they are doing used CoreReader with MySql enough to say that there are no problems involved? -- John Ragan [EMAIL PROTECTED] 501-228-0317 http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
