RE: Query Help
Paul
Err no, new area for me this
My two queries independently look like this
This give me all the lists some one is not a member of
SELECT lists_.Name FROM lists_ WHERE (((lists_.Name_) Not In (select
members_.List_ from members_ where members_.EmailAddr_ like (' em
'
And for all the list someone is in
SELECT ists_.Name_, members_.EmailAddr_
FROM lists_ INNER JOIN members_ ON lists_.Name_ = members_.List_
WHERE (((members_.EmailAddr_) like (' em ')))
So a union joins them somehow ?
Oh em is the variable I feed in
John B
-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: 29 February 2004 00:08
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Query Help
At 23:09 + 2/28/04, John Berman wrote:
Hi
Using MySql 4.x and need some help with a query
There are two tables
Lists
Which holds list name +other stuff
Members
Which holds list name from above, email address + other stuff
I want to list all the lists and then which lists a member is associated
with.
Eg
List1 - Member
List2 - Not a Member
List3 - Member
I can do a query that show the lists some is a member or a separate query
to
show which they are not a member of but not a query to bring the whole
thing
together
Have you tried using a UNION of the two queries?
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Help
At 0:32 + 2/29/04, John Berman wrote:
Paul
Err no, new area for me this
My two queries independently look like this
This give me all the lists some one is not a member of
SELECT lists_.Name FROM lists_ WHERE (((lists_.Name_) Not In (select
members_.List_ from members_ where members_.EmailAddr_ like (' em
'
And for all the list someone is in
SELECT ists_.Name_, members_.EmailAddr_
FROM lists_ INNER JOIN members_ ON lists_.Name_ = members_.List_
WHERE (((members_.EmailAddr_) like (' em ')))
So a union joins them somehow ?
Yes, though you would need to select the same number of columns in
each query. Read here for more information:
http://www.mysql.com/doc/en/UNION.html
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Help
Paul
Well getting there, im now selecting the correct number of columns but get a
cant be distinct error ?
John B
-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: 29 February 2004 00:47
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: Query Help
At 0:32 + 2/29/04, John Berman wrote:
Paul
Err no, new area for me this
My two queries independently look like this
This give me all the lists some one is not a member of
SELECT lists_.Name FROM lists_ WHERE (((lists_.Name_) Not In (select
members_.List_ from members_ where members_.EmailAddr_ like (' em
'
And for all the list someone is in
SELECT ists_.Name_, members_.EmailAddr_
FROM lists_ INNER JOIN members_ ON lists_.Name_ = members_.List_
WHERE (((members_.EmailAddr_) like (' em ')))
So a union joins them somehow ?
Yes, though you would need to select the same number of columns in
each query. Read here for more information:
http://www.mysql.com/doc/en/UNION.html
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Help
Got it working at last
SELECT lists_.DescShort_ FROM lists_ WHERE (((lists_.Name_) Not In (select
members_.List_ from members_ where members_.EmailAddr_ like (' em
' union SELECT lists_.DescShort_ FROM members_ INNER JOIN lists_ ON
members_.List_ = lists_.Name_ WHERE (members_.EmailAddr_ = (' em '))
My only problem being it now lists the lists Im not a member of and the ones
I am a member of - how on earth do I show on screen which is which ?
Regards
John Berman
-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: 29 February 2004 00:47
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: Query Help
At 0:32 + 2/29/04, John Berman wrote:
Paul
Err no, new area for me this
My two queries independently look like this
This give me all the lists some one is not a member of
SELECT lists_.Name FROM lists_ WHERE (((lists_.Name_) Not In (select
members_.List_ from members_ where members_.EmailAddr_ like (' em
'
And for all the list someone is in
SELECT ists_.Name_, members_.EmailAddr_
FROM lists_ INNER JOIN members_ ON lists_.Name_ = members_.List_
WHERE (((members_.EmailAddr_) like (' em ')))
So a union joins them somehow ?
Yes, though you would need to select the same number of columns in
each query. Read here for more information:
http://www.mysql.com/doc/en/UNION.html
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Help
At 2:45 + 2/29/04, John Berman wrote:
Got it working at last
SELECT lists_.DescShort_ FROM lists_ WHERE (((lists_.Name_) Not In (select
members_.List_ from members_ where members_.EmailAddr_ like (' em
' union SELECT lists_.DescShort_ FROM members_ INNER JOIN lists_ ON
members_.List_ = lists_.Name_ WHERE (members_.EmailAddr_ = (' em '))
My only problem being it now lists the lists Im not a member of and the ones
I am a member of - how on earth do I show on screen which is which ?
Maybe:
Select an extra column in each SELECT. SELECT member, ... UNION
SELECT non-member, ...
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urgent help required for mysql
Soni, Sanjay K [EMAIL PROTECTED] wrote: 040225 13:57:20 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 040225 13:57:21 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 040225 13:57:22 InnoDB: Log file ./ib_logfile0 did not exist: new to be = created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 040225 13:57:22 InnoDB: Log file ./ib_logfile1 did not exist: new to be = created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 040225 13:57:25 InnoDB: Started 040225 13:57:25 Fatal error: Can't open privilege tables: Table = 'mysql.host' doesn't exist 040225 13:57:25 Aborting =20 040225 13:57:25 InnoDB: Starting shutdown... 040225 13:57:27 InnoDB: Shutdown completed Look into MySQL data dir and check if files host.frm, host.MYI, host.MYD exist in the directory of 'mysql' database. -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urgent help required for mysql
Hi! It seems that you have installed mysql, but you did not run the mysql_install_db, this script will create mysql internal databases(the ones that carries user and config info), the script is usually in install_prefix/bin/mysql_install_db If you have no recently installed mysql, and the server has sometime working already before this message, there is some serius problem here. But i sencerely doubt it, innodb is recreating the tables and indices, this looks like a fresh install to me =) Best Regards! On Wed, 2004-02-25 at 19:48, Soni, Sanjay K wrote: 040225 13:57:20 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 040225 13:57:21 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 040225 13:57:22 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 040225 13:57:22 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 040225 13:57:25 InnoDB: Started 040225 13:57:25 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 040225 13:57:25 Aborting 040225 13:57:25 InnoDB: Starting shutdown... 040225 13:57:27 InnoDB: Shutdown completed -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 - Tél: +58-241-8507325 | ||geek by nature - linux by choice | |...|
RE: urgent help required for mysql
Sanjay, It seems you didn't run mysql_install_db script which is located in /scripts directory. This script creates Grant tables for you when you first install mysql. Basically the error says you missing those grant tables, you can re-run the script and recreate them. Hope that helps!! Raza GE Financial Assurance -Original Message- From: Soni, Sanjay K [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 25, 2004 6:48 PM To: [EMAIL PROTECTED] Subject: urgent help required for mysql 040225 13:57:20 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 040225 13:57:21 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 040225 13:57:22 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 040225 13:57:22 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 040225 13:57:25 InnoDB: Started 040225 13:57:25 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 040225 13:57:25 Aborting 040225 13:57:25 InnoDB: Starting shutdown... 040225 13:57:27 InnoDB: Shutdown completed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query help - add results then divide by
What about SELECT (SUM( ads.col * 1.91) * ads.depth ) ) / 131.77 FROM ads WHERE date = '2004-02-26' AND editionID = '13' AND ads.page = '16' Original Message On 2/25/04, 4:19:12 PM, Rogers, Dennis [EMAIL PROTECTED] wrote regarding Query help - add results then divide by : Good afternoon, How can I take the 3 results below add them together then divide by 131.77? Can it all be done in one SQL statement? Thanks in advance. mysql describe ads; +---+---+--+-+++ | Field | Type | Null | Key | Default| Extra | +---+---+--+-+++ | adID | int(11) | | PRI | NULL | auto_increment | | page | int(11) | | | 0 | | | adnum | varchar(20) | | || | | date | date | | | -00-00 | | | depth | decimal(3,2) | YES | | 0.00 | | | timestamp | timestamp(14) | YES | | NULL | | | col | int(11) | YES | | 0 | | | acc | varchar(50) | | || | | editionID | int(11) | | | 0 | | +---+---+--+-+++ 9 rows in set (0.00 sec) mysql SELECT ((ads.col * 1.91) * ads.depth) FROM ads Where date = '2004-02-26' AND editionID = '13' AND ads.page = '16'; +-+ | ((ads.col * 1.91) * ads.depth) | +-+ |7.64 | | 34.38 | |7.64 | +-+ 3 rows in set (0.01 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query help - add results then divide by
Thanks so much!! SELECT (SUM(( ads.col * 1.91) * ads.depth ) / 131.77) * 100 FROM ads WHERE date = '2004-02-26' AND editionID = '13' AND ads.page = '16' -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 25, 2004 5:55 PM To: Rogers, Dennis Cc: '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'; Hines, David Subject: Re: Query help - add results then divide by What about SELECT (SUM( ads.col * 1.91) * ads.depth ) ) / 131.77 FROM ads WHERE date = '2004-02-26' AND editionID = '13' AND ads.page = '16' Original Message On 2/25/04, 4:19:12 PM, Rogers, Dennis [EMAIL PROTECTED] wrote regarding Query help - add results then divide by : Good afternoon, How can I take the 3 results below add them together then divide by 131.77? Can it all be done in one SQL statement? Thanks in advance. mysql describe ads; +---+---+--+-+++ | Field | Type | Null | Key | Default| Extra | +---+---+--+-+++ | adID | int(11) | | PRI | NULL | auto_increment | | page | int(11) | | | 0 | | | adnum | varchar(20) | | || | | date | date | | | -00-00 | | | depth | decimal(3,2) | YES | | 0.00 | | | timestamp | timestamp(14) | YES | | NULL | | | col | int(11) | YES | | 0 | | | acc | varchar(50) | | || | | editionID | int(11) | | | 0 | | +---+---+--+-+++ 9 rows in set (0.00 sec) mysql SELECT ((ads.col * 1.91) * ads.depth) FROM ads Where date = '2004-02-26' AND editionID = '13' AND ads.page = '16'; +-+ | ((ads.col * 1.91) * ads.depth) | +-+ |7.64 | | 34.38 | |7.64 | +-+ 3 rows in set (0.01 sec)
Re: Query help - add results then divide by
I think that you can just do this: select sum(ads.col)*1.191*sum(ads.depth)/131.77 where date ='2004-02-26' AND editionID = '13' AND ads.page = '16'; because of the disttributive property of multiplication. (2 * 1.191) +(6*1.91) +(4*1.91)/131.77 = 12 *1.91/131.77 = (12*1.91)/131.77 = 12*(1.91/131.77) Test it to make sure I understand what you're asking, but it worked for my in my tests. bob Rogers, Dennis wrote: Good afternoon, How can I take the 3 results below add them together then divide by 131.77? Can it all be done in one SQL statement? Thanks in advance. mysql describe ads; +---+---+--+-+++ | Field | Type | Null | Key | Default| Extra | +---+---+--+-+++ | adID | int(11) | | PRI | NULL | auto_increment | | page | int(11) | | | 0 | | | adnum | varchar(20) | | || | | date | date | | | -00-00 | | | depth | decimal(3,2) | YES | | 0.00 | | | timestamp | timestamp(14) | YES | | NULL | | | col | int(11) | YES | | 0 | | | acc | varchar(50) | | || | | editionID | int(11) | | | 0 | | +---+---+--+-+++ 9 rows in set (0.00 sec) mysql SELECT ((ads.col * 1.91) * ads.depth) FROM ads Where date = '2004-02-26' AND editionID = '13' AND ads.page = '16'; +-+ | ((ads.col * 1.91) * ads.depth) | +-+ |7.64 | | 34.38 | |7.64 | +-+ 3 rows in set (0.01 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query help - add results then divide by
Rogers, Dennis wrote: Good afternoon, How can I take the 3 results below add them together then divide by 131.77? Can it all be done in one SQL statement? Thanks in advance. mysql describe ads; +---+---+--+-+++ | Field | Type | Null | Key | Default| Extra | +---+---+--+-+++ | adID | int(11) | | PRI | NULL | auto_increment | | page | int(11) | | | 0 | | | adnum | varchar(20) | | || | | date | date | | | -00-00 | | | depth | decimal(3,2) | YES | | 0.00 | | | timestamp | timestamp(14) | YES | | NULL | | | col | int(11) | YES | | 0 | | | acc | varchar(50) | | || | | editionID | int(11) | | | 0 | | +---+---+--+-+++ 9 rows in set (0.00 sec) mysql SELECT ((ads.col * 1.91) * ads.depth) FROM ads Where date = '2004-02-26' AND editionID = '13' AND ads.page = '16'; +-+ | ((ads.col * 1.91) * ads.depth) | +-+ |7.64 | | 34.38 | |7.64 | +-+ 3 rows in set (0.01 sec) SELECT SUM((ads.col * 1.91) * ads.depth)/131.77 FROM ads Where date = '2004-02-26' AND editionID = '13' AND ads.page = '16'; -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: pls help ...............
there must be some problem in concatenation of strings and number variables. If you can send the lines, we'll be able to suggest the exact change. Hope that helps Nitin - Original Message - From: CurlyBraces Technologies ( Pvt ) Ltd To: [EMAIL PROTECTED] Sent: Tuesday, February 24, 2004 12:08 PM Subject: Fw: pls help ... - Original Message - From: CurlyBraces Technologies ( Pvt ) Ltd To: [EMAIL PROTECTED] Sent: Tuesday, February 24, 2004 12:05 PM Subject: pls help ... hi , when i try to get mysql data to the web browser via php , always it is showing Parse error: parse error, unexpected T_LNUMBER, expecting ',' or ';' in /var/www/html/smsc/test1.php on line 14 error message. but i tried to rectify the essage , i couldn't. can some body help me to solve the problem ? thanx in advance curlys -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: pls help ...............
90% of the time you forgot the ; on the line before the parse error. if not then I would need to see the code to help you. Respectfully, Ligaya Turmelle Nitin Mehta [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] there must be some problem in concatenation of strings and number variables. If you can send the lines, we'll be able to suggest the exact change. Hope that helps Nitin - Original Message - From: CurlyBraces Technologies ( Pvt ) Ltd To: [EMAIL PROTECTED] Sent: Tuesday, February 24, 2004 12:08 PM Subject: Fw: pls help ... - Original Message - From: CurlyBraces Technologies ( Pvt ) Ltd To: [EMAIL PROTECTED] Sent: Tuesday, February 24, 2004 12:05 PM Subject: pls help ... hi , when i try to get mysql data to the web browser via php , always it is showing Parse error: parse error, unexpected T_LNUMBER, expecting ',' or ';' in /var/www/html/smsc/test1.php on line 14 error message. but i tried to rectify the essage , i couldn't. can some body help me to solve the problem ? thanx in advance curlys -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyODBC Help
I'm copying this to the list (hope you don't mind). You'll find you get quicker and better responses when all the experts on the list see your question. Plus, someone else may have a similar question and benefit from the answers you get. I've never seen this problem myself, but it looks like a configure error. I'd bet that if you look in your Makefile, you'll see a -L/yes/lib instead of -L/path/to/lib for some library. Perhaps you told configure --with-some-lib rather than --with-some-lib=/path/to/lib? I'd suggest you `make distclean` then try configure/make again. Assuming you get the same error, you could post your configure command and see if anyone can spot what you need to change. Michael Morgan, Andrew R. wrote: Mr. Stassen, Not trying to take advantage of your help this morning, but I wanted to know if you knew the answer to this problem now too. I'm trying to install MyODBC and now I get this error upon 'make': ../libtool[1296]: yes/lib: not found libtool: link: cannot determine absolute directory name of `yes/lib' make[2]: *** [libmyodbc3.la] Error 1 make[2]: Leaving directory `/virtual/MyODBC-3.51.06/driver' make[1]: *** [all] Error 2 make[1]: Leaving directory `/virtual/MyODBC-3.51.06/driver' make: *** [all-recursive] Error 1 Any ideas? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL-help needed
I'm still a beginner myself but try something like SELECT COUNT(YEAR), WINNER1 AS WINNER, WINNER2 AS WINNER, YEAR FROM CHAMPIONS WHERE CLASS = hd GROUP BY WINNER; I think this will give you something like: COUNT(YEAR) WINNER YEAR 2 carl1957 2 carl1985 1 mattias 1957 1 erik1985 Again I am a beginner and would have to test this to see if it actually gives me the right info or if I would have to tweek it. Respectfully, Ligaya Turmelle Computer Programmer Guam International Country Club 495 Battulo Street Dededo, Guam 96912 Tel: (671) 632-4445 Fax: (671) 632-4440 Reservations: (671) 632-4422 (GICC) -Original Message- From: Carl Schéle, IT, Posten [mailto:[EMAIL PROTECTED] Sent: Thursday, February 19, 2004 6:50 PM To: [EMAIL PROTECTED] Subject: SQL-help needed Hi! I got a table, champions, looking like this: idclass winner_1 winner_2 year - 0 hd carl mattias 1957 1 hs daniel 1982 2 hd erik carl 1985 3 js erik 1974 Imagine I want to see how many times each winner appears where class=hd and which year. In this case the answer would be: 2 carl 1957,1985 1 mattias 1957 1 erik 1985 Please help! Still using old MySQL 3.23.58. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL-help needed
Carl Schéle, IT, Posten wrote: Hi! I got a table, champions, looking like this: idclass winner_1 winner_2 year - 0 hd carl mattias 1957 1 hs daniel 1982 2 hd erik carl 1985 3 js erik 1974 Imagine I want to see how many times each winner appears where class=hd and which year. In this case the answer would be: 2 carl 1957,1985 1 mattias 1957 1 erik 1985 Please help! Still using old MySQL 3.23.58. The following is close to what you want: CREATE TEMPORARY TABLE champs (winner CHAR(10), year INT); # change the column types to match table champions INSERT INTO champs SELECT winner_1, year FROM champions WHERE class='hd'; INSERT INTO champs SELECT winner_2, year FROM champions WHERE class='hd' AND winner_2 IS NOT NULL; # some of your winner_2 spots are empty. If they're empty strings # instead of NULL, change IS NOT NULL to != '' SELECT * FROM champs ORDER by winner, year; +-+--+ | winner | year | +-+--+ | carl| 1957 | | carl| 1985 | | erik| 1985 | | mattias | 1957 | +-+--+ 4 rows in set (0.01 sec) SELECT count(*), winner FROM champs GROUP BY winner; +--+-+ | count(*) | winner | +--+-+ |2 | carl| |1 | erik| |1 | mattias | +--+-+ 3 rows in set (0.01 sec) === It seems to me that your table design is what makes this difficult. If you changed it to something like the following, where wintype denotes winner_1 or winner_2, this would be easier: id class winner wintype year - 1 hd carl 1 1957 2 hs daniel 1 1982 3 hd erik 1 1985 4 js erik 1 1974 5 hd mattias2 1957 6 hd carl 2 1985 You could then go straight to the select: SELECT * FROM champions WHERE class='hd' ORDER by winner, year; or SELECT count(*), winner FROM champions WHERE class='hd' GROUP BY winner; You could use a variant of the INSERT-SELECTs above to fill the new table, if you decide to go that way. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL-HELP
Hello, you might want to try select DISTINCT ? http://www.mysqlfreaks.com/statements/18.php /Jonas - Original Message - From: Carl Schéle, IT, Posten [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, February 17, 2004 3:49 PM Subject: SQL-HELP Hello! I got a table, champions, looking like this: id winner_1 winner_2 0carl mattias 1daniel carl 2erik daniel 3erik johan What I want is to retrieve the unique names ie: carl mattias daniel erik johan I use MySQL 3.23.58 (which means I can't use sub-selects). /Carl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL-HELP
Carl Schéle, IT, Posten wrote: I got a table, champions, looking like this: id winner_1 winner_2 0carl mattias 1daniel carl 2erik daniel 3erik johan What I want is to retrieve the unique names ie: carl mattias daniel erik johan I use MySQL 3.23.58 (which means I can't use sub-selects). The smart way: get a database that understands UNION. The other way: SELECT DISTINCT CASE WHEN c1.id = c1.id THEN c1.winner_1 ELSE c1.winner_2 END AS winner FROM champions c1, champions c2 Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Urgent help needed - SCO Unix binary
On Tue, 10 Feb 2004 [EMAIL PROTECTED] wrote: Hi, I could not find the binary for SCO Unixware (Intel based) in download section. I suppose mysql supports this environment too. May I know as to where can I find this binary. Do I need to build this myself? There are some version on ftp.zenez.com in the pub/zenez/prgms directory. You may want to try one of them. -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 1042 East Fort Union #135, Midvale Utah 84047 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Urgent help needed - SCO Unix binary
Hi! If memory serves me correctly, MySQL AB have stopped issuing SCO OpenServer and SCO UnixWare binaries publicly and will only be providing them to customers who take out commercial licences. This is probably due to the fact that OpenServer and UnixWare cost a lot compared ot other OSes, don't represent a huge percentage of the MySQL userbase and that the UDK for each is some disgusting amount to purchase. Which version of UnixWare is it? A workaround might be available in the form of the Linux Kernel Personality. Additionally, the Skunkware repository might be able to help you out if SCO's servers are currently accessible in this virus-infested internet we all wade in. I recently migrated a customer from UnixWare 7.1.1 to Redhat AS 2.1 (they had a copy that a mate sold them cheap) for this and a few other reasons (including Wow! The Redhat desktop looks pretty!). Regards, Chris [EMAIL PROTECTED] wrote: Hi, I could not find the binary for SCO Unixware (Intel based) in download section. I suppose mysql supports this environment too. May I know as to where can I find this binary. Do I need to build this myself? TIA Regards, Anup Mahansaria -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Urgent help needed - SCO Unix binary
Basically, the stability of the build is dependant on the following factors: * Suitable functions in your C library for threaded applications. UnixWare is apparently fine in this category and doesn't require any workarounds. Installing the latest patches and things would be in your best interest though. * The compiler you use. I'm pretty sure that Skunkware includes GCC 2.95.x . This is one of the recommended compilers for building MySQL binaries. If you install this and can get a clean compile, you'll probably be fine. Regards, Chris [EMAIL PROTECTED] wrote: Hi Chris, I want it on SCO Unixware 7.1.1 (intel based) I did see it in the installation manual of MySQL where they give some instructions for building the binaries for SCO Unixware. Can I go ahead and build it. How do you see the stability factor of such builds. Regards, Anup Mahansaria Chris Nolan [EMAIL PROTECTED] To [EMAIL PROTECTED] 02/10/04 07:47 PM cc [EMAIL PROTECTED] Subject Re: Urgent help needed - SCO Unix binary Hi! If memory serves me correctly, MySQL AB have stopped issuing SCO OpenServer and SCO UnixWare binaries publicly and will only be providing them to customers who take out commercial licences. This is probably due to the fact that OpenServer and UnixWare cost a lot compared ot other OSes, don't represent a huge percentage of the MySQL userbase and that the UDK for each is some disgusting amount to purchase. Which version of UnixWare is it? A workaround might be available in the form of the Linux Kernel Personality. Additionally, the Skunkware repository might be able to help you out if SCO's servers are currently accessible in this virus-infested internet we all wade in. I recently migrated a customer from UnixWare 7.1.1 to Redhat AS 2.1 (they had a copy that a mate sold them cheap) for this and a few other reasons (including Wow! The Redhat desktop looks pretty!). Regards, Chris [EMAIL PROTECTED] wrote: Hi, I could not find the binary for SCO Unixware (Intel based) in download section. I suppose mysql supports this environment too. May I know as to where can I find this binary. Do I need to build this myself? TIA Regards, Anup Mahansaria -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Urgent help needed - SCO Unix binary
Thanks Chris. Will get back to you if I get some problem. Regards, Anup Mahansaria Chris Nolan [EMAIL PROTECTED] To [EMAIL PROTECTED] 02/11/04 09:32 AM cc [EMAIL PROTECTED] Subject Re: Urgent help needed - SCO Unix binary Basically, the stability of the build is dependant on the following factors: * Suitable functions in your C library for threaded applications. UnixWare is apparently fine in this category and doesn't require any workarounds. Installing the latest patches and things would be in your best interest though. * The compiler you use. I'm pretty sure that Skunkware includes GCC 2.95.x . This is one of the recommended compilers for building MySQL binaries. If you install this and can get a clean compile, you'll probably be fine. Regards, Chris [EMAIL PROTECTED] wrote: Hi Chris, I want it on SCO Unixware 7.1.1 (intel based) I did see it in the installation manual of MySQL where they give some instructions for building the binaries for SCO Unixware. Can I go ahead and build it. How do you see the stability factor of such builds. Regards, Anup Mahansaria Chris Nolan [EMAIL PROTECTED] To [EMAIL PROTECTED] 02/10/04 07:47 PM cc [EMAIL PROTECTED] Subject Re: Urgent help needed - SCO Unix binary Hi! If memory serves me correctly, MySQL AB have stopped issuing SCO OpenServer and SCO UnixWare binaries publicly and will only be providing them to customers who take out commercial licences. This is probably due to the fact that OpenServer and UnixWare cost a lot compared ot other OSes, don't represent a huge percentage of the MySQL userbase and that the UDK for each is some disgusting amount to purchase. Which version of UnixWare is it? A workaround might be available in the form of the Linux Kernel Personality. Additionally, the Skunkware repository might be able to help you out if SCO's servers are currently accessible in this virus-infested internet we all wade in. I recently migrated a customer from UnixWare 7.1.1 to Redhat AS 2.1 (they had a copy that a mate sold them cheap) for this and a few other reasons (including Wow! The Redhat desktop looks pretty!). Regards, Chris [EMAIL PROTECTED] wrote: Hi, I could not find the binary for SCO Unixware (Intel based) in download section. I suppose mysql supports this environment too. May I know as to where can I find this binary. Do I need to build this myself? TIA Regards, Anup Mahansaria -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help with a SELECT statement across 3 tables
Dominique: Thanks for your suggestions/ideas. After playing with it for a little while longer of banging my head into a brick wall, I realized I was using a left join when I needed a right. You have my table structures pretty much down - here's the final SQL statement that I use to return 1 row per update per server that hasn't been applied: SELECT update_track.update_id, server.server_id, server.os, update_track.bugtraq_id FROM update_track LEFT JOIN server_update ON (update_track.update_id = server_update.update_id) RIGHT JOIN server ON (server_update.server_id = server.server_id) WHERE server.os = update_track.os AND server_update.server_id IS NULL; Which returns something easy to work with ( from a test set of 2 entries in the update_track table) +---+---+--++ | update_id | server_id | os | bugtraq_id | +---+---+--++ | 5 | 5 | Windows 2000 | 0 | | 5 | 7 | Windows 2000 | 0 | | 5 | 8 | Windows 2000 | 0 | | 5 |13 | Windows 2000 | 0 | | 5 |16 | Windows 2000 | 0 | | 5 |19 | Windows 2000 | 0 | | 5 |20 | Windows 2000 | 0 | | 5 |27 | Windows 2000 | 0 | | 5 |28 | Windows 2000 | 0 | | 5 |30 | Windows 2000 | 0 | | 5 |31 | Windows 2000 | 0 | | 5 |32 | Windows 2000 | 0 | | 5 |39 | Windows 2000 | 0 | | 5 |40 | Windows 2000 | 0 | | 5 |44 | Windows 2000 | 0 | | 5 |49 | Windows 2000 | 0 | | 5 |51 | Windows 2000 | 0 | | 4 |53 | RedHat 9 | 0 | | 5 |56 | Windows 2000 | 0 | | 5 | 104 | Windows 2000 | 0 | | 5 | 123 | Windows 2000 | 0 | | 4 | 532 | RedHat 9 | 0 | | 5 | 165 | Windows 2000 | 0 | | 4 | 295 | RedHat 9 | 0 | | 5 | 327 | Windows 2000 | 0 | | 5 | 361 | Windows 2000 | 0 | | 5 | 364 | Windows 2000 | 0 | | 5 | 388 | Windows 2000 | 0 | | 5 | 403 | Windows 2000 | 0 | | 5 | 405 | Windows 2000 | 0 | | 5 | 406 | Windows 2000 | 0 | | 5 | 407 | Windows 2000 | 0 | | 5 | 408 | Windows 2000 | 0 | | 5 | 424 | Windows 2000 | 0 | | 5 | 430 | Windows 2000 | 0 | | 5 | 455 | Windows 2000 | 0 | | 5 | 457 | Windows 2000 | 0 | | 4 | 467 | RedHat 9 | 0 | | 4 | 529 | RedHat 9 | 0 | | 4 | 512 | RedHat 9 | 0 | | 5 | 533 | Windows 2000 | 0 | | 5 | 554 | Windows 2000 | 0 | | 4 | 556 | RedHat 9 | 0 | | 4 | 558 | RedHat 9 | 0 | | 4 | 565 | RedHat 9 | 0 | | 4 | 575 | RedHat 9 | 0 | | 4 | 601 | RedHat 9 | 0 | | 5 | 614 | Windows 2000 | 0 | +---+---+--++ I think I should be able to claim SQL as a second language - you can say so much with it! Brandon Ewing -Original Message- From: Dominique Plante [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 27, 2004 4:43 PM To: 'Brandon Ewing' Subject: RE: Need help with a SELECT statement across 3 tables Brandon: I have been toying with your problem, and unfortunately, I have yet to come up with a good solution, since I am interested in seeing what the solution would be. Maybe you can confirm a few things. Do your table structures look anything like this? Server: | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | server_id | int(11) | | PRI | NULL| auto_increment | | location | varchar(30) | YES | | NULL|| | os| varchar(30) | YES | | NULL|| Server_update: | Field | Type | Null | Key | Default | Extra | ++---+--+-+-+---+ | server_id | int(11) | | | 0 | | | update_id | int(11) | | | 0 | | | updateDateTime | timestamp | YES | | NULL| | Update_track: | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | update_id
Re: Please help with check syntax
Aaron P. Martinez [EMAIL PROTECTED] wrote:
I am trying to set up a table from a script that came with some software
Value accounting/CRM and i'm getting a few errors, one of which i
can't seem to figure out/fix.
My system is RH 3.0 ES with mysql-server-3.23.58-1. I have innodb
tables configured with the following statement in my /etc/my.cnf:
innodb_data_file_path = ibdata1:10M:autoextend
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M
set-variable = innodb_log_file_size=20M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
The create table statement is below followed by the error.
create table ItemSalesTax (
STYPE integer not null primary key, /* STax Type */
SDESC varchar(35),
SPERC numeric(13,4) zerofill not null /* Percentage */
check(SPERC = 0),
SCONUM integer not null,
SYRNO integer not null,
SLEVEL varchar(4) not null, /* Access Control Level */
constraint staxlevel_ck
check (SLEVEL in ('READ','RW','DENY')),
constraint STax_fk foreign key (SCONUM, SYRNO)
references AccYear(ACONUM, AYEARNO)
);
ERROR 1064: You have an error in your SQL syntax near 'check(SPERC =
0),
SCONUM integer not null,
SYRNO i' at line 9
I am not great w/mysql but gradually learning. I have looked in the
online manual and can't find anything that helps.
I would really like to get this going as soon as possible to
evaluate...any and all help is GREATLY appreciated.
The above create table statement works fine for me on MySQL version 4.0.17.
Note: Currently CHECK clause does nothing in MySQL:
http://www.mysql.com/doc/en/CREATE_TABLE.html
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Query help
No. With the method you're using (storing the parent id with each record) you have to use a recursive series of queries to show the subtree levels. Take a look at this article--especially the second and third pages about the modified preorder tree traversal and nested sets. There are many other articles on this, but I found this one especially helpful starting out. It will let you do what you want, but this method has its drawbacks too. http://www.sitepoint.com/article/1105 _ Let the new MSN Premium Internet Software make the most of your high-speed experience. http://join.msn.com/?pgmarket=en-uspage=byoa/premST=1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select help
Hi, I want to select from the table sum of logins for each day. Would this help: mysql select date_format(your_date_column, %Y-%m-%d), count(*) - from your_table - group by date_format(your_date_column, %Y-%m-%d); Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select help
- Original Message - From: Mike Mapsnac [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 23, 2004 11:42 PM Subject: Select help Hello I want to select from the table sum of logins for each day. Here's one way to do it. SELECT SUBSTRING(last_login, 1, 10) AS day, login_count FROM table GROUP BY day ORDER BY day ASC; For example: Date Logins 2004-01-22 10 2004-01-23 12 Any ideas if such select is possible? +--+--+ | Field| Type | +--+--+ | login_count | int(4) | | last_login | datetime | Mikael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RegExp Help
you should be able to use STR_REPLACE. update 02093_xdir_links SET title = REPLACE(*,,title); hth jeff Bob Cohen [EMAIL PROTECTED]To: [EMAIL PROTECTED] ve.com cc: Subject: RegExp Help 01/21/2004 10:55 AM Please respond to bcohen Sorry for the newbie question. [Begin]$Groveling_non-programmer_string_of_excuses[End]. I imported a bunch of records into a table. One of the fields came through bracketed in double quotes, e.g., field data. I want remove the double quotes but not the data bracketed within. E.g., field data to field data. Here's my stab at the SQL: UPDATE 02093_xdir_links SET title * WHERE title REGEXP[]*[]; Will this work?. Is there a better way? Did I get it right? Normally I'd just experiment but this is a live database. Thanks in advance. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RegExp Help
you should be able to use STR_REPLACE. DOH. Sorry, there is NO STR_REPLACE its just REPLACE. jd update 02093_xdir_links SET title = REPLACE(*,,title); hth jeff Bob Cohen [EMAIL PROTECTED]To: [EMAIL PROTECTED] ve.com cc: Subject: RegExp Help 01/21/2004 10:55 AM Please respond to bcohen Sorry for the newbie question. [Begin]$Groveling_non-programmer_string_of_excuses[End]. I imported a bunch of records into a table. One of the fields came through bracketed in double quotes, e.g., field data. I want remove the double quotes but not the data bracketed within. E.g., field data to field data. Here's my stab at the SQL: UPDATE 02093_xdir_links SET title * WHERE title REGEXP[]*[]; Will this work?. Is there a better way? Did I get it right? Normally I'd just experiment but this is a live database. Thanks in advance. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RegExp Help
you should be able to use STR_REPLACE. DOH. Sorry, there is NO STR_REPLACE its just REPLACE. jd update 02093_xdir_links SET title = REPLACE(*,,title); Thank you very much for responding. Sorry to be dense but will this SQL find only those records with data in the TITLE field that are bracketed in double quotes and remove ONLY the quotes? E.g., Change the record from: Id Title Address City State Zip 1 Joe 1313 Mockingbird Lane TransylvaniaPA 02098 ^ To: Id Title Address City State Zip 1 Joe 1313 Mockingbird Lane TransylvaniaPA 02098 ^^^ To my untrained eye it looks like the REPLACE, as you wrote it above, searches the title field for anything e.g., *. And replaces it with nothing . Thanks. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RegExp Help
wow, one post, two mistakes. how right you are. sorry. update 02093_xdir_links SET title = REPLACE(\,,title); you may/may not need to escape the . hth Jeff Bob Cohen [EMAIL PROTECTED]To: [EMAIL PROTECTED], [EMAIL PROTECTED] ve.com cc: Subject: RE: RegExp Help 01/21/2004 01:47 PM Please respond to bcohen you should be able to use STR_REPLACE. DOH. Sorry, there is NO STR_REPLACE its just REPLACE. jd update 02093_xdir_links SET title = REPLACE(*,,title); Thank you very much for responding. Sorry to be dense but will this SQL find only those records with data in the TITLE field that are bracketed in double quotes and remove ONLY the quotes? E.g., Change the record from: IdTitle Address City State Zip 1 Joe 1313 Mockingbird Lane Transylvania PA 02098 ^ To: IdTitle Address City State Zip 1 Joe 1313 Mockingbird Lane Transylvania PA 02098 ^^^ To my untrained eye it looks like the REPLACE, as you wrote it above, searches the title field for anything e.g., *. And replaces it with nothing . Thanks. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Help
* sulewski [...] What I need is all records in table 1 that will not link to table 2 such that relid=rid and vid=46 Sounds like a job for LEFT JOIN...? Join to the rows you do NOT want with a left join, and put as a condition in the WHERE clause that a joined column IS NULL. Something like this: SELECT tab1.* FROM tab1 LEFT JOIN tab2 ON tab2.rid=tab1.id AND tab2.vid=46 WHERE tab2.rid IS NULL -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Help
Roger, Thank you for the feedback. But unfortunately this doesn't work. The problem is that rid will never be null. I'm trying to find the item in tab1 where a link cannot be created in tab2 where tab2.rid=tab1.id and tab2.vid=46 because there is no record in tab2. Not that the record may have null values. I did try what you said and it didn't work. But thanks, Joe On Friday, January 16, 2004, at 11:49 AM, Roger Baklund wrote: * sulewski [...] What I need is all records in table 1 that will not link to table 2 such that relid=rid and vid=46 Sounds like a job for LEFT JOIN...? Join to the rows you do NOT want with a left join, and put as a condition in the WHERE clause that a joined column IS NULL. Something like this: SELECT tab1.* FROM tab1 LEFT JOIN tab2 ON tab2.rid=tab1.id AND tab2.vid=46 WHERE tab2.rid IS NULL -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Help
Roger, In regards to my last e-mail what would be great is if I can get all the records in tab1 then subtract from there all the records that match the query tab1.id=tab2.rid and tab2.vid=46. The result would give me what I need but alas mysql doesn't support minus. Joe On Friday, January 16, 2004, at 11:49 AM, Roger Baklund wrote: * sulewski [...] What I need is all records in table 1 that will not link to table 2 such that relid=rid and vid=46 Sounds like a job for LEFT JOIN...? Join to the rows you do NOT want with a left join, and put as a condition in the WHERE clause that a joined column IS NULL. Something like this: SELECT tab1.* FROM tab1 LEFT JOIN tab2 ON tab2.rid=tab1.id AND tab2.vid=46 WHERE tab2.rid IS NULL -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Help
That is the whole point of a left join. It joins to a null record when the appropriate right record does not exist. sulewski wrote: Roger, Thank you for the feedback. But unfortunately this doesn't work. The problem is that rid will never be null. I'm trying to find the item in tab1 where a link cannot be created in tab2 where tab2.rid=tab1.id and tab2.vid=46 because there is no record in tab2. Not that the record may have null values. I did try what you said and it didn't work. But thanks, Joe On Friday, January 16, 2004, at 11:49 AM, Roger Baklund wrote: * sulewski [...] What I need is all records in table 1 that will not link to table 2 such that relid=rid and vid=46 Sounds like a job for LEFT JOIN...? Join to the rows you do NOT want with a left join, and put as a condition in the WHERE clause that a joined column IS NULL. Something like this: SELECT tab1.* FROM tab1 LEFT JOIN tab2 ON tab2.rid=tab1.id AND tab2.vid=46 WHERE tab2.rid IS NULL -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Help
Gerald, Your right. You and Roger hit it on the head. Stupid me miss read Roger's original post. Last night I was banging my head on the left and right joins but I didn't understand it until I read Gerald's last note. Plus I didn't realize you can put two conditions in the ON clause which is why I didn't get Roger's post. Thank you very much guys. You saved the day. Joe On Friday, January 16, 2004, at 12:31 PM, gerald_clark wrote: That is the whole point of a left join. It joins to a null record when the appropriate right record does not exist. sulewski wrote: Roger, Thank you for the feedback. But unfortunately this doesn't work. The problem is that rid will never be null. I'm trying to find the item in tab1 where a link cannot be created in tab2 where tab2.rid=tab1.id and tab2.vid=46 because there is no record in tab2. Not that the record may have null values. I did try what you said and it didn't work. But thanks, Joe On Friday, January 16, 2004, at 11:49 AM, Roger Baklund wrote: * sulewski [...] What I need is all records in table 1 that will not link to table 2 such that relid=rid and vid=46 Sounds like a job for LEFT JOIN...? Join to the rows you do NOT want with a left join, and put as a condition in the WHERE clause that a joined column IS NULL. Something like this: SELECT tab1.* FROM tab1 LEFT JOIN tab2 ON tab2.rid=tab1.id AND tab2.vid=46 WHERE tab2.rid IS NULL -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with syntax for mysqldump
I am really struggling with a mysqldump. I am trying to create a dump of a complete database called csi_db01 and I am trying to save the dump file to My Documents on the C drive. I am sure I am doing right, but could somebody email me the full syntax to use which comes after the mysql if you could send errors you are getting that would be a help for us One thing, you don't use mysqldump from the mysql prompt, you use it from the msyql directory on your C:/Drive --example C:\mysqlbin/mysqldump -u yourusername -pyoupassword --alldatabases outfile.sql specifying the password in the string is not the best way to go hth Jeff If I can't get the dump file to work, how do I get the database on my local machine up to the remote server. I am using version 4.0.15. Are there any bugs? Thanks Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with syntax for mysqldump
On Tue, 13 Jan 2004 14:07:38 +, Matthew Stuart [EMAIL PROTECTED] wrote: I am really struggling with a mysqldump. I am trying to create a dump of a complete database called csi_db01 and I am trying to save the dump file to My Documents on the C drive. I am sure I am doing right, but could somebody email me the full syntax to use which comes after the mysql mysqldump -udavidrayner -pdavidrayner eeetic eeetic.sql mysqldump -udavidrayner -pdavidrayner -A all.sql zzapper (vim cygwin zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with syntax for mysqldump
Matthew Stuart wrote: I am really struggling with a mysqldump. I am trying to create a dump of a complete database called csi_db01 and I am trying to save the dump file to My Documents on the C drive. I am sure I am doing right, but could somebody email me the full syntax to use which comes after the mysql Run mysqldump from a shell (DOS) prompt, not from the mysql client. Something like: prompt% mysqldump -u root -prootpassword csi_db01 :: will dump to standard out; add the path to where you want to save the dump, like: prompt% mysqldump -uroot -p csi_db01 /path/to/dumpfile.sql I'm not sure how a path with spaces -- My Documents -- is going to work, but experiment (or pick another location). :-) HTH! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with syntax for mysqldump
mysqldump is run at the system command-line, not within the mysql client environment. This is most likely to be my problem then. I assumed that what is called the system command line to be the mysql client environment. The tutorial book that I have been going through instructs me to issue this command in the start menus run dialogue box: C:\Windows\Desktop cd C:\mysql\bin and from there I have issued all commands in the black window. Is this not the command line, and if not, what is? Sorry for my ignorance, I am still a beginner. Mat -- Matthew Stuart 11 Yew Tree Close Middleton Cheney Banbury Oxon OX17 2SU 01295 713813 07803 207734 -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with syntax for mysqldump
On Tue, 13 Jan 2004 14:07:38 +, Matthew Stuart [EMAIL PROTECTED] wrote: Similarly. SELECT intDEVID,txtDEVPOSTCODE INTO OUTFILE c:/aaa/dump.sql from ytbl_development; (dump.sql file must NOT already exist) zzapper (vim cygwin zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with syntax for mysqldump
Matthew Stuart wrote: mysqldump is run at the system command-line, not within the mysql client environment. This is most likely to be my problem then. I assumed that what is called the system command line to be the mysql client environment. The tutorial book that I have been going through instructs me to issue this command in the start menus run dialogue box: C:\Windows\Desktop cd C:\mysql\bin and from there I have issued all commands in the black window. Is this not the command line, and if not, what is? That is the command line. Open a new Command Prompt in windows (something like Start-Programs-Accessories-Command Prompt) Then type cd c:\mysql assuming that is where you installed MySQL then type : bin\mysqldump -uusername -ppassword dbname csi_db01 c:\csi_db01.sql This will create a file called csi_db01.sql in the base of your C: drive that contains all the SQL needed to recreate your table. Note that the c:\mysql isn't strictly needed - and it would work just as well to type c:\mysq\bin\mysqldump in any folder in your system. I'm sure this functionality definately works, so if you can't make it work then post back to the list and someone will realise what mistake you're making. Andrew [Also - most individuals choose not to disclose address and telephone numbers on public mailing lists because they are so widely distributed - espically popular ones like this. I'd suggest removing that information from your signature. But it's just a suggestion ;) ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimization help
I think... you don't have an index on the Incident field itself, just
on (Date, Incident, Type, Task) which means that it concatenates those
fields and orders the result - thus this may be virtually useless if
you're looking for a specific incident within a large date range. Since
your query has a specific incident number, indexing that field would
probably help a lot.
Do a SHOW INDEXES FROM DeltaPAF;
To see the indexes that are actually there.
or
EXPLAIN SELECT Date FROM DeltaPAF WHERE Date=2003-12-11 AND
Date=2004-01-11 AND Incident=98996144;
to see which indexes MySQL is really using.
For example, in the table below, there are really only two indexes, the
one primary key index and the second name index. The Seq_in_index
column shows the fields that are included in the index but the ones that
aren't listed first will be much harder to find. Like a telephone
directory, which is ordered by lastname, firstname - both fields are
indexed but they are in the same index, so finding a specific firstname
still means a full table scan.
Good luck!
mysql describe test1;
++-+--+-++---+
| Field | Type| Null | Key | Default| Extra |
++-+--+-++---+
| name | varchar(20) | YES | MUL | NULL | |
| mydate | date| | PRI | -00-00 | |
| number | int(10) | | PRI | 0 | |
++-+--+-++---+
3 rows in set (0.00 sec)
mysql show indexes from test1;
+---++--+--+-+---+-+--++--++-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
| Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---++--+--+-+---+-+--++--++-+
| test1 | 0 | PRIMARY |1 | mydate | A
|NULL | NULL | NULL | | BTREE | |
| test1 | 0 | PRIMARY |2 | number | A
| 0 | NULL | NULL | | BTREE | |
| test1 | 1 | name |1 | name| A
|NULL | NULL | NULL | YES | BTREE | |
| test1 | 1 | name |2 | mydate | A
|NULL | NULL | NULL | | BTREE | |
| test1 | 1 | name |3 | number | A
|NULL | NULL | NULL | | BTREE | |
+---++--+--+-+---+-+--++--++-+
5 rows in set (0.15 sec)
Mike Schienle wrote:
Hi all -
I have a speed problem that I don't understand. I've been pretty
active with DB's for a few years, but I'm no expert, so let me know if
I'm missing the obvious. I have Paul DuBois' MySQL book (New Riders
edition) and Descartes and Bunce's Programming DBI book on my desk, so
feel free to reference something there if that will help.
Here's the table I'm working from and it's structure:
CREATE TABLE DeltaPAF (
Date DATE NOT NULL,
Type VARCHAR(4) NOT NULL,
Incident INT UNSIGNED NOT NULL,
Mgr VARCHAR(4) NOT NULL,
Site VARCHAR(40) NOT NULL,
Task ENUM('Proposed', 'Approved', 'Completed', 'Invoiced',
'Expired', 'Rejected', 'Cancelled') NOT NULL,
Webpage MEDIUMTEXT NOT NULL,
BudgetDECIMAL(12, 2) DEFAULT 0.00,
PRIMARY KEY (Date, Incident, Type, Task),
INDEX (Type, Mgr, Site)
);
I have about 125,000 records in the table and it's running on an older
400 MHz MacOS X 10.2.8 system. The MySQL version is 3.23.52.
The following query comes back with 210 records in about 0.6 seconds.
mysql SELECT Date FROM DeltaPAF WHERE Date=2003-12-11
- AND Date=2004-01-11 AND Incident=98996144;
However, this query comes back with 210 records in a little over 2
minutes.
mysql SELECT Budget FROM DeltaPAF WHERE Date=2003-12-11
- AND Date=2004-01-11 AND Incident=98996144;
Can someone clue me in how I might get the SELECT Budget query to
return in a similar time to the SELECT Date query? I tried adding an
index for Budget, knowing it shouldn't help, and it didn't. FWIW, the
Webpage fields average about 5K characters, but can be as much as 40K.
Mike Schienle, Custom Visuals
http://www.customvisuals.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimization help
On Jan 12, 2004, at 08:09 AM, Douglas Sims wrote: I think... you don't have an index on the Incident field itself, just on (Date, Incident, Type, Task) which means that it concatenates those fields and orders the result - thus this may be virtually useless if you're looking for a specific incident within a large date range. Since your query has a specific incident number, indexing that field would probably help a lot. Thanks for the help, Douglas. That was the ticket. We're back under a second for queries now. Mike Schienle, Custom Visuals http://www.customvisuals.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a query
* Soheil Shaghaghi Hello everyone, I need help with MySQL coding in php please if anyone can help. I can try. :) I have 3 tables: -users, where the user info is stored. -awards: contains the list of all the awards for each user -award_types: contains different types of award The tables are at the bottom of the page. What I need to do is look at these tables when a user id is being viewed and display the awards image that the user has won. A user can have multiple awards. Ok... and what is the problem? The SQL could be something like this: SELECT award_type, award_image FROM award_types,awards WHERE award_types.id = awards.award_id AND awards.chosen = 'enabled' AND awards.user_id = $userid (Not sure about the chosen = 'enabled', just looked like that was what you wanted from your example data.) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a query..
Tibby wrote: ..and I want to get this with a single query: +-++--+ | key | desc| value | +-++--+ | 2 | book| 7 | | 6 | pen | 7 | +-++--+ I need to get only one row from col. DESC, the one with the highest VALUE. With one query... select `desc`, max(value) from mytable group by `desc` -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a query..
Hi, I have already tried the 'rtfm', but it just didn't help. But it's right there :) 3.5.2 The Row Holding the Maximum of a Certain Column ..and I want to get this with a single query: +-++--+ | key | desc| value | +-++--+ | 2 | book| 7 | | 6 | pen | 7 | +-++--+ select key, desc, value from your_table t1 where value = (select max(value) from your_table where desc = t1.desc) Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a query..
* Aleksandar Bradaric select key, desc, value from your_table t1 where value = (select max(value) from your_table where desc = t1.desc) Anyway, when i execute this query, i get an error near 'select max(value)'... :( It's because the subselects are supported from version 4.1. Yes. If you use older MySQL version then it's not possible to do it with a single query :( Yes, it is. :) URL: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html See the MAX-CONCAT trick. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help 2
yes, just a typing mistakes~~ anyway, both of them work fine. gerald_clark [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] DId I type that? I meant 1,4 I left it as substring because that was what he tried. ¿n©_ ¡E¢X ¡¸ wrote: yes, it should work, but should not be 1,1 ? or simply: UPDATE RemoteStation SET company=LEFT(ID, 4); ? gerald_clark [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] How about: update RemoteStation set company=substring(ID,1,1); Jeff McKeon wrote: Damn fat fingers and MS Outlook. I sent the Query Help message before I was finishes typing. Sorry... I have two tables, customer table and a company table The customer table has an ID field that is 8 characters long. The first 4 characters are the company code. We just added a company table that has an id field that contains that companies id code. We also added a field to the customer table that will hold the company ID that the customer belongs to. So. Customer table ID name company 12347771 joe null 12347772 mary null 43210001 bob null Company Table ID name 1234 Acme 4321 Acme_Europe What I now need to do is create an update statement that will match the customer to the company by substring(Customer.ID,1,4) to Company.ID I tried: update RemoteStation set Company_ID=Company.ID where substring(Company_ID,1,4) like Company.ID; But it didn't work. Any suggestions? Thanks for the help, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help 2
How about: update RemoteStation set company=substring(ID,1,1); Jeff McKeon wrote: Damn fat fingers and MS Outlook. I sent the Query Help message before I was finishes typing. Sorry... I have two tables, customer table and a company table The customer table has an ID field that is 8 characters long. The first 4 characters are the company code. We just added a company table that has an id field that contains that companies id code. We also added a field to the customer table that will hold the company ID that the customer belongs to. So. Customer table ID namecompany 12347771joe null 12347772marynull 43210001bob null Company Table ID name 1234Acme 4321Acme_Europe What I now need to do is create an update statement that will match the customer to the company by substring(Customer.ID,1,4) to Company.ID I tried: update RemoteStation set Company_ID=Company.ID where substring(Company_ID,1,4) like Company.ID; But it didn't work. Any suggestions? Thanks for the help, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help 2
yes, it should work, but should not be 1,1 ? or simply: UPDATE RemoteStation SET company=LEFT(ID, 4); ? gerald_clark [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] How about: update RemoteStation set company=substring(ID,1,1); Jeff McKeon wrote: Damn fat fingers and MS Outlook. I sent the Query Help message before I was finishes typing. Sorry... I have two tables, customer table and a company table The customer table has an ID field that is 8 characters long. The first 4 characters are the company code. We just added a company table that has an id field that contains that companies id code. We also added a field to the customer table that will hold the company ID that the customer belongs to. So. Customer table ID name company 12347771 joe null 12347772 mary null 43210001 bob null Company Table ID name 1234 Acme 4321 Acme_Europe What I now need to do is create an update statement that will match the customer to the company by substring(Customer.ID,1,4) to Company.ID I tried: update RemoteStation set Company_ID=Company.ID where substring(Company_ID,1,4) like Company.ID; But it didn't work. Any suggestions? Thanks for the help, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help 2
DId I type that? I meant 1,4 I left it as substring because that was what he tried. ¿n©_ ¡E¢X ¡¸ wrote: yes, it should work, but should not be 1,1 ? or simply: UPDATE RemoteStation SET company=LEFT(ID, 4); ? gerald_clark [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] How about: update RemoteStation set company=substring(ID,1,1); Jeff McKeon wrote: Damn fat fingers and MS Outlook. I sent the Query Help message before I was finishes typing. Sorry... I have two tables, customer table and a company table The customer table has an ID field that is 8 characters long. The first 4 characters are the company code. We just added a company table that has an id field that contains that companies id code. We also added a field to the customer table that will hold the company ID that the customer belongs to. So. Customer table ID name company 12347771 joe null 12347772 mary null 43210001 bob null Company Table ID name 1234 Acme 4321 Acme_Europe What I now need to do is create an update statement that will match the customer to the company by substring(Customer.ID,1,4) to Company.ID I tried: update RemoteStation set Company_ID=Company.ID where substring(Company_ID,1,4) like Company.ID; But it didn't work. Any suggestions? Thanks for the help, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RESOLVED - Help with JOIN and Record Display
I managed to figure out where I was going wrong (or at least I think I have.
I was playing around with the syntax and decided to add a BR after the
$Row[notes] and it displayed the records line by line. I don't know if this
was the best way to do it but it worked.
Thanks
Jess
-Original Message-
From: Hunter, Jess [SMTP:[EMAIL PROTECTED]
Sent: Sunday, December 14, 2003 11:34 AM
To: [EMAIL PROTECTED]
Subject: Help with JOIN and Record Display
This being the first time I have tried to do a JOIN statement (and still
not
yet fully understanding it). If someone could take a look at the below
code
and see what I may be doing wrong.
I have the actual code working and it displays the information from
$TableName2.notes, however in this table there are three records that are
being displayed (which it should) however it displays them all together.
i.e.
DISPLAYED
This is the first record This is the second record This is the thirdrecord
/DISPLAYED
What I am wanting it to do is:
DISPLAYED
This is the first record
This is the second record
This is the thirdrecord
/DISPLAYED
Here is the code I am using
SNIPPET
$Link = mysql_connect($Host, $User, $Password);
$Query=SELECT * from $TableName1 LEFT JOIN $TableName2 ON ($TableName1.id
=
$TableName2.id) WHERE $TableName1.id=1;
$Result= mysql_db_query ($DBName, $Query, $Link);
while ($Row = mysql_fetch_array ($Result)){
print ($Row[notes]);
}
/SNIPPET
I have even tried putting a line break (\n) in at the end of the
$Row[notes]
to see if that would do anything which it did not.
Thanks in advance for any possible assistance
Jess
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.550 / Virus Database: 342 - Release Date: 12/9/03
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.550 / Virus Database: 342 - Release Date: 12/9/03
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.550 / Virus Database: 342 - Release Date: 12/9/03
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: plz help a newbie
Here is a link to the MySQL manual. Lots of good info here. http://www.mysql.com/documentation/mysql/bychapter/index.htm l The max size of a varchar is 255 characters. You will want to use one of the text data types if you are going to store long articles. I suggest mediumtext. You can find the exact maximum sizes in the manual under the data types section. A timestamp column gets automatically updated to now on inserts and updates unless you explictly set it to something. Datetime fields just store a datetime (you update them to whatever you want). I don't understand what you meant by this. Can you give an example? I have these type of associations supported by mysql 1:1 1:1 (Non-Identyfying) 1:1 (Descendent Obj.) What are the differences among them? Especially the last one? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: delete help
Are you aware that you are using a nested sql query which isn't available until version 4.1? Try splitting the delete into multiple steps 1) populate a temp table with the id's you want to delete then 2) use the temp table data to delete the rows. - Original Message - From: tech [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 08, 2003 3:16 AM Subject: delete help I hope someone can help, I am a newbe as sql and need a some help I can by using this command at mysql prompt |select ACCTSESSIONID from radiuslog where ACCTSESSIONTIME='0';| get a list that I then need to delete but when I used the following I keep get errors delete from radiuslog where ACCTSESSIONID = any (select ACCTSESSIONID from radiuslog where ACCTSESSIONTIME='0'); I can not delete the record by just deleting the record with the 0 ACCTSESSIONTIME as this would only delete the Stop and not the start record with the same ACCTSESSIONID from this radiuslog http://netwinsite.com/dbabble/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: delete help
Thank you for taking the time to reply I am using version 4.1 as can be seen from the out put below mysql Ver 13.5 Distrib 4.1.0-alpha, for portbld-freebsd5.1 (i386) Also I am unsure of the syntax for what you suggest Are you aware that you are using a nested sql query which isn't available until version 4.1? Try splitting the delete into multiple steps 1) populate a temp table with the id's you want to delete then 2) use the temp table data to delete the rows. - Original Message - From: tech [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 08, 2003 3:16 AM Subject: delete help I hope someone can help, I am a newbe as sql and need a some help I can by using this command at mysql prompt |select ACCTSESSIONID from radiuslog where ACCTSESSIONTIME='0';| get a list that I then need to delete but when I used the following I keep get errors delete from radiuslog where ACCTSESSIONID = any (select ACCTSESSIONID from radiuslog where ACCTSESSIONTIME='0'); I can not delete the record by just deleting the record with the 0 ACCTSESSIONTIME as this would only delete the Stop and not the start record with the same ACCTSESSIONID from this radiuslog http://netwinsite.com/dbabble/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] http://netwinsite.com/dbabble/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with query. Please!
Try: SELECT C.company_id, C.company_name FROM companies C LEFT JOIN company_group_intersect CG ON (C.company_id=CG.company_id AND CG.group_id='1') WHERE C.status='1' AND CG.company_id IS NULL -- Diana Soares On Fri, 2003-12-05 at 15:08, Robert Hughes wrote: I have the following 3 tables: table 1: companies fields 1: company_id, company name, status Sample data: 1 , company 1 , 0 2 , company 2 , 1 3 , company 3 , 1 4 , company 4 , 0 5 , company 5 , 1 table 2: groups fields 2: group_id, group_name Sample data: 1 , Group 1 2 , Group 2 3 , Group 3 4 , Group 4 5 , Group 5 table 3: company_group_intersect table 3: group_id, company_id Sample data: 1 , 2 1 , 3 2 , 2 2 , 3 2 , 5 As you can see, Group 1 consists of Companies 2 and 3. And Group 2 consists of Companies 2, 3 and 5. The query I'm having trouble with is trying to get a result set of (status=1) companies that AREN'T in a particular group. group_id is my only available variable passed in from the script. I need a results set that has: * companies.company_name, companies.company_id where companies.status='1' and companies.company_id is not in intersect table next to the variable group_id. If I pass in group_id 1 the result set should be: 5 , company 5 since it's the only status='1' company that's not in group 1 Thanks in advance for your help. Robert --- At Executive Performance Group we take security very seriously. All emails and attachments are scanned for viruses prior to sending. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.544 / Virus Database: 338 - Release Date: 11/25/2003 -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help with query. Please!
That worked perfectly!!! Thanks so much :-) -Original Message- From: Diana Soares [mailto:[EMAIL PROTECTED] Sent: Friday, December 05, 2003 10:31 AM To: Robert Hughes Cc: [EMAIL PROTECTED] Subject: Re: Need help with query. Please! Try: SELECT C.company_id, C.company_name FROM companies C LEFT JOIN company_group_intersect CG ON (C.company_id=CG.company_id AND CG.group_id='1') WHERE C.status='1' AND CG.company_id IS NULL -- Diana Soares On Fri, 2003-12-05 at 15:08, Robert Hughes wrote: I have the following 3 tables: table 1: companies fields 1: company_id, company name, status Sample data: 1 , company 1 , 0 2 , company 2 , 1 3 , company 3 , 1 4 , company 4 , 0 5 , company 5 , 1 table 2: groups fields 2: group_id, group_name Sample data: 1 , Group 1 2 , Group 2 3 , Group 3 4 , Group 4 5 , Group 5 table 3: company_group_intersect table 3: group_id, company_id Sample data: 1 , 2 1 , 3 2 , 2 2 , 3 2 , 5 As you can see, Group 1 consists of Companies 2 and 3. And Group 2 consists of Companies 2, 3 and 5. The query I'm having trouble with is trying to get a result set of (status=1) companies that AREN'T in a particular group. group_id is my only available variable passed in from the script. I need a results set that has: * companies.company_name, companies.company_id where companies.status='1' and companies.company_id is not in intersect table next to the variable group_id. If I pass in group_id 1 the result set should be: 5 , company 5 since it's the only status='1' company that's not in group 1 Thanks in advance for your help. Robert --- At Executive Performance Group we take security very seriously. All emails and attachments are scanned for viruses prior to sending. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.544 / Virus Database: 338 - Release Date: 11/25/2003 -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.544 / Virus Database: 338 - Release Date: 11/25/2003 --- At Executive Performance Group we take security very seriously. All emails and attachments are scanned for viruses prior to sending. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.544 / Virus Database: 338 - Release Date: 11/25/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
On 2 Dec 2003 at 7:20, Greg Jones wrote: snip select l.ltsysid,l.lientraknum, c.name from lientrak as l, customer as c where l.custsysid=c.custsysid and l.ltsysid in (select l2.ltsysid from lientrak as l2 where l2.lientraknum like '2003-%') snip Hi greg Yopur problem is that you are using a subquery - MySQL 4.0.1.5 does not support subqueries - you will need 4.1 for that... A possible workaround (based on your query above - so please tolerate any mistakes :) ) : select l.ltsysid,l.lientraknum, c.name from lientrak as l, customer as c where l.custsysid=c.custsysid and l.lientraknum like '2003-%' Actually, having written the above workaround, I am puzzled as to why you would need the subquery at all :)...unless of course, above workaround is completely wrong, and I have to eat humble pie again. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown)
Re: Query Help
Access. However, when I run it against MySQL I get an error. select l.ltsysid,l.lientraknum, c.name from lientrak as l, customer as c where l.custsysid=c.custsysid and l.ltsysid in (select l2.ltsysid from lientrak as l2 where l2.lientraknum like '2003-%') Sub queries are not going to be available until version 4.1. You'll need to re-write the above query using an outer (?) join. I'm not sure what the exact syntax should be and I'm sure someone will pipe up with that info. Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
- Original Message - From: Chris Boget [EMAIL PROTECTED] To: Greg Jones [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 8:32 AM Subject: Re: Query Help Access. However, when I run it against MySQL I get an error. select l.ltsysid,l.lientraknum, c.name from lientrak as l, customer as c where l.custsysid=c.custsysid and l.ltsysid in (select l2.ltsysid from lientrak as l2 where l2.lientraknum like '2003-%') Sub queries are not going to be available until version 4.1. You'll need to re-write the above query using an outer (?) join. I'm not sure what the exact syntax should be and I'm sure someone will pipe up with that info. First impression: it looks like it might be messy if ltsysid isn't unique (i.e., isn't a key). Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Help Upgrading From 4.x to 4.x
On Wed, 19 Nov 2003, Mark Marshall wrote: Hi, everyone. I have a 4.0.4 beta install of Mysql on Red Hat 7.3. I want to upgrade it to 4.0.16, and keep all the data intact. Do I just dump the databases (just in case), stop the server, then ./configure, make, make install over top of the old server and start it up again and see what happens? exactly. The dump is a good idea. Make sure that you compile with the same options to configure as your 4.0.4 build (everything should go to the same directory as it was before). This used to be a problem with SuSE distributions when installing over an old prm installation, because they used to have a different directory layout. I can't tell you how RedHat did this. Thomas Spahni Thanks, Mark As of November 1st, 2003, Brandywine Senior Care's Corporate Office new contact information is: Brandywine Senior Care, Inc. 525 Fellowship Road Suite 360 Mt. Laurel, NJ 08054 (856) 813-2000 Phone (856) 813-2020 Fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Help Upgrading From 4.x to 4.x
You could either use mysqldump or just copy the data directory to a safe place. Also, if you do not need any special build flags, you should use the official MySQL binaries, either the RPM or tarball. They optimized the binaries. -will - Original Message - From: Thomas Spahni [EMAIL PROTECTED] To: Mark Marshall [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, November 19, 2003 10:18 AM Subject: Re: Need Help Upgrading From 4.x to 4.x On Wed, 19 Nov 2003, Mark Marshall wrote: Hi, everyone. I have a 4.0.4 beta install of Mysql on Red Hat 7.3. I want to upgrade it to 4.0.16, and keep all the data intact. Do I just dump the databases (just in case), stop the server, then ./configure, make, make install over top of the old server and start it up again and see what happens? exactly. The dump is a good idea. Make sure that you compile with the same options to configure as your 4.0.4 build (everything should go to the same directory as it was before). This used to be a problem with SuSE distributions when installing over an old prm installation, because they used to have a different directory layout. I can't tell you how RedHat did this. Thomas Spahni Thanks, Mark As of November 1st, 2003, Brandywine Senior Care's Corporate Office new contact information is: Brandywine Senior Care, Inc. 525 Fellowship Road Suite 360 Mt. Laurel, NJ 08054 (856) 813-2000 Phone (856) 813-2020 Fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Help Upgrading From 4.x to 4.x
What if one is using rpm - should just use the --upgrade option rpm -U MySQL-server-4.x Is this ok to do? Thanks Aman Raheja AGF Technologies http://www.agftech.com [EMAIL PROTECTED] wrote: On Wed, 19 Nov 2003, Mark Marshall wrote: Hi, everyone. I have a 4.0.4 beta install of Mysql on Red Hat 7.3. I want to upgrade it to 4.0.16, and keep all the data intact. Do I just dump the databases (just in case), stop the server, then ./configure, make, make install over top of the old server and start it up again and see what happens? exactly. The dump is a good idea. Make sure that you compile with the same options to configure as your 4.0.4 build (everything should go to the same directory as it was before). This used to be a problem with SuSE distributions when installing over an old prm installation, because they used to have a different directory layout. I can't tell you how RedHat did this. Thomas Spahni Thanks, Mark As of November 1st, 2003, Brandywine Senior Care's Corporate Office new contact information is: Brandywine Senior Care, Inc. 525 Fellowship Road Suite 360 Mt. Laurel, NJ 08054 (856) 813-2000 Phone (856) 813-2020 Fax -- Your favorite stores, helpful shopping tools and great gift ideas. Experience the convenience of buying online with [EMAIL PROTECTED] http://shopnow.netscape.com/
RE: Needing help about PHP+mysql on RedHat 9.
You could try http://us2.php.net/ , http://us3.php.net/ , or http://us4.php.net/ . This isn't a PHP mailing list, but I'll answer your question briefly. The php.ini setting register_globals is set to 'off', so the $PHP_SELF variable isn't getting created. Either change $PHP_SELF to $_SERVER['PHP_SELF'] (recommended) or turn register_globals on. http://us3.php.net/manual/en/reserved.variables.php Chris -Original Message- From: Mario Miyojim [mailto:[EMAIL PROTECTED] Sent: Monday, November 17, 2003 10:18 PM To: [EMAIL PROTECTED] Subject: Needing help about PHP+mysql on RedHat 9. I am trying to revive an existing LAMP system. I recently installed RedHat9. I had several LAMP applications working under RedHat 7.1, but now they don't function properly. For instance, I have many scripts that use the FORM ACTION=?php print($PHP_SELF) METHOD=post construct, but they are not working as they should. I tried to get help from the PHP home site, but www.php.net has been unreacheable to me for several days. I am considering a desperate attempt to go back to RedHat 7.1 because that configuration supported my PHP scripts, but now I can't even read the updated PHP manual. Please, if anyone knows how to circumvent this problem, help me! Thanks __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Needing help about PHP+mysql on RedHat 9.
Thank you. It was the king of advice that I needed, because I was unaware of the existence of this parameter. I was struggling with the lack of mysql support when I tried to use the new RedHat 9. When I finally solved that problem, I became confused with the inability to transmit variable values through the POST method, that is why I asked for help. Now I can help others with the same dilemma. Thanks, indeed. p --- John Nichel [EMAIL PROTECTED] wrote: Mario Miyojim wrote: I am trying to revive an existing LAMP system. I recently installed RedHat9. I had several LAMP applications working under RedHat 7.1, but now they don't function properly. Chances are, register_globals was on with your old installation, and now it is off by default in php. Either turn it on in the php.ini (not-recommended), and restart your webserver, or rewrite your code to work with globals off (recommended). Things like $PHP_SELF would now be, $_SERVER['PHP_SELF']. Look here http://us3.php.net/register_globals Also, you'll get more help with an item such as this from a php mailing list. -- By-Tor.com It's all about the Rush http://www.by-tor.com __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Needing help about PHP+mysql on RedHat 9.
Thank you. Now I read tutorials at the new PHP web site, so my old scripts are refurbished and are functional. I initialize every variable like this: $var1 = $_POST['var1']; for example, and values are correctly passed. Mario p --- Chris [EMAIL PROTECTED] wrote: You could try http://us2.php.net/ , http://us3.php.net/ , or http://us4.php.net/ . This isn't a PHP mailing list, but I'll answer your question briefly. __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Needing help for JDBC issue
Can you post what you are trying to do? -Original Message- From: Prasad Budim Ram [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 2:17 AM To: [EMAIL PROTECTED] Subject: Needing help for JDBC issue Hi, I'm getting the following error while connecting to a MySQL databases using JDBC. Any clues?? java.net.SocketException: errno: 48, error: Address already in use Thanks, Ram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Needing help for JDBC issue
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Prasad Budim Ram wrote: Hi, I'm getting the following error while connecting to a MySQL databases using JDBC. Any clues?? java.net.SocketException: errno: 48, error: Address already in use Thanks, Ram You don't say what OS you're running on (Netware???). This is most likely due to you running out of originating ports to make a TCP/IP connection from. Are you creating a large number of connections when this happens (or is any other process on your system doing so?) -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 557 2388 www.mysql.com Are you MySQL Certified? http://www.mysql.com/certification/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQE/ujpItvXNTca6JD8RAqRLAKCbcoseLiNd27xbpkAtAiW+rgCB3wCfTWDv NUmvYZHVTQvB+8Mv5lGRYqQ= =MW7L -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help comparing MySQL to MS SQL Server
KEVIN ZEMBOWER wrote: The organization hired an outside consultant to evaluate which SQL engine to go with. This is what he sent to us: ... SQL Server 2000 is a complete Relational Database Management System (RDBMS) that also includes integrated analysis functionality for OLAP and data mining. SQL Server 2000 meets the data and analysis storage requirements of the largest data processing systems and commercial Web sites, yet at the same time can provide easy-to-use data storage services to an individual or small business. I don't know whether the consultant wrote this himself, or if it came from somewhere. Answer: It came from somewhere. The above paragraph is a word-for-word quote from this Microsoft document: http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/deploy/upgrdmigrate/mysql.asp Regards, PeterG -- Peter Gulutzan, Software Architect MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help DB Error: unknown error
Hi Thai, I think you're just joining wy too many tables! LOL Matt - Original Message - From: Thai Thanh Ha Sent: Sunday, November 16, 2003 8:10 PM Subject: Please help DB Error: unknown error Hi all, I have a problem with my query on mySQL 4.0. DB Error: unknown error I don't know what is the problem. Please help me!!! Thanks in advance. Regards, Thai SELECT t.UserID, t.Country, t.Zip FROM tblUser t, tblUserProfile t1,tblUserProfile t2,tblUserProfile t3,tblUserProfile t4,tblUserProfile t5,tblUserProfile t6,tblUserProfile t7,tblUserProfile t8,tblUserProfile t9,tblUserProfile t10,tblUserProfile t11,tblUserProfile t12,tblUserProfile t13,tblUserProfile t14,tblUserProfile t15,tblUserProfile t16,tblUserProfile t17,tblUserProfile t18,tblUserProfile t19,tblUserProfile t20,tblUserProfile t21,tblUserProfile t22,tblUserProfile t23,tblUserProfile t24,tblUserProfile t25,tblUserProfile t26,tblUserProfile t27,tblUserProfile t28,tblUserProfile t29,tblUserProfile t30,tblUserProfile t31,tblUserProfile t32,tblUserProfile t33,tblUserProfile t34,tblUserProfile t35,tblUserProfile t36,tblUserProfile t37,tblUserProfile t38,tblUserProfile t39,tblUserProfile t40,tblUserProfile t41,tblUserProfile t42,tblUserProfile t43,tblUserProfile t44,tblUserProfile t45,tblUserProfile t46,tblUserProfile t47,tblUserProfile t48,tblUserProfile t49,tblUserProfile t50,tblUserProfile t51,tblUserProfile t52,tblUserProfile t53,tblUserProfile t54,tblUserProfile t55,tblUserProfile t56,tblUserProfile t57,tblUserProfile t58,tblUserProfile t59,tblUserProfile t60,tblUserProfile t61,tblUserProfile t62,tblUserProfile t63,tblUserProfile t64,tblUserProfile t65,tblUserProfile t66,tblUserProfile t67,tblUserProfile t68,tblUserProfile t69,tblUserProfile t70,tblUserProfile t71,tblUserProfile t72,tblUserProfile t73,tblUserProfile t74,tblUserProfile t75,tblUserProfile t76,tblUserProfile t77,tblUserProfile t78,tblUserProfile t79,tblUserProfile t80,tblUserProfile t81,tblUserProfile t82,tblUserProfile t83,tblUserProfile t84,tblUserProfile t85,tblUserProfile t86,tblUserProfile t87,tblUserProfile t88,tblUserProfile t89,tblUserProfile t90,tblUserProfile t91,tblUserProfile t92,tblUserProfile t93,tblUserProfile t94,tblUserProfile t95,tblUserProfile t96,tblUserProfile t97,tblUserProfile t98,tblUserProfile t99,tblUserProfile t100,tblUserProfile t101,tblUserProfile t102,tblUserProfile t103,tblUserProfile t104,tblUserProfile t105,tblUserProfile t106,tblUserProfile t107,tblUserProfile t108,tblUserProfile t109,tblUserProfile t110,tblUserProfile t111,tblUserProfile t112,tblUserProfile t113,tblUserProfile t114,tblUserProfile t115,tblUserProfile t116,tblUserProfile t117,tblUserProfile t118,tblUserProfile t119,tblUserProfile t120,tblUserProfile t121,tblUserProfile t122,tblUserProfile t123,tblUserProfile t124,tblUserProfile t125,tblUserProfile t126,tblUserProfile t127,tblUserProfile t128,tblUserProfile t129,tblUserProfile t130,tblUserProfile t131,tblUserProfile t132,tblUserProfile t133,tblUserProfile t134,tblUserProfile t135,tblUserProfile t136,tblUserProfile t137,tblUserProfile t138,tblUserProfile t139,tblUserProfile t140,tblUserProfile t141,tblUserProfile t142,tblUserProfile t143,tblUserProfile t144,tblUserProfile t145,tblUserProfile t146,tblUserProfile t147,tblUserProfile t148,tblUserProfile t149,tblUserProfile t150,tblUserProfile t151,tblUserProfile t152,tblUserProfile t153,tblUserProfile t154,tblUserProfile t155,tblUserProfile t156,tblUserProfile t157,tblUserProfile t158,tblUserProfile t159,tblUserProfile t160,tblUserProfile t161, tblMatchProfile mp WHERE t.ShowProfile = 1 AND t.LoginHandle 'administrator' AND t.AccountType 2 AND t.AccountType 3 AND t.Gender = 1 AND (Year(Now()) - Year(t.BirthDate)) BETWEEN 18 AND 80 AND t.UserID = t1.UserID AND t1.FieldID = 78 AND t1.FieldValue = 8 AND t1.UserID=t2.UserID AND t2.FieldID = 78 AND t2.FieldValue = 6 AND t1.UserID=t3.UserID AND t3.FieldID = 78 AND t3.FieldValue = 5 AND t1.UserID=t4.UserID AND t4.FieldID = 78 AND t4.FieldValue = 3 AND t1.UserID=t5.UserID AND t5.FieldID = 78 AND t5.FieldValue = 4 AND t1.UserID=t6.UserID AND t6.FieldID = 78 AND t6.FieldValue = 7 AND t1.UserID=t7.UserID AND t7.FieldID = 78 AND t7.FieldValue = 1 AND t1.UserID=t8.UserID AND t8.FieldID = 78 AND t8.FieldValue = 9 AND t1.UserID=t9.UserID AND t9.FieldID = 78 AND t9.FieldValue = 2 AND t1.UserID=t10.UserID AND t10.FieldID = 79 AND t10.FieldValue = 3 AND t1.UserID=t11.UserID AND t11.FieldID = 79 AND t11.FieldValue = 1 AND t1.UserID=t12.UserID AND t12.FieldID = 79 AND t12.FieldValue = 2 AND t1.UserID=t13.UserID AND t13.FieldID = 79 AND t13.FieldValue = 5 AND t1.UserID=t14.UserID AND t14.FieldID = 79 AND t14.FieldValue = 6 AND t1.UserID=t15.UserID AND t15.FieldID = 79 AND t15.FieldValue = 4 AND t1.UserID=t16.UserID AND t16.FieldID = 131 AND t16.FieldValue = 4 AND t1.UserID=t17.UserID AND t17.FieldID = 131 AND t17.FieldValue = 2 AND t1.UserID=t18.UserID AND t18.FieldID = 131
Re: Please help DB Error: unknown error
Typically, this is the kind of query I see at MS-SQL houses. :) -- R. Deuce - Original Message - From: Matt W [EMAIL PROTECTED] To: Thai Thanh Ha [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 5:28 PM Subject: Re: Please help DB Error: unknown error Hi Thai, I think you're just joining wy too many tables! LOL Matt - Original Message - From: Thai Thanh Ha Sent: Sunday, November 16, 2003 8:10 PM Subject: Please help DB Error: unknown error Hi all, I have a problem with my query on mySQL 4.0. DB Error: unknown error I don't know what is the problem. Please help me!!! Thanks in advance. Regards, Thai SELECT t.UserID, t.Country, t.Zip FROM tblUser t, tblUserProfile t1,tblUserProfile t2,tblUserProfile t3,tblUserProfile t4,tblUserProfile t5,tblUserProfile t6,tblUserProfile t7,tblUserProfile t8,tblUserProfile t9,tblUserProfile t10,tblUserProfile t11,tblUserProfile t12,tblUserProfile t13,tblUserProfile t14,tblUserProfile t15,tblUserProfile t16,tblUserProfile t17,tblUserProfile t18,tblUserProfile t19,tblUserProfile t20,tblUserProfile t21,tblUserProfile t22,tblUserProfile t23,tblUserProfile t24,tblUserProfile t25,tblUserProfile t26,tblUserProfile t27,tblUserProfile t28,tblUserProfile t29,tblUserProfile t30,tblUserProfile t31,tblUserProfile t32,tblUserProfile t33,tblUserProfile t34,tblUserProfile t35,tblUserProfile t36,tblUserProfile t37,tblUserProfile t38,tblUserProfile t39,tblUserProfile t40,tblUserProfile t41,tblUserProfile t42,tblUserProfile t43,tblUserProfile t44,tblUserProfile t45,tblUserProfile t46,tblUserProfile t47,tblUserProfile t48,tblUserProfile t49,tblUserProfile t50,tblUserProfile t51,tblUserProfile t52,tblUserProfile ... snip ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help DB Error: unknown error
At 9:10 +0700 11/17/03, Thai Thanh Ha wrote: Hi all, I have a problem with my query on mySQL 4.0. DB Error: unknown error I don't know what is the problem. Please help me!!! Thanks in advance. Joins can join up to 31 tables in MySQL. Looks like you're exceeding that limit. Regards, Thai SELECT t.UserID, t.Country, t.Zip FROM tblUser t, tblUserProfile t1,tblUserProfile t2,tblUserProfile t3,tblUserProfile t4,tblUserProfile t5,tblUserProfile t6,tblUserProfile t7,tblUserProfile t8,tblUserProfile t9,tblUserProfile t10,tblUserProfile t11,tblUserProfile t12,tblUserProfile t13,tblUserProfile t14,tblUserProfile t15,tblUserProfile t16,tblUserProfile t17,tblUserProfile t18,tblUserProfile t19,tblUserProfile t20,tblUserProfile t21,tblUserProfile t22,tblUserProfile t23,tblUserProfile t24,tblUserProfile t25,tblUserProfile t26,tblUserProfile t27,tblUserProfile t28,tblUserProfile t29,tblUserProfile t30,tblUserProfile t31,tblUserProfile t32,tblUserProfile t33,tblUserProfile t34,tblUserProfile t35,tblUserProfile t36,tblUserProfile t37,tblUserProfile t38,tblUserProfile t39,tblUserProfile t40,tblUserProfile t41,tblUserProfile t42,tblUserProfile t43,tblUserProfile t44,tblUserProfile t45,tblUserProfile t46,tblUserProfile t47,tblUserProfile t48,tblUserProfile t49,tblUserProfile t50,tblUserProfile t51,tblUserProfile t52,tblUserProfile t53,tblUserProfile t54,tblUserProfile t55,tblUserProfile t56,tblUserProfile t57,tblUserProfile t58,tblUserProfile t59,tblUserProfile t60,tblUserProfile t61,tblUserProfile t62,tblUserProfile t63,tblUserProfile t64,tblUserProfile t65,tblUserProfile t66,tblUserProfile t67,tblUserProfile t68,tblUserProfile t69,tblUserProfile t70,tblUserProfile t71,tblUserProfile t72,tblUserProfile t73,tblUserProfile t74,tblUserProfile t75,tblUserProfile t76,tblUserProfile t77,tblUserProfile t78,tblUserProfile t79,tblUserProfile t80,tblUserProfile t81,tblUserProfile t82,tblUserProfile t83,tblUserProfile t84,tblUserProfile t85,tblUserProfile t86,tblUserProfile t87,tblUserProfile t88,tblUserProfile t89,tblUserProfile t90,tblUserProfile t91,tblUserProfile t92,tblUserProfile t93,tblUserProfile t94,tblUserProfile t95,tblUserProfile t96,tblUserProfile t97,tblUserProfile t98,tblUserProfile t99,tblUserProfile t100,tblUserProfile t101,tblUserProfile t102,tblUserProfile t103,tblUserProfile t104,tblUserProfile t105,tblUserProfile t106,tblUserProfile t107,tblUserProfile t108,tblUserProfile t109,tblUserProfile t110,tblUserProfile t111,tblUserProfile t112,tblUserProfile t113,tblUserProfile t114,tblUserProfile t115,tblUserProfile t116,tblUserProfile t117,tblUserProfile t118,tblUserProfile t119,tblUserProfile t120,tblUserProfile t121,tblUserProfile t122,tblUserProfile t123,tblUserProfile t124,tblUserProfile t125,tblUserProfile t126,tblUserProfile t127,tblUserProfile t128,tblUserProfile t129,tblUserProfile t130,tblUserProfile t131,tblUserProfile t132,tblUserProfile t133,tblUserProfile t134,tblUserProfile t135,tblUserProfile t136,tblUserProfile t137,tblUserProfile t138,tblUserProfile t139,tblUserProfile t140,tblUserProfile t141,tblUserProfile t142,tblUserProfile t143,tblUserProfile t144,tblUserProfile t145,tblUserProfile t146,tblUserProfile t147,tblUserProfile t148,tblUserProfile t149,tblUserProfile t150,tblUserProfile t151,tblUserProfile t152,tblUserProfile t153,tblUserProfile t154,tblUserProfile t155,tblUserProfile t156,tblUserProfile t157,tblUserProfile t158,tblUserProfile t159,tblUserProfile t160,tblUserProfile t161, tblMatchProfile mp WHERE t.ShowProfile = 1 AND t.LoginHandle 'administrator' AND t.AccountType 2 AND t.AccountType 3 AND t.Gender = 1 AND (Year(Now()) - Year(t.BirthDate)) BETWEEN 18 AND 80 AND t.UserID = t1.UserID AND t1.FieldID = 78 AND t1.FieldValue = 8 AND t1.UserID=t2.UserID AND t2.FieldID = 78 AND t2.FieldValue = 6 AND t1.UserID=t3.UserID AND t3.FieldID = 78 AND t3.FieldValue = 5 AND t1.UserID=t4.UserID AND t4.FieldID = 78 AND t4.FieldValue = 3 AND t1.UserID=t5.UserID AND t5.FieldID = 78 AND t5.FieldValue = 4 AND t1.UserID=t6.UserID AND t6.FieldID = 78 AND t6.FieldValue = 7 AND t1.UserID=t7.UserID AND t7.FieldID = 78 AND t7.FieldValue = 1 AND t1.UserID=t8.UserID AND t8.FieldID = 78 AND t8.FieldValue = 9 AND t1.UserID=t9.UserID AND t9.FieldID = 78 AND t9.FieldValue = 2 AND t1.UserID=t10.UserID AND t10.FieldID = 79 AND t10.FieldValue = 3 AND t1.UserID=t11.UserID AND t11.FieldID = 79 AND t11.FieldValue = 1 AND t1.UserID=t12.UserID AND t12.FieldID = 79 AND t12.FieldValue = 2 AND t1.UserID=t13.UserID AND t13.FieldID = 79 AND t13.FieldValue = 5 AND t1.UserID=t14.UserID AND t14.FieldID = 79 AND t14.FieldValue = 6 AND t1.UserID=t15.UserID AND t15.FieldID = 79 AND t15.FieldValue = 4 AND t1.UserID=t16.UserID AND t16.FieldID = 131 AND t16.FieldValue = 4 AND t1.UserID=t17.UserID AND t17.FieldID = 131 AND t17.FieldValue = 2 AND t1.UserID=t18.UserID AND t18.FieldID = 131 AND t18.FieldValue = 1 AND t1.UserID=t19.UserID AND t19.FieldID = 131 AND t19.FieldValue = 3 AND t1.UserID=t20.UserID AND t20.FieldID = 137 AND
RE: Please help DB Error: unknown error
Can you please post the error? -Original Message- From: Thai Thanh Ha [mailto:[EMAIL PROTECTED] Sent: Sunday, November 16, 2003 8:10 PM To: '[EMAIL PROTECTED]' Subject: Please help DB Error: unknown error Hi all, I have a problem with my query on mySQL 4.0. DB Error: unknown error I don't know what is the problem. Please help me!!! Thanks in advance. Regards, Thai SELECT t.UserID, t.Country, t.Zip FROM tblUser t, tblUserProfile t1,tblUserProfile t2,tblUserProfile t3,tblUserProfile t4,tblUserProfile t5,tblUserProfile t6,tblUserProfile t7,tblUserProfile t8,tblUserProfile t9,tblUserProfile t10,tblUserProfile t11,tblUserProfile t12,tblUserProfile t13,tblUserProfile t14,tblUserProfile t15,tblUserProfile t16,tblUserProfile t17,tblUserProfile t18,tblUserProfile t19,tblUserProfile t20,tblUserProfile t21,tblUserProfile t22,tblUserProfile t23,tblUserProfile t24,tblUserProfile t25,tblUserProfile t26,tblUserProfile t27,tblUserProfile t28,tblUserProfile t29,tblUserProfile t30,tblUserProfile t31,tblUserProfile t32,tblUserProfile t33,tblUserProfile t34,tblUserProfile t35,tblUserProfile t36,tblUserProfile t37,tblUserProfile t38,tblUserProfile t39,tblUserProfile t40,tblUserProfile t41,tblUserProfile t42,tblUserProfile t43,tblUserProfile t44,tblUserProfile t45,tblUserProfile t46,tblUserProfile t47,tblUserProfile t48,tblUserProfile t49,tblUserProfile t50,tblUserProfile t51,tblUserProfile t52,tblUserProfile t53,tblUserProfile t54,tblUserProfile t55,tblUserProfile t56,tblUserProfile t57,tblUserProfile t58,tblUserProfile t59,tblUserProfile t60,tblUserProfile t61,tblUserProfile t62,tblUserProfile t63,tblUserProfile t64,tblUserProfile t65,tblUserProfile t66,tblUserProfile t67,tblUserProfile t68,tblUserProfile t69,tblUserProfile t70,tblUserProfile t71,tblUserProfile t72,tblUserProfile t73,tblUserProfile t74,tblUserProfile t75,tblUserProfile t76,tblUserProfile t77,tblUserProfile t78,tblUserProfile t79,tblUserProfile t80,tblUserProfile t81,tblUserProfile t82,tblUserProfile t83,tblUserProfile t84,tblUserProfile t85,tblUserProfile t86,tblUserProfile t87,tblUserProfile t88,tblUserProfile t89,tblUserProfile t90,tblUserProfile t91,tblUserProfile t92,tblUserProfile t93,tblUserProfile t94,tblUserProfile t95,tblUserProfile t96,tblUserProfile t97,tblUserProfile t98,tblUserProfile t99,tblUserProfile t100,tblUserProfile t101,tblUserProfile t102,tblUserProfile t103,tblUserProfile t104,tblUserProfile t105,tblUserProfile t106,tblUserProfile t107,tblUserProfile t108,tblUserProfile t109,tblUserProfile t110,tblUserProfile t111,tblUserProfile t112,tblUserProfile t113,tblUserProfile t114,tblUserProfile t115,tblUserProfile t116,tblUserProfile t117,tblUserProfile t118,tblUserProfile t119,tblUserProfile t120,tblUserProfile t121,tblUserProfile t122,tblUserProfile t123,tblUserProfile t124,tblUserProfile t125,tblUserProfile t126,tblUserProfile t127,tblUserProfile t128,tblUserProfile t129,tblUserProfile t130,tblUserProfile t131,tblUserProfile t132,tblUserProfile t133,tblUserProfile t134,tblUserProfile t135,tblUserProfile t136,tblUserProfile t137,tblUserProfile t138,tblUserProfile t139,tblUserProfile t140,tblUserProfile t141,tblUserProfile t142,tblUserProfile t143,tblUserProfile t144,tblUserProfile t145,tblUserProfile t146,tblUserProfile t147,tblUserProfile t148,tblUserProfile t149,tblUserProfile t150,tblUserProfile t151,tblUserProfile t152,tblUserProfile t153,tblUserProfile t154,tblUserProfile t155,tblUserProfile t156,tblUserProfile t157,tblUserProfile t158,tblUserProfile t159,tblUserProfile t160,tblUserProfile t161, tblMatchProfile mp WHERE t.ShowProfile = 1 AND t.LoginHandle 'administrator' AND t.AccountType 2 AND t.AccountType 3 AND t.Gender = 1 AND (Year(Now()) - Year(t.BirthDate)) BETWEEN 18 AND 80 AND t.UserID = t1.UserID AND t1.FieldID = 78 AND t1.FieldValue = 8 AND t1.UserID=t2.UserID AND t2.FieldID = 78 AND t2.FieldValue = 6 AND t1.UserID=t3.UserID AND t3.FieldID = 78 AND t3.FieldValue = 5 AND t1.UserID=t4.UserID AND t4.FieldID = 78 AND t4.FieldValue = 3 AND t1.UserID=t5.UserID AND t5.FieldID = 78 AND t5.FieldValue = 4 AND t1.UserID=t6.UserID AND t6.FieldID = 78 AND t6.FieldValue = 7 AND t1.UserID=t7.UserID AND t7.FieldID = 78 AND t7.FieldValue = 1 AND t1.UserID=t8.UserID AND t8.FieldID = 78 AND t8.FieldValue = 9 AND t1.UserID=t9.UserID AND t9.FieldID = 78 AND t9.FieldValue = 2 AND t1.UserID=t10.UserID AND t10.FieldID = 79 AND t10.FieldValue = 3 AND t1.UserID=t11.UserID AND t11.FieldID = 79 AND t11.FieldValue = 1 AND t1.UserID=t12.UserID AND t12.FieldID = 79 AND t12.FieldValue = 2 AND t1.UserID=t13.UserID AND t13.FieldID = 79 AND t13.FieldValue = 5 AND t1.UserID=t14.UserID AND t14.FieldID = 79 AND t14.FieldValue = 6 AND t1.UserID=t15.UserID AND t15.FieldID = 79 AND t15.FieldValue = 4 AND t1.UserID=t16.UserID AND t16.FieldID = 131 AND t16.FieldValue = 4 AND t1.UserID=t17.UserID AND t17.FieldID = 131 AND t17.FieldValue = 2 AND t1.UserID=t18.UserID AND t18.FieldID = 131 AND t18.FieldValue = 1 AND t1.UserID=t19.UserID AND t19.FieldID =
Re: Some help with a complex query
* Elisenda [...] So, the good way to write joins will be as follows, doesn't it? FASE.SQL_ID_CE=CA.CA_ID_CE AND FASE.PR_PP_ID_COORD=PP.PP_ID_PP AND CA.CA_ID_CE=CE.CE_ID_CE AND FASE.AU_PR_AULA=AU.AU_AULA Those are the exact same criteria as you had, only in a different order, isn't it? The order of these criteria within the WHERE clause does not matter, the server will select the optimal way to join, which is what is expressed in the EXPLAIN SELECT output. You also changed CE.CA_ID_CE=CA.CE_ID_CE to CA.CA_ID_CE=CE.CE_ID_CE, this is the same thing, it makes no difference. [...] So, when explain select says this CE ref Centro 7CA.CA_ID_CE10 , it isn't a good result for me, isn't it? That depends... Because CA it is supposed to have one record for each CE or the other way round. ...then it is not good. You would have expected a 1 in the last column. I have to review CA and CE. yes. :) [...] I don't understand... How many FASE records with PR_flag=1 and SQL_ID_PY='P081'? You say when you join FASE and CA on those criteria you get 253 rows, but you should get 753? I mean that in my database I have 753 records that match this critera (SQL_ID_PY=P081 and PR_flag=1 and CA_ID_Idioma=6) but the result of mysql gives me only 253. I still don't get it. When you say in my database, which database are you talking about? How do you know you have 753 when mysql (=database?) says you have 253? And what do you mean by records that match, the CA_ID_Idioma column is from a different table, isn't it? [...] Thank you very much for your help and patients. You're welcome. :) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Please help DB Error: unknown error
I see the only message DB Error: unknown error. Even when I run mysqld.exe with --log and --error-log options, I still cannot get a more descriptive error message. I guess the problem is because of the number of joins is too large or the number of *temporary* rows is too large. But I don't know how to resolve this problem. Regards, Thai -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Monday, November 17, 2003 8:02 PM To: 'Thai Thanh Ha'; '[EMAIL PROTECTED]' Subject: RE: Please help DB Error: unknown error Can you please post the error? -Original Message- From: Thai Thanh Ha [mailto:[EMAIL PROTECTED] Sent: Sunday, November 16, 2003 8:10 PM To: '[EMAIL PROTECTED]' Subject: Please help DB Error: unknown error Hi all, I have a problem with my query on mySQL 4.0. DB Error: unknown error I don't know what is the problem. Please help me!!! Thanks in advance. Regards, Thai SELECT t.UserID, t.Country, t.Zip FROM tblUser t, tblUserProfile t1,tblUserProfile t2,tblUserProfile t3,tblUserProfile t4,tblUserProfile t5,tblUserProfile t6,tblUserProfile t7,tblUserProfile t8,tblUserProfile t9,tblUserProfile t10,tblUserProfile t11,tblUserProfile t12,tblUserProfile t13,tblUserProfile t14,tblUserProfile t15,tblUserProfile t16,tblUserProfile t17,tblUserProfile t18,tblUserProfile t19,tblUserProfile t20,tblUserProfile t21,tblUserProfile t22,tblUserProfile t23,tblUserProfile t24,tblUserProfile t25,tblUserProfile t26,tblUserProfile t27,tblUserProfile t28,tblUserProfile t29,tblUserProfile t30,tblUserProfile t31,tblUserProfile t32,tblUserProfile t33,tblUserProfile t34,tblUserProfile t35,tblUserProfile t36,tblUserProfile t37,tblUserProfile t38,tblUserProfile t39,tblUserProfile t40,tblUserProfile t41,tblUserProfile t42,tblUserProfile t43,tblUserProfile t44,tblUserProfile t45,tblUserProfile t46,tblUserProfile t47,tblUserProfile t48,tblUserProfile t49,tblUserProfile t50,tblUserProfile t51,tblUserProfile t52,tblUserProfile t53,tblUserProfile t54,tblUserProfile t55,tblUserProfile t56,tblUserProfile t57,tblUserProfile t58,tblUserProfile t59,tblUserProfile t60,tblUserProfile t61,tblUserProfile t62,tblUserProfile t63,tblUserProfile t64,tblUserProfile t65,tblUserProfile t66,tblUserProfile t67,tblUserProfile t68,tblUserProfile t69,tblUserProfile t70,tblUserProfile t71,tblUserProfile t72,tblUserProfile t73,tblUserProfile t74,tblUserProfile t75,tblUserProfile t76,tblUserProfile t77,tblUserProfile t78,tblUserProfile t79,tblUserProfile t80,tblUserProfile t81,tblUserProfile t82,tblUserProfile t83,tblUserProfile t84,tblUserProfile t85,tblUserProfile t86,tblUserProfile t87,tblUserProfile t88,tblUserProfile t89,tblUserProfile t90,tblUserProfile t91,tblUserProfile t92,tblUserProfile t93,tblUserProfile t94,tblUserProfile t95,tblUserProfile t96,tblUserProfile t97,tblUserProfile t98,tblUserProfile t99,tblUserProfile t100,tblUserProfile t101,tblUserProfile t102,tblUserProfile t103,tblUserProfile t104,tblUserProfile t105,tblUserProfile t106,tblUserProfile t107,tblUserProfile t108,tblUserProfile t109,tblUserProfile t110,tblUserProfile t111,tblUserProfile t112,tblUserProfile t113,tblUserProfile t114,tblUserProfile t115,tblUserProfile t116,tblUserProfile t117,tblUserProfile t118,tblUserProfile t119,tblUserProfile t120,tblUserProfile t121,tblUserProfile t122,tblUserProfile t123,tblUserProfile t124,tblUserProfile t125,tblUserProfile t126,tblUserProfile t127,tblUserProfile t128,tblUserProfile t129,tblUserProfile t130,tblUserProfile t131,tblUserProfile t132,tblUserProfile t133,tblUserProfile t134,tblUserProfile t135,tblUserProfile t136,tblUserProfile t137,tblUserProfile t138,tblUserProfile t139,tblUserProfile t140,tblUserProfile t141,tblUserProfile t142,tblUserProfile t143,tblUserProfile t144,tblUserProfile t145,tblUserProfile t146,tblUserProfile t147,tblUserProfile t148,tblUserProfile t149,tblUserProfile t150,tblUserProfile t151,tblUserProfile t152,tblUserProfile t153,tblUserProfile t154,tblUserProfile t155,tblUserProfile t156,tblUserProfile t157,tblUserProfile t158,tblUserProfile t159,tblUserProfile t160,tblUserProfile t161, tblMatchProfile mp WHERE t.ShowProfile = 1 AND t.LoginHandle 'administrator' AND t.AccountType 2 AND t.AccountType 3 AND t.Gender = 1 AND (Year(Now()) - Year(t.BirthDate)) BETWEEN 18 AND 80 AND t.UserID = t1.UserID AND t1.FieldID = 78 AND t1.FieldValue = 8 AND t1.UserID=t2.UserID AND t2.FieldID = 78 AND t2.FieldValue = 6 AND t1.UserID=t3.UserID AND t3.FieldID = 78 AND t3.FieldValue = 5 AND t1.UserID=t4.UserID AND t4.FieldID = 78 AND t4.FieldValue = 3 AND t1.UserID=t5.UserID AND t5.FieldID = 78 AND t5.FieldValue = 4 AND t1.UserID=t6.UserID AND t6.FieldID = 78 AND t6.FieldValue = 7 AND t1.UserID=t7.UserID AND t7.FieldID = 78 AND t7.FieldValue = 1 AND t1.UserID=t8.UserID AND t8.FieldID = 78 AND t8.FieldValue = 9 AND t1.UserID=t9.UserID AND t9.FieldID = 78 AND t9.FieldValue = 2 AND t1.UserID=t10.UserID AND t10.FieldID = 79 AND t10.FieldValue = 3 AND t1.UserID=t11.UserID AND t11.FieldID
Re: Needing help about PHP+mysql on RedHat 9.
Mario Miyojim wrote: I am trying to revive an existing LAMP system. I recently installed RedHat9. I had several LAMP applications working under RedHat 7.1, but now they don't function properly. For instance, I have many scripts that use the FORM ACTION=?php print($PHP_SELF) METHOD=post construct, but they are not working as they should. I tried to get help from the PHP home site, but www.php.net has been unreacheable to me for several days. I am considering a desperate attempt to go back to RedHat 7.1 because that configuration supported my PHP scripts, but now I can't even read the updated PHP manual. Please, if anyone knows how to circumvent this problem, help me! Thanks Chances are, register_globals was on with your old installation, and now it is off by default in php. Either turn it on in the php.ini (not-recommended), and restart your webserver, or rewrite your code to work with globals off (recommended). Things like $PHP_SELF would now be, $_SERVER['PHP_SELF']. Look here http://us3.php.net/register_globals Also, you'll get more help with an item such as this from a php mailing list. -- By-Tor.com It's all about the Rush http://www.by-tor.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some help with a complex query
Roger, * Elisenda [...] The explain select says as follows: [...] I re-formatted the query and the EXPLAIN output for readability: Sorry for not re-formatted the query, I've learn it for next time. Well, I've learn a lot with your lessons. Select FASE.PR_Date_Visita_2, CE.CE_Centro, CE.CE_Domicilio, CE.CE_CP, CE.CE_Poblacion, CE.CE_Capital, CE.CE_PROV, CE.CE_CCAA, CA.CA_Horario, PP.PP_Contacto, PP.PP_Cargo, AU.AU_A_M, AU.AU_A_F From FASE,CE,CA,PP,AU where FASE.SQL_ID_PY='P081' AND FASE.PR_FLAG='1' AND CA.CA_ID_IDIOMA_A='6' AND AU.AU_NIVEL='13.14' AND FASE.SQL_ID_CE=CE.CE_ID_CE AND FASE.SQL_ID_CE=CA.CA_ID_CE AND CE.CE_ID_CE=CA.CA_ID_CE AND FASE.PR_PP_ID_COORD=PP.PP_ID_PP AND FASE.AU_PR_AULA=AU.AU_AULA table type key key_len ref rows FASE ref Participa 12 const,const 1157 CA ref Centro 7FASE.SQL_ID_CE 1 PP ref PP_ID 7FASE.PR_PP_ID_Coord 1 CE ref Centro 7CA.CA_ID_CE10 AU ref AU_AULA 256 FASE.AU_PR_Aula 264 (I removed the possible_keys and Extra columns) The first thing the EXPLAIN output tells us is in what order the server will access the tables. In this case the FASE table is read first, then the CA and PP tables are read based on the columns SQL_ID_CE and PR_PP_ID_Coord from FASE (the 'ref' column), then the CE table is read based on the value of CA.CA_ID_CE, and finally the AU table is read based on FASE.AU_PR_Aula. So, the good way to write joins will be as follows, doesn't it? FASE.SQL_ID_CE=CA.CA_ID_CE AND FASE.PR_PP_ID_COORD=PP.PP_ID_PP AND CA.CA_ID_CE=CE.CE_ID_CE AND FASE.AU_PR_AULA=AU.AU_AULA The 'rows' column hows approximately how many rows the server will have to read. It is a goal to make/keep these numbers low, I don't know if you did a EXPLAIN before you created you indexes, in that case you will see that the numbers in the 'rows' column was higher, possibly as high as the row count of the respective tables. A way to calculate how 'heavy' a select query is, is to multiply these numbers. In the case above, the multiplum is 1157*1*1*10*264 = 3054480. In other words, the server must examine approximately 3 million rows to produce your result. (Note that this is an estimate, based on statistics stored in the server. Running OPTIMIZE TABLE will update these statistics, and this may also change the servers preferred plan.) So, when explain select says this CE ref Centro 7CA.CA_ID_CE10 , it isn't a good result for me, isn't it? Because CA it is supposed to have one record for each CE or the other way round. I have to review CA and CE. The 'ref' column for FASE says 'const,const'. This means the index used (Participa) is a combined index used to match two constants, presumably the SQL_ID_PY and PR_FLAG. Is the number 1157 close to correct? Yes, it is correct. I have 1.157 record which match SQL_ID_PY=P081 and PR_flag=1 What I know is that I have 753 records which match FASE.PR_flag=1 and FASE.SQL_ID_PY=P081 and CA.CA_ID_Idioma_A=6 and my result is 253 records. I don't understand... How many FASE records with PR_flag=1 and SQL_ID_PY='P081'? You say when you join FASE and CA on those criteria you get 253 rows, but you should get 753? I mean that in my database I have 753 records that match this critera (SQL_ID_PY=P081 and PR_flag=1 and CA_ID_Idioma=6) but the result of mysql gives me only 253. In general, if you get 'too few' rows on a multi-table join like the one you are doing here, it could be because some of the tables you join to does not have a corresponding row for the criteria. If that is the case, and you still want those rows to show up in the result, you can use LEFT JOIN for those tables. URL: http://www.mysql.com/doc/en/JOIN.html Does it have to be with my query? Or does it have to be with data in mysql, I mean I didn¹t insert them allright? I don't know. Check each table separately, use SELECT COUNT(*) FROM ... WHERE ... to check how many rows match any given criteria. Try to use the output of EXPLAIN SELECT to manually do what the server will be doing, and see if you get any unexpected results. Run OPTIMIZE TABLE to refresh the index statistics. I will do it. URL: http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html For further speed improvements: Your AU_AULA and CA.Centro indexes could be replaced by combined indexes, (AU_AULA,AU_NIVEL) and (CA_ID_CE,CA_ID_IDIOMA_A) respectively. Your column CA.CA_ID_IDIOMA_A is defined as an integer, in your WHERE clause you should check for integer 6, not string '6'. This also applies to FASE.PR_FLAG. The AU.AU_NIVEL column was not mentioned in your previous table description, so I don't know if it's numeric or a string. If it is numeric, you should not use quotes on the constant. It will work, but the server must convert from string to integer, this
Re: Some help with a complex query
Roger, Your help was fantastic. It seems that it goes better. At the end I achieve some result but not all I need. The explain select says as follows: EXPLAIN Select FASE.PR_Date_Visita_2, CE.CE_Centro, CE.CE_Domicilio, CE.CE_CP, CE.CE_Poblacion, CE.CE_Capital, CE.CE_PROV, CE.CE_CCAA,CA.CA_Horario, PP.PP_Contacto, PP.PP_Cargo, AU.AU_A_M, AU.AU_A_F From FASE,CE,CA,PP,AU where FASE.SQL_ID_PY='P081' AND FASE.PR_FLAG='1' AND CA.CA_ID_IDIOMA_A='6' AND AU.AU_NIVEL='13.14' AND FASE.SQL_ID_CE=CE.CE_ID_CE AND FASE.SQL_ID_CE=CA.CA_ID_CE AND CE.CE_ID_CE=CA.CA_ID_CE AND FASE.PR_PP_ID_COORD=PP.PP_ID_PP AND FASE.AU_PR_AULA=AU.AU_AULA\ table type possible_keys keykey_len ref rows Extra FASE ref Proyecto,Folleto,Solicitud,Participa,Seguimiento,Ganador,Solicitud_CCAA,Soli citud_PROV,TipoSL_CCAA,TipoSL_PROV,SG_Recibibido_CCAA,SG_Recibibido_PROV,PR_ Aula,SL_Categoria_CCAA,Centro Participa 12 const,const 1157 Using where CA ref Centro,IDIOMA_A Centro 7FASE.SQL_ID_CE 1 Using where PP ref PP_ID PP_ID 7FASE.PR_PP_ID_Coord 1 Using where CE ref Centro Centro 7CA.CA_ID_CE 10Using where AU ref Nivel_FASE,AU_AULA,Au_Nivel AU_AULA256 FASE.AU_PR_Aula 264 Using where What I know is that I have 753 records which match FASE.PR_flag=1 and FASE.SQL_ID_PY=P081 and CA.CA_ID_Idioma_A=6 and my result is 253 records. Does it have to be with my query? Or does it have to be with data in mysql, I mean I didn¹t insert them allright? eli * Elisenda The problem is that it 's too slow and the result doesn't appear. I am going to try to explain the query. Please do that using the EXPLAIN SELECT statement: URL: http://www.mysql.com/doc/en/EXPLAIN.html This will show what index is beeing used on the different joins, and approximately how many rows the server must handle to produce your result. I suspect that in this case there are no index on some of the columns beeing used for the joins, whih means the server must scan the entire table multiple times. This will often result in a query that appears to 'hang', no result is returned. The server is actually working on the result, but it will take 'forever', you will normally kill your connection before you recieve anything. More below... Fields from Table FASE: (300.000 records) ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, SQL_ID_PY char(6), SQL_ID_CE char(6), PR_flag INT, PR_Date_Visita_2 Date, AU_PR_Aula varchar(255) (it a field that contain SQL_ID_PY_SQL_ID_CE_PR) Field from Table CE (30.000 records) CE_ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, CE_ID_CE char(6), CE_Centro varchar(32), CE_Domicilio varchar(32), CE_CP varchar(5), CE_Poblacion varchar(30), CE_ID_Capital char(2), CE_Capital varchar(30), CE_ID_PROV char(2), CE_PROV varchar(15), CE_ID_CCAA char(2), CE_CCAA varchar(15) Field from Table CA (30.000 records) CA_ID INT NOT NULL PRIMARY KEY, CA_ID_User char(6), CA_ID_CE char(6), CA_Centro varchar(32), CA_ID_Idioma_A INT, CA_Horario varchar(30) Fields from table AU (700.000 records) AU_ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, AU_ID_CE char(6), AU_ID_PY char(6), AU_ID_FASE INT, AU_A_M INT, AU_A_F INT, AU_Aula varchar(32) (it a field that contain AU_ID_PY_AU_ID_CE_PR) Fields from table PP (200.000 records) PP_ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, PP_ID_PP char(6), PP_ID_CE char(6), PP_Contacto char(50), PP_ID_Cargo char(6), PP_Cargo char(32) There seems to be only primary keys on these tables? No other index defined? If that is the case, this is probably the reason of your problem. Put an index on any column used to join other tables, the so-called foreign keys. I select from Fase some records. From fase I only want records (SQL_ID_CE) that have FASE.SQL_ID_PY='P081' AND FASE.PR_FLAG= '1'. From this selection, You can create a combined index on SQL_ID_PY and PR_FLAG: CREATE INDEX SQL_ID_PY_PR_FLAG_INDEX ON FASE (SQL_ID_PY,PR_FLAG) I only want records that in AU have AU.AU_NIVEL= '13.14' and in CA have CA.CA_ID_IDIOMA_A= '6'. Then probably both AU.AU_NIVEL and CA.CA_ID_IDIOMA_A should be indexed. In WHERE I write AU.AU_Aula= fase.AU_PR_Aula AND AU.AU_ID_CE = CA.CA_ID_CE AND CE.CE_ID_CE = CA.CA_ID_CE AND CE.CE_ID_CE = Fase.SQL_ID_CE AND CE.CE_ID_CE = PP.PP_ID_CE AND Fase.PR_PP_ID_Coord = PP.PP_ID_PP Main relation in all tables is SQL_ID_CE. Then all columns related to SQL_ID_CE should have an index. Probably also some of the other fields mentioned above: AU.AU_Aula, fase.AU_PR_Aula, AU.AU_ID_CE, CA.CA_ID_CE, CE.CE_ID_CE, Fase.SQL_ID_CE, PP.PP_ID_CE, Fase.PR_PP_ID_Coord, PP.PP_ID_PP. MySQL will not use more than one index per table per select statement, but which index
RE: Please help. MySQL Error.
do a show status like 'open_files' and a show variables like 'open_files_limit' -Original Message- From: William Bailey [mailto:[EMAIL PROTECTED] Sent: Friday, November 14, 2003 6:56 AM To: [EMAIL PROTECTED] Subject: Please help. MySQL Error. -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I am currently getting the following error on one of the mysql servers im looking after and wonder if anybody knows what specifically it relates to. Error in accept: Too many open files Im currently running MySQL version '4.0.14' under FreeBSD 5.1 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE/tNDpzSfrYDJMXmERAvKBAKC6vY0PnowjAaI8sRIIu+Mumeum8gCfVWAH hRU4PeRdpbIGgWPI9/xWVJY= =wHd+ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some help with a complex query
* Elisenda [...] The explain select says as follows: [...] I re-formatted the query and the EXPLAIN output for readability: Select FASE.PR_Date_Visita_2, CE.CE_Centro, CE.CE_Domicilio, CE.CE_CP, CE.CE_Poblacion, CE.CE_Capital, CE.CE_PROV, CE.CE_CCAA, CA.CA_Horario, PP.PP_Contacto, PP.PP_Cargo, AU.AU_A_M, AU.AU_A_F From FASE,CE,CA,PP,AU where FASE.SQL_ID_PY='P081' AND FASE.PR_FLAG='1' AND CA.CA_ID_IDIOMA_A='6' AND AU.AU_NIVEL='13.14' AND FASE.SQL_ID_CE=CE.CE_ID_CE AND FASE.SQL_ID_CE=CA.CA_ID_CE AND CE.CE_ID_CE=CA.CA_ID_CE AND FASE.PR_PP_ID_COORD=PP.PP_ID_PP AND FASE.AU_PR_AULA=AU.AU_AULA table type key key_len ref rows FASE ref Participa 12 const,const 1157 CA ref Centro 7FASE.SQL_ID_CE 1 PP ref PP_ID 7FASE.PR_PP_ID_Coord 1 CE ref Centro 7CA.CA_ID_CE10 AU ref AU_AULA 256 FASE.AU_PR_Aula 264 (I removed the possible_keys and Extra columns) The first thing the EXPLAIN output tells us is in what order the server will access the tables. In this case the FASE table is read first, then the CA and PP tables are read based on the columns SQL_ID_CE and PR_PP_ID_Coord from FASE (the 'ref' column), then the CE table is read based on the value of CA.CA_ID_CE, and finally the AU table is read based on FASE.AU_PR_Aula. The 'rows' column hows approximately how many rows the server will have to read. It is a goal to make/keep these numbers low, I don't know if you did a EXPLAIN before you created you indexes, in that case you will see that the numbers in the 'rows' column was higher, possibly as high as the row count of the respective tables. A way to calculate how 'heavy' a select query is, is to multiply these numbers. In the case above, the multiplum is 1157*1*1*10*264 = 3054480. In other words, the server must examine approximately 3 million rows to produce your result. (Note that this is an estimate, based on statistics stored in the server. Running OPTIMIZE TABLE will update these statistics, and this may also change the servers preferred plan.) The 'ref' column for FASE says 'const,const'. This means the index used (Participa) is a combined index used to match two constants, presumably the SQL_ID_PY and PR_FLAG. Is the number 1157 close to correct? What I know is that I have 753 records which match FASE.PR_flag=1 and FASE.SQL_ID_PY=P081 and CA.CA_ID_Idioma_A=6 and my result is 253 records. I don't understand... How many FASE records with PR_flag=1 and SQL_ID_PY='P081'? You say when you join FASE and CA on those criteria you get 253 rows, but you should get 753? In general, if you get 'too few' rows on a multi-table join like the one you are doing here, it could be because some of the tables you join to does not have a corresponding row for the criteria. If that is the case, and you still want those rows to show up in the result, you can use LEFT JOIN for those tables. URL: http://www.mysql.com/doc/en/JOIN.html Does it have to be with my query? Or does it have to be with data in mysql, I mean I didn¹t insert them allright? I don't know. Check each table separately, use SELECT COUNT(*) FROM ... WHERE ... to check how many rows match any given criteria. Try to use the output of EXPLAIN SELECT to manually do what the server will be doing, and see if you get any unexpected results. Run OPTIMIZE TABLE to refresh the index statistics. URL: http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html For further speed improvements: Your AU_AULA and CA.Centro indexes could be replaced by combined indexes, (AU_AULA,AU_NIVEL) and (CA_ID_CE,CA_ID_IDIOMA_A) respectively. Your column CA.CA_ID_IDIOMA_A is defined as an integer, in your WHERE clause you should check for integer 6, not string '6'. This also applies to FASE.PR_FLAG. The AU.AU_NIVEL column was not mentioned in your previous table description, so I don't know if it's numeric or a string. If it is numeric, you should not use quotes on the constant. It will work, but the server must convert from string to integer, this take some time, using a constant of a type matching the column type is faster. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some help with a complex query
I'm sorry I didn't explain anything. The problem is that it 's too slow and the result doesn't appear. I am going to try to explain the query. Fields from Table FASE: (300.000 records) ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, SQL_ID_PY char(6), SQL_ID_CE char(6), PR_flag INT, PR_Date_Visita_2 Date, AU_PR_Aula varchar(255) (it a field that contain SQL_ID_PY_SQL_ID_CE_PR) Field from Table CE (30.000 records) CE_ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, CE_ID_CE char(6), CE_Centro varchar(32), CE_Domicilio varchar(32), CE_CP varchar(5), CE_Poblacion varchar(30), CE_ID_Capital char(2), CE_Capital varchar(30), CE_ID_PROV char(2), CE_PROV varchar(15), CE_ID_CCAA char(2), CE_CCAA varchar(15) Field from Table CA (30.000 records) CA_ID INT NOT NULL PRIMARY KEY, CA_ID_User char(6), CA_ID_CE char(6), CA_Centro varchar(32), CA_ID_Idioma_A INT, CA_Horario varchar(30) Fields from table AU (700.000 records) AU_ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, AU_ID_CE char(6), AU_ID_PY char(6), AU_ID_FASE INT, AU_A_M INT, AU_A_F INT, AU_Aula varchar(32) (it a field that contain AU_ID_PY_AU_ID_CE_PR) Fields from table PP (200.000 records) PP_ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, PP_ID_PP char(6), PP_ID_CE char(6), PP_Contacto char(50), PP_ID_Cargo char(6), PP_Cargo char(32) I select from Fase some records. From fase I only want records (SQL_ID_CE) that have FASE.SQL_ID_PY='P081' AND FASE.PR_FLAG= '1'. From this selection, I only want records that in AU have AU.AU_NIVEL= '13.14' and in CA have CA.CA_ID_IDIOMA_A= '6'. In WHERE I write AU.AU_Aula= fase.AU_PR_Aula AND AU.AU_ID_CE = CA.CA_ID_CE AND CE.CE_ID_CE = CA.CA_ID_CE AND CE.CE_ID_CE = Fase.SQL_ID_CE AND CE.CE_ID_CE = PP.PP_ID_CE AND Fase.PR_PP_ID_Coord = PP.PP_ID_PP Main relation in all tables is SQL_ID_CE. I don't know if I explain myself or it is too boring to continue reading. It will be fantastic if some can help me. I don't know if I am doing something wrong or what. * Elisenda I have a query which tries to select different fields from 5 different tables. In WHERE part I have write all the conditions and relationships. Perhaps two many. Joining 5 tables should not be a problem, but having indexes on the relevant columns may be essential, especially on large tables. The main table for me is FASE. From this table I try to find all the other information. I guess I'm doing something wrong but I don't know what. What is the problem? Do you get an error message, does it return unexpected results, or is it just too slow? SELECT CE.CE_CENTRO, CE.CE_DOMICILIO, CE.CE_CP, CE.CE_POBLACION, CE.CE_PROV, PP.PP_CONTACTO, PP.PP_CARGO, CA.CA_HORARIO, AU.AU_A_M, AU.AU_A_F, FASE.PR_DATE_VISITA_1 FROM AU, CA, CE,FASE,PP WHERE FASE.SQL_ID_PY='P081' AND FASE.PR_FLAG= '1' AND CA.CA_ID_IDIOMA_A= '6' AND AU.AU_NIVEL= '13.14' AND AU.AU_Aula= fase.AU_PR_Aula AND AU.AU_ID_CE = CA.CA_ID_CE AND CE.CE_ID_CE = CA.CA_ID_CE AND CE.CE_ID_CE = Fase.SQL_ID_CE AND CE.CE_ID_CE = PP.PP_ID_CE AND Fase.PR_PP_ID_Coord = PP.PP_ID_PP It's difficult to suggest changes without knowing what the problem is... :) I can however safely suggest that you use a consistent letter casing on your table names... is it FASE, Fase or fase? On some MySQL servers this will make a difference, on others it may not. (I think mysql on windows is case insensitive by default, but this may be changed at compile-time, iirc.) Please tell us what the problem is, and if it's about efficiency, post the output of EXPLAIN SELECT your_query, that should get us started. :) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some help with a complex query
* Elisenda The problem is that it 's too slow and the result doesn't appear. I am going to try to explain the query. Please do that using the EXPLAIN SELECT statement: URL: http://www.mysql.com/doc/en/EXPLAIN.html This will show what index is beeing used on the different joins, and approximately how many rows the server must handle to produce your result. I suspect that in this case there are no index on some of the columns beeing used for the joins, whih means the server must scan the entire table multiple times. This will often result in a query that appears to 'hang', no result is returned. The server is actually working on the result, but it will take 'forever', you will normally kill your connection before you recieve anything. More below... Fields from Table FASE: (300.000 records) ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, SQL_ID_PY char(6), SQL_ID_CE char(6), PR_flag INT, PR_Date_Visita_2 Date, AU_PR_Aula varchar(255) (it a field that contain SQL_ID_PY_SQL_ID_CE_PR) Field from Table CE (30.000 records) CE_ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, CE_ID_CE char(6), CE_Centro varchar(32), CE_Domicilio varchar(32), CE_CP varchar(5), CE_Poblacion varchar(30), CE_ID_Capital char(2), CE_Capital varchar(30), CE_ID_PROV char(2), CE_PROV varchar(15), CE_ID_CCAA char(2), CE_CCAA varchar(15) Field from Table CA (30.000 records) CA_ID INT NOT NULL PRIMARY KEY, CA_ID_User char(6), CA_ID_CE char(6), CA_Centro varchar(32), CA_ID_Idioma_A INT, CA_Horario varchar(30) Fields from table AU (700.000 records) AU_ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, AU_ID_CE char(6), AU_ID_PY char(6), AU_ID_FASE INT, AU_A_M INT, AU_A_F INT, AU_Aula varchar(32) (it a field that contain AU_ID_PY_AU_ID_CE_PR) Fields from table PP (200.000 records) PP_ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, PP_ID_PP char(6), PP_ID_CE char(6), PP_Contacto char(50), PP_ID_Cargo char(6), PP_Cargo char(32) There seems to be only primary keys on these tables? No other index defined? If that is the case, this is probably the reason of your problem. Put an index on any column used to join other tables, the so-called foreign keys. I select from Fase some records. From fase I only want records (SQL_ID_CE) that have FASE.SQL_ID_PY='P081' AND FASE.PR_FLAG= '1'. From this selection, You can create a combined index on SQL_ID_PY and PR_FLAG: CREATE INDEX SQL_ID_PY_PR_FLAG_INDEX ON FASE (SQL_ID_PY,PR_FLAG) I only want records that in AU have AU.AU_NIVEL= '13.14' and in CA have CA.CA_ID_IDIOMA_A= '6'. Then probably both AU.AU_NIVEL and CA.CA_ID_IDIOMA_A should be indexed. In WHERE I write AU.AU_Aula= fase.AU_PR_Aula AND AU.AU_ID_CE = CA.CA_ID_CE AND CE.CE_ID_CE = CA.CA_ID_CE AND CE.CE_ID_CE = Fase.SQL_ID_CE AND CE.CE_ID_CE = PP.PP_ID_CE AND Fase.PR_PP_ID_Coord = PP.PP_ID_PP Main relation in all tables is SQL_ID_CE. Then all columns related to SQL_ID_CE should have an index. Probably also some of the other fields mentioned above: AU.AU_Aula, fase.AU_PR_Aula, AU.AU_ID_CE, CA.CA_ID_CE, CE.CE_ID_CE, Fase.SQL_ID_CE, PP.PP_ID_CE, Fase.PR_PP_ID_Coord, PP.PP_ID_PP. MySQL will not use more than one index per table per select statement, but which index to use may vary, depending on the criteria and the distribution of your data. In what order the tables are read, will also vary. Don't be afraid of indexing too many columns, you can easily remove any unused index after you have identified which you really need. The EXPLAIN SELECT statement will let you identify the actual index used, but you may need to test with various data, because of the internal join optimizer behaviour mentioned above, the index used may change depending on your criteria/data. By the way, why don't you use the primary keys? It is very common to use the primary keys for some of the joins when joining many tables. For instance, you join the PP table using PP_ID_PP and Fase.PR_PP_ID_Coord, is it supposed to return multiple PP rows for each Fase row? If you only except one, i.e. PP_ID_PP is unique in the PP table, then you should have a UNIQUE index on it, or promote it to primary key, or maybe use the primary key in place of this column? I don't know if I explain myself or it is too boring to continue reading. I'm not bored. :) It will be fantastic if some can help me. I don't know if I am doing something wrong or what. I think you only need indexing. Run EXPLAIN SELECT first, save the output (or post it here), put on some indexes, run EXPLAIN SELECT again, and see the difference. When all the numbers in the 'rows' column of the explain select output multiplied together is a relatively low number, your query should be fast... please include the result of EXPLAIN SELECT if you have more questions/problems. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some help with a complex query
* Elisenda I have a query which tries to select different fields from 5 different tables. In WHERE part I have write all the conditions and relationships. Perhaps two many. Joining 5 tables should not be a problem, but having indexes on the relevant columns may be essential, especially on large tables. The main table for me is FASE. From this table I try to find all the other information. I guess I'm doing something wrong but I don't know what. What is the problem? Do you get an error message, does it return unexpected results, or is it just too slow? SELECT CE.CE_CENTRO, CE.CE_DOMICILIO, CE.CE_CP, CE.CE_POBLACION, CE.CE_PROV, PP.PP_CONTACTO, PP.PP_CARGO, CA.CA_HORARIO, AU.AU_A_M, AU.AU_A_F, FASE.PR_DATE_VISITA_1 FROM AU, CA, CE,FASE,PP WHERE FASE.SQL_ID_PY='P081' AND FASE.PR_FLAG= '1' AND CA.CA_ID_IDIOMA_A= '6' AND AU.AU_NIVEL= '13.14' AND AU.AU_Aula= fase.AU_PR_Aula AND AU.AU_ID_CE = CA.CA_ID_CE AND CE.CE_ID_CE = CA.CA_ID_CE AND CE.CE_ID_CE = Fase.SQL_ID_CE AND CE.CE_ID_CE = PP.PP_ID_CE AND Fase.PR_PP_ID_Coord = PP.PP_ID_PP It's difficult to suggest changes without knowing what the problem is... :) I can however safely suggest that you use a consistent letter casing on your table names... is it FASE, Fase or fase? On some MySQL servers this will make a difference, on others it may not. (I think mysql on windows is case insensitive by default, but this may be changed at compile-time, iirc.) Please tell us what the problem is, and if it's about efficiency, post the output of EXPLAIN SELECT your_query, that should get us started. :) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some help with a complex query
it would help alot if you dump the table structure for us - Original Message - From: Elisenda To: [EMAIL PROTECTED] Sent: Tuesday, November 11, 2003 7:10 PM Subject: Some help with a complex query I have a query which tries to select different fields from 5 different tables. In WHERE part I have write all the conditions and relationships. Perhaps two many. The main table for me is FASE. From this table I try to find all the other information. I guess I'm doing something wrong but I don't know what. SELECT CE.CE_CENTRO, CE.CE_DOMICILIO, CE.CE_CP, CE.CE_POBLACION, CE.CE_PROV, PP.PP_CONTACTO, PP.PP_CARGO, CA.CA_HORARIO, AU.AU_A_M, AU.AU_A_F, FASE.PR_DATE_VISITA_1 FROM AU, CA, CE,FASE,PP WHERE FASE.SQL_ID_PY='P081' AND FASE.PR_FLAG= '1' AND CA.CA_ID_IDIOMA_A= '6' AND AU.AU_NIVEL= '13.14' AND AU.AU_Aula= fase.AU_PR_Aula AND AU.AU_ID_CE = CA.CA_ID_CE AND CE.CE_ID_CE = CA.CA_ID_CE AND CE.CE_ID_CE = Fase.SQL_ID_CE AND CE.CE_ID_CE = PP.PP_ID_CE AND Fase.PR_PP_ID_Coord = PP.PP_ID_PP Thank you fro your help in advanced. Eli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help constructing query ...
* John Kelly
I have a table of full URLs and IPs and am using the following
query to return
distinct web requests by domain. Using SUBSTRING_INDEX it only returns the
domain part of the URL:
SELECT SUBSTRING_INDEX(url, '/', 3) as topsites, count(distinct
ip) as count
from tablename WHERE SUBSTRING_INDEX(url, '/', 3) LIKE
'%mydomain%' group by
topsites order by count
Example output:
topsitescount
http://www.mydomain.com5
http://mydomain.com 3
My question is how do I modify the query to get it to merge
requests for the
same domain by ignoring the www. so that the above would return:
http://mydomain.com 8
I think it has something to do with adding
REPLACE('url', 'www.', '')
but I can't figure out where to put it to make it work.
Try either
SUBSTRING_INDEX(REPLACE(url, 'www.', ''), '/', 3)
or
REPLACE(SUBSTRING_INDEX(url, '/', 3),'www.', '')
You don't need it in the WHERE clause, only in the field list and GROUP BY:
SELECT REPLACE(SUBSTRING_INDEX(url, '/', 3),'www.', '') as topsites,
count(distinct ip) as count from tablename
WHERE SUBSTRING_INDEX(url, '/', 3) LIKE '%mydomain%'
group by topsites order by count
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help comparing MySQL to MS SQL Server
Hi Kevin, I'm a system administrator for a small (200 people) branch of a large university/medical school. I've worked with MySQL and use it as my database of choice for web-based dynamic content. I would not consider myself an experienced, professionally-trained, knowledgeable database administrator, more of a database user who's had to administer his own database systems because no one else's around. My organization is trying to decide on an SQL engine for general purpose database work within our organization. The one professional database administrator we have works mainly in MS Access, but is looking forward to building on her beginner-level understanding of SQL and becoming an SQL administrator. Right now, the largest database in our organization is a flat-file structure with less than 500,000 records in it, which could conceivably grow ten-fold in the next five years. The organization hired an outside consultant to evaluate which SQL engine to go with. This is what he sent to us: === MySQL is an open-source database management system (DBMS). It uses client/server architecture and is a multi-threaded, multi-user database server. MySQL was designed for speed; therefore, it does not provide many of the features provided by relational database systems, such as sub-queries, foreign keys, referential integrity, stored procedures, triggers, and views. In addition, it contains a locking mechanism that is not adequate for tables containing many write actions occurring simultaneously from different users. It is also lacking in reference to support for software applications and tools. SQL Server 2000 is a complete Relational Database Management System (RDBMS) that also includes integrated analysis functionality for OLAP and data mining. SQL Server 2000 meets the data and analysis storage requirements of the largest data processing systems and commercial Web sites, yet at the same time can provide easy-to-use data storage services to an individual or small business. The architecture of Microsoft SQL Server supports advanced server features, such as row-level locking, advanced query optimization, data replication, distributed database management, and Analysis Services. Transact-SQL (T-SQL) is the SQL dialect supported by SQL Server 2000. === I don't know whether the consultant wrote this himself, or if it came from somewhere. It could be Microsoft advertizement, for all I know. Most of the terms aren't familiar to me, like sub-queries or referential integrity. I feel out of my depth evaluating this comparison. Referential integrity is supported for InnoDB type tables - with MySQL, each table can have a different type, each table handler (in the MySQL system) can handle different features. With InnoDB, there's Referential integrity, transaction support and and also a different locking mechanism - which is more suited for lots of readers and concurrent writers. My questions are: 1. Is this a fair comparison of MySQL and MS SQL Server 2000? Fairly fair :-) 2. Is this up to date with the current status of MySQL? Triggers, views and Stored Procedures are expected to be included in MySQL 5. The next version of MSSQL will include a new locktype/transaction isolation, one which works pretty much the same as InnoDB - versioned locking. 3. Would the deficiencies pointed out in MySQL, if true, apply to the type of work we envision? Granted, I haven't given you all much information about what we hope to do with an SQL engine, but I don't think it will be very sophisticated. If it's not very sophisticated, MySQL will do just fine. In my opinion. There are other free and open source DBMSes as well, that do have procedures, triggers, views, subqueries and the like. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help comparing MySQL to MS SQL Server
Martijn, thank you very much for your analysis. I hope others will continue to join in. With regard to your point quoted below, are you referring to PostgreSQL, and would that be a stronger competitor to MS SQL Server 2000 than either the current version of MySQL or MySQL 5? Thanks, again, for your thoughts. -Kevin Martijn Tonies [EMAIL PROTECTED] 11/07/03 12:12PM 3. Would the deficiencies pointed out in MySQL, if true, apply to the type of work we envision? Granted, I haven't given you all much information about what we hope to do with an SQL engine, but I don't think it will be very sophisticated. If it's not very sophisticated, MySQL will do just fine. In my opinion. There are other free and open source DBMSes as well, that do have procedures, triggers, views, subqueries and the like. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help comparing MySQL to MS SQL Server
Hi Kevin, Martijn, thank you very much for your analysis. I hope others will continue to join in. So do I :-) With regard to your point quoted below, are you referring to PostgreSQL, and would that be a stronger competitor to MS SQL Server 2000 than either the current version of MySQL or MySQL 5? I have no experience with PostgreSQL - although, from what I've heard and read, it's quite capable - but not easy to get going on Windows. One other open source RDBMS would be Firebird - see www.firebirdsql.org Especially the newer release (1.5). Don't get fooled by that version number - it's a fork of the Borland InterBase code, which has been around for about 20 years now. I'm looking forward to MySQL5 to see what's new and how it's implemented. As for what engine would be the best for you - it all depends on what you're going to do. For example, I frequently use triggers and check constraints in my database applications, with MySQL, I can't do this. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help comparing MySQL to MS SQL Server
I have not work with it but postgres is supposed to work great in /BSD/Linux/Unix/solaris environment Which platform are you using? :-) Nestor A. Florez Martijn Tonies [EMAIL PROTECTED] 11/7/2003 10:08:53 AM Hi Kevin, Martijn, thank you very much for your analysis. I hope others will continue to join in. So do I :-) With regard to your point quoted below, are you referring to PostgreSQL, and would that be a stronger competitor to MS SQL Server 2000 than either the current version of MySQL or MySQL 5? I have no experience with PostgreSQL - although, from what I've heard and read, it's quite capable - but not easy to get going on Windows. One other open source RDBMS would be Firebird - see www.firebirdsql.org Especially the newer release (1.5). Don't get fooled by that version number - it's a fork of the Borland InterBase code, which has been around for about 20 years now. I'm looking forward to MySQL5 to see what's new and how it's implemented. As for what engine would be the best for you - it all depends on what you're going to do. For example, I frequently use triggers and check constraints in my database applications, with MySQL, I can't do this. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help comparing MySQL to MS SQL Server
It sounds like a copy and paste from Microsoft, but that is just my guess. An objective recommendation with show pluses and minuses of both. It most definitely does not sound like this consultant is qualified to suggest a database product. What about PostgresSQL, Oracle, Sybase, DB2? They all at least match the features of SQL Server, except maybe Postgres, and they run on more platforms and are more scalable. MySQL does have transaction support in the form of InnoDB tables. Sub-queries are now supported in v4, although not the fastest implementation. The other areas MySQL is lacking. When I first started using MySQL, subqueries were not supported and I found it frustrating. Since then I have truly acquired in depth knowledge of left and right joins and other techniques that I really didn't have before. I think I now write better, faster queries because I was forced to learn a new technique. I now have better knowledge of SQL. Not sure if that was a good or bad point. If your needs are simple, you can get by without stored procedures and triggers. Referential integrity can be enforced in your front-end code. Here is a question: how much would it cost to give everyone a copy of the database to play with? On their laptop, home computer? Nothing for MySQL or Postgres. What front-end will be used? Your options for SQL Server are kind of limited. On Friday, November 7, 2003, at 11:39 AM, KEVIN ZEMBOWER wrote: I don't know whether the consultant wrote this himself, or if it came from somewhere. It could be Microsoft advertizement, for all I know. Most of the terms aren't familiar to me, like sub-queries or referential integrity. I feel out of my depth evaluating this comparison. My questions are: 1. Is this a fair comparison of MySQL and MS SQL Server 2000? 2. Is this up to date with the current status of MySQL? 3. Would the deficiencies pointed out in MySQL, if true, apply to the type of work we envision? Granted, I haven't given you all much information about what we hope to do with an SQL engine, but I don't think it will be very sophisticated. Thank you for all your thoughts and comments. -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help comparing MySQL to MS SQL Server
Nestor, thanks for your question. The platform will actually be dictated by the SQL engine, not the other way around, which is more typically the case. If we go with MS SQL Server, we'll build a separate host, NT I would guess, to host it. I'm only responsible for Unix and Linux boxes here, so it'll be the responsibility of another group. If we go with MySQL or PostgreSQL (the only databases I have any familiarity with), I'll probably be responsible for setting up and configuring a new Linux (Debian) host, and maintaining it. The in-house database administer would be the administrator, and I would just offer any help that I could, which might not be much. Thanks, again, for writing. -Kevin Nestor Florez [EMAIL PROTECTED] 11/07/03 01:18PM I have not work with it but postgres is supposed to work great in /BSD/Linux/Unix/solaris environment Which platform are you using? :-) Nestor A. Florez Martijn Tonies [EMAIL PROTECTED] 11/7/2003 10:08:53 AM Hi Kevin, Martijn, thank you very much for your analysis. I hope others will continue to join in. So do I :-) With regard to your point quoted below, are you referring to PostgreSQL, and would that be a stronger competitor to MS SQL Server 2000 than either the current version of MySQL or MySQL 5? I have no experience with PostgreSQL - although, from what I've heard and read, it's quite capable - but not easy to get going on Windows. One other open source RDBMS would be Firebird - see www.firebirdsql.org Especially the newer release (1.5). Don't get fooled by that version number - it's a fork of the Borland InterBase code, which has been around for about 20 years now. I'm looking forward to MySQL5 to see what's new and how it's implemented. As for what engine would be the best for you - it all depends on what you're going to do. For example, I frequently use triggers and check constraints in my database applications, with MySQL, I can't do this. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help comparing MySQL to MS SQL Server
What about MySQL-max/SAPDB? I believe that it was completely omitted in the consultants report but has many of the features you need. I would also like to ask a question; do you need stored procedures, triggers or views for your application? There are a number of high volume, high quality sites that do very nicely without them. Why are you different? John Griffin -Original Message- From: KEVIN ZEMBOWER [mailto:[EMAIL PROTECTED] Sent: Friday, November 07, 2003 1:53 PM To: [EMAIL PROTECTED] Subject: Re: Need help comparing MySQL to MS SQL Server Nestor, thanks for your question. The platform will actually be dictated by the SQL engine, not the other way around, which is more typically the case. If we go with MS SQL Server, we'll build a separate host, NT I would guess, to host it. I'm only responsible for Unix and Linux boxes here, so it'll be the responsibility of another group. If we go with MySQL or PostgreSQL (the only databases I have any familiarity with), I'll probably be responsible for setting up and configuring a new Linux (Debian) host, and maintaining it. The in-house database administer would be the administrator, and I would just offer any help that I could, which might not be much. Thanks, again, for writing. -Kevin Nestor Florez [EMAIL PROTECTED] 11/07/03 01:18PM I have not work with it but postgres is supposed to work great in /BSD/Linux/Unix/solaris environment Which platform are you using? :-) Nestor A. Florez Martijn Tonies [EMAIL PROTECTED] 11/7/2003 10:08:53 AM Hi Kevin, Martijn, thank you very much for your analysis. I hope others will continue to join in. So do I :-) With regard to your point quoted below, are you referring to PostgreSQL, and would that be a stronger competitor to MS SQL Server 2000 than either the current version of MySQL or MySQL 5? I have no experience with PostgreSQL - although, from what I've heard and read, it's quite capable - but not easy to get going on Windows. One other open source RDBMS would be Firebird - see www.firebirdsql.org Especially the newer release (1.5). Don't get fooled by that version number - it's a fork of the Borland InterBase code, which has been around for about 20 years now. I'm looking forward to MySQL5 to see what's new and how it's implemented. As for what engine would be the best for you - it all depends on what you're going to do. For example, I frequently use triggers and check constraints in my database applications, with MySQL, I can't do this. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help comparing MySQL to MS SQL Server
kevin, i tend to think the consultant really just read something that microsoft sent him. it doesn't sound like he's qualified to suggest one database or another. We've been usinf mysql for a year now. We use InnoDB tables, which give us primary key/foreign key constraints and transactions. We've gotten around the lack of stored procedures by putting the necessary logic and checks into the application that inserts/updates the database. We have several tables with 8 million rows, and growing every day. updating rows on the big tables still shows approximately constant time performance. In general, we are extremely satisfied with the product, and have purchased a license (about $400 -- mainly so we can contribute to the cause). When 4.1 becomes stable, we will upgrade in order to get the sub-select capability. I came from an Oracle/Sybase background. Those products have features that mysql does not have, in particular DBA-specific tables, views, and triggers, but you may not need these features. happy to give you more information if you need it. jeff KEVIN ZEMBOWER wrote: Nestor, thanks for your question. The platform will actually be dictated by the SQL engine, not the other way around, which is more typically the case. If we go with MS SQL Server, we'll build a separate host, NT I would guess, to host it. I'm only responsible for Unix and Linux boxes here, so it'll be the responsibility of another group. If we go with MySQL or PostgreSQL (the only databases I have any familiarity with), I'll probably be responsible for setting up and configuring a new Linux (Debian) host, and maintaining it. The in-house database administer would be the administrator, and I would just offer any help that I could, which might not be much. Thanks, again, for writing. -Kevin Nestor Florez [EMAIL PROTECTED] 11/07/03 01:18PM I have not work with it but postgres is supposed to work great in /BSD/Linux/Unix/solaris environment Which platform are you using? :-) Nestor A. Florez Martijn Tonies [EMAIL PROTECTED] 11/7/2003 10:08:53 AM Hi Kevin, Martijn, thank you very much for your analysis. I hope others will continue to join in. So do I :-) With regard to your point quoted below, are you referring to PostgreSQL, and would that be a stronger competitor to MS SQL Server 2000 than either the current version of MySQL or MySQL 5? I have no experience with PostgreSQL - although, from what I've heard and read, it's quite capable - but not easy to get going on Windows. One other open source RDBMS would be Firebird - see www.firebirdsql.org Especially the newer release (1.5). Don't get fooled by that version number - it's a fork of the Borland InterBase code, which has been around for about 20 years now. I'm looking forward to MySQL5 to see what's new and how it's implemented. As for what engine would be the best for you - it all depends on what you're going to do. For example, I frequently use triggers and check constraints in my database applications, with MySQL, I can't do this. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Upgrading help please
Yup that worked - I rebooted my machine - and I was allowed in - Thank you very much Andrew -Original Message- From: Brian Snyder [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 3:30 PM Cc: MySQL Subject: Re: Upgrading help please Andrew, I had the same problem and had to stop and restart the servers. Give that a shot. brian On Wed, 2003-11-05 at 17:19, Andrew wrote: Good day List, I have just upgraded from 3.23 -4.0.16 I downloaded all the RPM's and then ran rpm -U *.rpm It did all that it was supposed to do, and then told me to use the /usr/bin/mysql_fix_privilege_tables script which I did got horrid errors ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) So I specified mysql_fix_privilege_tables root_password Same Errors (2002) Tried the other method that is on the mysql manual mysql_fix_privilege_tables --password=root_password Same error 2002. now Unfortunately I am unable to even connect to mysql with either mysqladmin or the client. MySQL-bench-4.0.16-0.i386.rpm MySQL-client-4.0.16-0.i386.rpm MySQL-devel-4.0.16-0.i386.rpm MySQL-embedded-4.0.16-0.i386.rpm MySQL-Max-4.0.16-0.i386.rpm MySQL-server-4.0.16-0.i386.rpm MySQL-shared-4.0.16-0.i386.rpm MySQL-shared-compat-4.0.16-0.i386.rpm is a list of the RPMS that I downloaded and ran. Any ideas and help would be most appreciated. Thank you Andrew Message sent using UebiMiau 2.7.2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading help please
Andrew, I had the same problem and had to stop and restart the servers. Give that a shot. brian On Wed, 2003-11-05 at 17:19, Andrew wrote: Good day List, I have just upgraded from 3.23 -4.0.16 I downloaded all the RPM's and then ran rpm -U *.rpm It did all that it was supposed to do, and then told me to use the /usr/bin/mysql_fix_privilege_tables script which I did got horrid errors ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) So I specified mysql_fix_privilege_tables root_password Same Errors (2002) Tried the other method that is on the mysql manual mysql_fix_privilege_tables --password=root_password Same error 2002. now Unfortunately I am unable to even connect to mysql with either mysqladmin or the client. MySQL-bench-4.0.16-0.i386.rpm MySQL-client-4.0.16-0.i386.rpm MySQL-devel-4.0.16-0.i386.rpm MySQL-embedded-4.0.16-0.i386.rpm MySQL-Max-4.0.16-0.i386.rpm MySQL-server-4.0.16-0.i386.rpm MySQL-shared-4.0.16-0.i386.rpm MySQL-shared-compat-4.0.16-0.i386.rpm is a list of the RPMS that I downloaded and ran. Any ideas and help would be most appreciated. Thank you Andrew Message sent using UebiMiau 2.7.2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help on WHERE ... LIKE Query
This should work for you: SELECT * FROM sometable WHERE surname BETWEEN 'A' AND 'D' ORDER BY surname In my quick test the first parameter is inclusive while the second is not, which is why it is D and not C. On Thursday, October 30, 2003, at 02:22 PM, Scott Brown wrote: I want to do a string comparison like this: SELECT * FROM sometable WHERE surname LIKE '[A-C]%' ORDER BY surname; -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help on WHERE ... LIKE Query
You can use RLIKE which is regular expressions then you should be able to execute SELECT * FROM sometable WHERE surname RLIKE '^[A-C]' ORDER BY surname; Kelley Scott Brown wrote: Hi, List, I looked here: http://www.mysql.com/doc/en/String_comparison_functions.html But I am not seeing what I need. I want to do a string comparison like this: SELECT * FROM sometable WHERE surname LIKE '[A-C]%' ORDER BY surname; This works in another RDBMS. It doesn't return a syntax error, either, but it returns no records. My guess is that MySQL is interpreting the whole thing literally, rather than looking for what I want. I need this to return all records where surname begins with the letters A through C (that is, all records with a surname which begins with A, B, or C). Anybody got a how-to? I'm sure there must be some way, other than to do this three times. Some of these can vary; that is, it may be 0-9, or 0-Z (show all), even, so I don't want to do a bunch of OR'ing, either. Thanks! --Scott Brown -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help on WHERE ... LIKE Query
Thanks for all of the responses! Actually, Brent Baisley wins the syntax question of the day. The BETWEEN syntax is what I needed. REGEXP and RLIKE do not return any records, they return a count of the number of rows matching the expression. Thanks! --Scott Brown At 11:22 AM 10/30/2003, you wrote: Hi, List, I looked here: http://www.mysql.com/doc/en/String_comparison_functions.html But I am not seeing what I need. I want to do a string comparison like this: SELECT * FROM sometable WHERE surname LIKE '[A-C]%' ORDER BY surname; This works in another RDBMS. It doesn't return a syntax error, either, but it returns no records. My guess is that MySQL is interpreting the whole thing literally, rather than looking for what I want. I need this to return all records where surname begins with the letters A through C (that is, all records with a surname which begins with A, B, or C). Anybody got a how-to? I'm sure there must be some way, other than to do this three times. Some of these can vary; that is, it may be 0-9, or 0-Z (show all), even, so I don't want to do a bunch of OR'ing, either. Thanks! --Scott Brown -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
