Re: Technical Clarification
Hi,
On Mon, Mar 19, 2001 at 01:23:56AM -0600, Jason Landry wrote:
I know that this response was directly related to a question about
subqueries, but I think it's important to point this out, because I almost
didn't pursue this avenue as a result.
As Fred pointed out, MySQL does not support IN and NOT IN, but *only* in the
case of subqueries:
select * from table 1 where id in (select id from table2)
However, it DOES support IN and NOT IN as long as a subquery is not used:
select * from table 1 where id in (115,515,614,23,51,24,15,12,51,23)
Ah yes, thanks for correcting me. I use the constant IN frequently as well.
It can even be useful in some cases to replace sub-queries by serialized
queries if you know the result isn't a very large set of values.
I believe MySQL optimizes matches against the list of values by putting them
in a hash table, but by doing two queries you'll send the result of the first
('sub'-select) back and forth which is not very efficient. You may even bump
into the size limit of query packets (16M).
Fred.
--
Fred van Engen XO Communications B.V.
email: [EMAIL PROTECTED] Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Technical Clarification
hi,
This is kathir from Uniware Systems, We are developing project on ASP with Backend
as MySQL, We having a problem using "IN" and "NOT IN" operators in sub query
here i am listing my query as follows:
"select c.pickupdate,c.contno, r.relrefno, r.custname, r.contsize, r.conttype,
r.qtybooked from contpickup c,releaseauthor r where c.relrefno=r.relrefno and
c.relrefno in(select relrefno from contpickup where contno='" ccode "') and
c.contno not in (select contno from billlading where relrefno in(select relrefno from
contpickup where contno='" ccode "'))"
iam selecting the values from 3 different tables using in and not in operators. When
iam executing the above query it displaying the error as follows:
[TCX][MyODBC]You have an error in your SQL syntax near 'CRXU9021208'')' at line 1
for ur clarification the simple query is also not executing using IN operator.
select relrefno from releaseauthor where relrefno in
(select relrefno from contpickup)
But this statement is executing
select relrefno from releaseauthor where relrefno in
("123","456")
pls.. let me know solutions as earliest for the above query and using IN and NOT IN
operators.
note:
The above query is executing in MSSQL Server 7.0 and Oracle 8.
regards,
kathir (Programmer Analyst)
_
Chat with your friends as soon as they come online. Get Rediff Bol at
http://bol.rediff.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: Technical Clarification
Hi Kathir, On Mon, Mar 19, 2001 at 05:38:55AM -, Rama kathiresan kathiresan wrote: This is kathir from Uniware Systems, We are developing project on ASP with Backend as MySQL, We having a problem using "IN" and "NOT IN" operators in sub query ... iam selecting the values from 3 different tables using in and not in operators. When iam executing the above query it displaying the error as follows: [TCX][MyODBC]You have an error in your SQL syntax near 'CRXU9021208'')' at line 1 MySQL doesn't support IN and NOT IN. The manual and mailing lists describe alternatives for many cases, but these are less readable (and maybe slower). Regards, Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Technical Clarification
Hi, We are doing a project with combination of ASP MYSQL (backend). We are having some problem for setting relationship between the tables. We have to know whether foreign key will be supported in MySQL and we came to know that there is no use for foreign key(as per your documentation: The FOREIGN KEY, CHECK, and REFERENCES clauses don't actually do anything. The syntax for them is provided only for compatibility, to make it easier to port code from other SQL servers and to run applications that create tables with references.) pls.. let us know whether we can use foreign key to access master table records in child tables. if there is any possiblity pls... provide solution for us. awaiting ur reply, at the earliest. regards, kathir.
RE: Technical Clarification
Greetings and salutations, EverybodySingAlong MySQL Supports FKs. A FK is a table's PK value, stored in another table in order to relate the records together. It does NOT support FK CONSTRAINTS. (Cascading deletes, restricts, etc.) /EverybodySingAlong ...and now for the opinion portion of our show... For all the of databases I've worked on for the past 16 years, this is not a deal killer. It just means that I, as the developer, have to do my job. IN a LOT of ways, FK constraints are bad, evil things that were designed so programmers could slack. :) Kidding, of course, but if not implemented properly, they can cause problems and they ALWAYS cost performance. Currently, my largest MySQL databases is a whopping 30 tables! (ok, so I'm not working on a grand scale here!) But 90% of my tables have FKs in them from other tables. The only difference between this database and it's MSSQL sibling (I'm porting) is that I have to manually cascade my deletes. But since my license for MSSQL was going to be $5k+ this year, it's worth it! :) Stored Procedures, IMHO, should be a much higher priority for the development team. Cal http://www.calevans.com -Original Message- From: gopinath [mailto:[EMAIL PROTECTED]] Sent: Monday, March 12, 2001 3:16 AM To: [EMAIL PROTECTED] Subject: Technical Clarification Hi, We are doing a project with combination of ASP MYSQL (backend). We are having some problem for setting relationship between the tables. We have to know whether foreign key will be supported in MySQL and we came to know that there is no use for foreign key(as per your documentation: The FOREIGN KEY, CHECK, and REFERENCES clauses don't actually do anything. The syntax for them is provided only for compatibility, to make it easier to port code from other SQL servers and to run applications that create tables with references.) pls.. let us know whether we can use foreign key to access master table records in child tables. if there is any possiblity pls... provide solution for us. awaiting ur reply, at the earliest. regards, kathir. - 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
Technical Clarification
Hi, Is there any optimization tool for MySQL database , so that it can be fast? Please let us know as we are uploading our site using MySQL. Regards Gopinath
Technical Clarification
Hi, "gopinath" == gopinath [EMAIL PROTECTED] writes: gopinath Hi, Is there any optimization tool for MySQL database , so gopinath that it can be fast? gopinath Please let us know as we are uploading our site using MySQL. gopinath Regards gopinath Gopinath !DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 gopinath Transitional//EN" HTMLHEAD META content="text/html; gopinath charset=iso-8859-1" http-equiv=Content-Type META gopinath content="MSHTML 5.00.2314.1000" name=GENERATOR gopinath STYLE/STYLE /HEAD BODY bgColor=#ff DIVFONT gopinath size=2 DIVFONT size=2Hi,/FONT/DIV DIVnbsp;/DIV gopinath DIVFONT size=2Is there any optimization tool for MySQL gopinath database , so that it can be fast?nbsp; /FONT/DIV gopinath DIVnbsp;/DIV DIVFONT size=2Please let us know as we gopinath are uploading our sitenbsp; using MySQL./FONT/DIV gopinath DIVnbsp;/DIV DIVFONT size=2Regards/FONT/DIV gopinath DIVnbsp;/DIV DIVFONT gopinath size=2Gopinath/FONT/DIV/FONT/DIV/BODY/HTML If you have a valid MySQL support certificate, you can ask for help from the MySQL developers. Here are our support options: Basic email EURO 170 Extended email EURO 1000 LoginEURO 2000 Extended Login EURO 5000 Basic email support Basic email support is a very inexpensive support option and should be thought of more as a way to support our development of MySQL than as a real support option. Basic email support includes the following types of service: If your question is already answered in the manual, we will inform you of the correct section in which you can find the answer. If the answer is not in the manual, we will point you in the right direction to solve your problem. We guarantee a timely answer for your email messages. We can't guarantee that we can solve any problem, but at least you will receive an answer if we can contact you by email. We will help with unexpected problems when you install MySQL from a binary distribution on supported platforms. This level of support does not cover installing MySQL from a source distribution. "Supported" platforms are those for which MySQL is known to work. See section Operating systems supported by MySQL. We will help you with bugs and missing features. Any bugs that are found are fixed for the next MySQL release. If the bug is critical for you, we will mail you a patch for it as soon the bug is fixed. Critical bugs always have the highest priority for us, to ensure that they are fixed as soon as possible. Your suggestions for the further development of MySQL will be taken into consideration. By taking email support you have already helped the further development of MySQL. If you want to have more input, upgrade to a higher level of support. If you want us to help optimize your system, you must upgrade to a higher level of support. Extended email support Extended email support includes everything in basic email support with these additions: Your email will be dealt with before mail from basic email support users and non-registered users. Your suggestions for the further development of MySQL will receive strong consideration. Simple extensions that suit the basic goals of MySQL are implemented in a matter of days. By taking extended email support you have already helped the further development of MySQL. We include a binary version of the pack_isam packing tool for creating fast compressed read-only databases (it does not support BLOB or TEXT types yet). The current server includes support for reading such databases but not the packing tool used to create them. Typical questions that are covered by extended email support are: We will answer and (within reason) solve questions that relate to possible bugs in MySQL. As soon as the bug is found and corrected, we will mail a patch for it. We will help with unexpected problems when you install MySQL from a source or binary distribution on supported platforms. We will answer questions about missing features and offer hints how to work around them. We will provide hints on optimizing mysqld for your situation. You are allowed to influence the priority of items on the MySQL TODO. This will ensure that the features you really need will be implemented sooner than they might be otherwise. Login support Login support includes everything in extended email support with these additions: Your email will be dealt with even before mail from extended email support users. Your suggestions for the further development of MySQL will be taken into very high consideration. Realistic extensions that can be implemented in a couple of hours and that suit the basic goals of MySQL will be implemented as soon as possible. If you have a very specific problem, we can try to log in on your system to solve the problem "in place." Like any database vendor, we can't guarantee that we can rescue any data from crashed tables, but if the worst happens we will help you rescue as much as possible. MySQL has proven itself
Technical Clarification
Hi,
We have uploaded our site that uses Chilisoft ASP with MySQL backend database.We are
now in testing stage.It works fine with just 10 records in each table. We have our
original data in Excel. Now how do we convert that excel data to mysql data (which is
in bulk.) and upload .
The main issue is where in the server should the mysql (converted) data be uploaded.
The Connection string we use is
set cc=server.createobject("adodb.connection")
cc.open "DRIVER={MySQL};SERVER=www.www.net;DATABASE=mydatabase;
UID=myUser;PWD=myPwd"
Please do give us a solution in this regard.
Regards
Gopinath
