table is read only
I'm trying to update a table and I get this error message. ERROR 1036: Table 'list' is read only J. __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using LIKE to search for occurence of a column value in a string
I am having the same problem.
This Query:
SELECT 'haystack needle haystack' LIKE concat('%', 'needle', '%')
returns 1
However, this query does not work correctly If you generalize is to
include a database column:
SELECT * FROM MyTable WHERE 'haystack needle haystack' LIKE CONCAT('%',
NeedleColumn, '%')
This query does not work either:
SELECT * FROM MyTable WHERE LOCATE(NeedleColumn, 'haystack needle
haystack') 0
The two previous queries fail even when an exact match exists:
SELECT LOCATE('haystack needle haystack', 'haystack needle haystack')
returns 1
SELECT * FROM MyTable WHERE NeedleColumn = 'haystack needle haystack'
Returns
NeedleColumn
-
haystack needle haystack
The following query (incorrectly) returns no records:
SELECT * FROM MyTable WHERE LOCATE(NeedleColumn, 'haystack needle
haystack') 0
As far as I can tell, this is a bug. can someone suggest a workaround?
On Sunday, March 23, 2003, at 07:23 AM, Jakob Vedel Adeltoft wrote:
I wan't to search for all rows that match a value.
I have these rows:
URL Name
'http://www.microsoft.com/kb/' Microsoft Knowledgebase
'http://www.microsoft.com/search/' Microsoft Search
Now I wan't to find all occurences where any of above URL columns
exist in the string
'http://www.microsoft.com/kb/knowledgeb.asp?id=3strse=12'.
I tried to use LIKE:
SELECT URL, Name
FROM websites
WHERE 'http://www.microsoft.com/kb/knowledgeb.asp?id=3strse=12' LIKE
(URL + '%');
But this doesn't return any results. I would like the following as
output:
'http://www.microsoft.com/kb/' Microsoft Knowledgebase
Plz help me:)
/Jakob
--
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: Memory Leak
- Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Jeff Kilbride [EMAIL PROTECTED] Cc: Lopez David E-r9374c [EMAIL PROTECTED]; 'DeepBlue' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, March 28, 2003 10:05 PM Subject: Re: Memory Leak On Fri, Mar 28, 2003 at 10:46:52AM -0800, Jeff Kilbride wrote: I've heard some bad things about turning off swap on Linux. I think it was on Jeremy Z.'s Blogger page. If he sees this maybe he can comment. It really depends on the kernel too. Newer (2.4.19+) kernel are much better than those after 2.4.9. The great VM swap was a bad, bad idea. I agree with David, though, in that your key_buffer size is way too low. Did he ever say how large his indexes are? If not, how can you be sure of that? I can't be, other than making inferences from his original posting. He's trying to support 1000 connections on a box with 512MB of RAM and only giving 32MB to his key_buffer. If I was having problems in that situation, the key_buffer would be the first thing I would tune -- unless I had a *tiny* database. --jeff Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 53 days, processed 1,833,401,792 queries (394/sec. avg) -- 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: Using LIKE to search for occurence of a column value in a string
On Sat, 29 Mar 2003 05:43:53 -0500, Jeff Moore wrote:
However, this query does not work correctly If you generalize is to
include a database column:
SELECT * FROM MyTable WHERE 'haystack needle haystack' LIKE CONCAT('%',
NeedleColumn, '%')
Two things:
1. you don't seem to need the CONCAT() function. Just stick the % signs
around the string you're looking for.
2. your syntax just seems wrong. This should be:
SELECT * FROM MyTable WHERE NeedleColumn LIKE '%needle%';
to search for the string 'needle' anywhere in NeedleColumn, if that's
what you were looking for.
I'm jumping in mid-stream so excuse me if I'm confused.
--
Trevor Smith|[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Databases go away... But why?
On 3/29/03 12:53 AM, Jeremy Zawodny [EMAIL PROTECTED] wrote: Every so often, once every couple days or so, the databases just disappear. Doing a show databases command yields an empty set. Actually sometimes, it shows 1 or 2 in there. Any ideas as to what could be causing this, or how to troubleshoot it to narrow it down further? MySQL is not quitting apparently, the databases just disappear. Shutting down the service and restarting it fixes it. Any chance you're running FreeBSD? Well, we are running Mac OS X which is of course based on FreeBSD! So... I suspect you have some news for me :-) Thanks -- Bill Leonard [EMAIL PROTECTED] www.machinemen.com407.464.0147 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UPDATE ... FROM query
I have these tables: CREATE TABLE stack ( ProjectID INTEGER NOT NULL StatusID INTEGER NOT NULL ItemNumber INTEGER NOT NULL Stamp DATETIME NOT NULL ); CREATE TABLE queue ( ProjectID INTEGER NOT NULL StatusID INTEGER NOT NULL ItemNumber INTEGER NOT NULL Stamp DATETIME Wait BIT NOT NULL ); And these rows (date is DD-MM- hh:mm:ss): INSERT INTO stack (ProjectID, StatusID, ItemNumber, Stamp) VALUES(1, 3, 10, '28-03-2003 20:00:00'); INSERT INTO stack (ProjectID, StatusID, ItemNumber, Stamp) VALUES(1, 3, 10, '28-03-2003 20:15:00'); INSERT INTO stack (ProjectID, StatusID, ItemNumber, Stamp) VALUES(1, 3, 10, '28-03-2003 20:30:00'); INSERT INTO stack (ProjectID, StatusID, ItemNumber, Stamp) VALUES(1, 2, 10, '28-03-2003 17:00:00'); INSERT INTO stack (ProjectID, StatusID, ItemNumber, Stamp) VALUES(1, 1, 10, '28-03-2003 19:15:00'); INSERT INTO queue (ProjectID, StatusID, ItemNumber, Stamp, Wait) VALUES(1, 3, 10, '28-03-2003 19:15:00', 0); INSERT INTO queue (ProjectID, StatusID, ItemNumber, Stamp, Wait) VALUES(1, 2, 10, '28-03-2003 17:00:00', 0); INSERT INTO queue (ProjectID, StatusID, ItemNumber, Stamp, Wait) VALUES(1, 1, 10, NULL, 0); Now I wan't to UPDATE queue so it's stamp is set to the nearest higher stamp value in stack where ProjectID, StatusID and ItemNumber is equal to stack values. If stamp in queue is NULL then this should be set to the MIN(stamp) value in stack, based on the same criterias as before. Then wait should also be 1, for above criterias, but that's not that important now... The result rows in queue should look like this after doing the UPDATE: ProjectID StatusIDItemNumber Stamp Wait 1 3 10 28-03-2003 20:30:00 1 1 2 10 28-03-2003 17:00:00 0 1 1 10 28-03-2003 19:15:00 1 I've tried with this query (this is only for StatusID=3, but imagine this run for StatusID 1,2,3): UPDATE queue SET Wait = 1, stamp = S.stamp FROM stack S WHERE queue.ProjectID = S.ProjectID AND queue.ItemNumber = S.ItemNumber AND queue.StatusID = S.StatusID AND queue.Stamp S.Stamp AND queue.ProjectID = 1 AND queue.ItemNumber = 10 AND queue.Wait = 0 AND queue.StatusID = 3 Plz help me - I don't know how to solve this one... /Jakob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Union Problems? Me too!
On Friday 28 March 2003 19:29, James wrote: It seems that the union has problems when some values returned in the selects are nulls like in left joines. Instead of seeing Nulls the rows inherit the value of the previous row. Which make a real mess. I did how ever found a work around. I use coalesce(myfield,space(30)) as myfield anywhere a null may be returned. Seems to work ! It's fixed in 4.1, but it will not be ported to 4.0. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: table is read only
On Saturday 29 March 2003 10:32, Joseph Bannon wrote: I'm trying to update a table and I get this error message. ERROR 1036: Table 'list' is read only Is your table compressed with myisampack? Check also permissions on the table files. -- 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: Fulltext search -- no wildcards in phrases?
On Thursday 27 March 2003 20:59, Nick Arnett wrote: It appears to me that fulltext phrase searches cannot include wildcards. For example, I would expect app* serv* to match application server, application services, etc. But it returns no results, so I'm having to run each variation separately. Can anyone confirm that wildcards, indeed, can't be used in phrase searches. I'm doing these in Boolean mode because I need exact counts of occurrences. This is on MySQL-4.0.12-nt. Yes, phrase search currently doesn't work with operators. It's still in TODO. -- 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]
MySQL: Selecting a patricular row
Hello, I am working on a PHP/MySQL/Apache website. I have a MySQL table with 5 rows. I want to select a patricular row using it's absolute row number. How can I do this? I found mysql_row_seek() n te MySQL docs but this requires me to get the whole table and then lets me within the result set select the desired row. That's too much overhead - I want to use SQL to select a particular row using the row number thanks, Marc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL: Selecting a patricular row
On 3/29/03 7:55 AM, Marc Bakker [EMAIL PROTECTED] wrote: Hello, I am working on a PHP/MySQL/Apache website. I have a MySQL table with 5 rows. I want to select a patricular row using it's absolute row number. How can I do this? I found mysql_row_seek() n te MySQL docs but this requires me to get the whole table and then lets me within the result set select the desired row. That's too much overhead - I want to use SQL to select a particular row using the row number thanks, Marc Maybe I am missing something, but the fifth row is by no means an absolute thing, depending on what you order the records by. If you refer to the order in which the records where inserted into the table I would add an auto_increment field or if you already have another field that needs to be primary key add a timestamp field (but you might get identical values). With the auto increment field you could easily do: SELECT * FROM yourtable WHERE auto_increment_field = 5; I am not sure how reliable you can do a (see below) and get what you want. SELECT * FROM yourtable LIMIT 5, 1; http://www.mysql.com/doc/en/SELECT.html Sure, if you table will always contain 5 rows then it should be easy to just add a column 'rownumber' and give each row the number you want it to have. Cheers/h -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Databases go away... But why?
On Sat, Mar 29, 2003 at 07:53:32AM -0500, Bill Leonard wrote: On 3/29/03 12:53 AM, Jeremy Zawodny [EMAIL PROTECTED] wrote: Every so often, once every couple days or so, the databases just disappear. Doing a show databases command yields an empty set. Actually sometimes, it shows 1 or 2 in there. Any ideas as to what could be causing this, or how to troubleshoot it to narrow it down further? MySQL is not quitting apparently, the databases just disappear. Shutting down the service and restarting it fixes it. Any chance you're running FreeBSD? Well, we are running Mac OS X which is of course based on FreeBSD! So... I suspect you have some news for me :-) MySQL needs to be build with the HAVE_BROKEN_REALPATH flag. The realpath() on FreeBSD (and OS X) is not thread-safe. MySQL should auto-detect this when you build it. And the official binaries should be okay. But maybe you're using an old one? FWIW, the fix has been committed to FreeBSD 5.0. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 54 days, processed 1,848,966,807 queries (394/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Nested SQL QUERY
I hope someone can help me with a syntax error I'm getting with a nested
query. I'm a newbie.
Here is the statement:
SELECT *
FROM tblmarkets
WHERE MarketID IN
(SELECT MarketID
FROM tblmarketproducts, tblproducts
WHERE tblmarketproducts.ProductID = tblproducts.ProductID AND
ProductName = 'Apples')
ORDER BY MarketName
and I'm getting the following error message:
You have an error in your SQL syntax near 'SELECT MarketID
FROM tblmarketproducts, tblproducts
WHERE tblmarke' at line 4
I have 3 tables - tblmarkets, tblmarketproducts, and tblproducts. I am
trying to return the columns in tblmarkets based on the value of 'Apples'
(test value for a form variable). MarketID is the primary key in tblmarkets
which is related to MarketID in tblmarketproducts. The second column in
tblmarketproducts is ProductID which is related to ProductID in tblproducts.
The other column in tblproducts is ProductName which is the initial form
variable the query is based upon ('Apples' is the test variable for the
statement').
I have also tried a nested INNER JOIN statement:
SELECT tblMarkets.*, tblMarketProducts.*, tblProducts.ProductName
FROM tblProducts INNER JOIN (tblMarkets INNER JOIN tblMarketProducts ON
tblMarkets.MarketID = tblMarketProducts.MarketID) ON tblProducts.ProductID =
tblMarketProducts.ProductID
WHERE tblMarkets.ProductID = varProductID and MarketName LIKE 'varName%' AND
City Like 'varCity%' AND State LIKE 'varState%' AND County LIKE 'varCounty%'
AND Zip LIKE 'varZip%' AND Approved = '1'
ORDER BY MarketName
and get a syntax error in line 2.
I am at a loss and would ve grateful for any help.
Eric Leupold
RE: Nested SQL QUERY
Nested SQL statements are not supported except in insert statements in
MySQL (4.1 may include this feature, if not seems scheduled to be
included soon). You can accomplish this query using an inner join.
SELECT m.*
FROM tblmarkets m
INNER JOIN tblmarketproducts mp ON mp.MarketID = m.MarketID
INNER JOIN tblproducts p ON mp.ProductID = p.ProductID
WHERE p.ProductName='Apples';
I used the following database setup to test this:
DROP TABLE IF EXISTS tblmarkets;
CREATE TABLE IF NOT EXISTS tblmarkets (
MarketID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
MarketName VARCHAR(200)
);
DROP TABLE IF EXISTS tblmarketproducts;
CREATE TABLE IF NOT EXISTS tblmarketproducts (
ProductID INT UNSIGNED NOT NULL,
MarketID INT UNSIGNED NOT NULL,
PRIMARY KEY (MarketID, ProductID),
INDEX (ProductID)
);
DROP TABLE IF EXISTS tblproducts;
CREATE TABLE IF NOT EXISTS tblproducts (
ProductID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
ProductName VARCHAR(200) NOT NULL,
INDEX (ProductName)
);
INSERT INTO tblmarkets VALUES (NULL, Market1);
INSERT INTO tblmarkets VALUES (NULL, Market2);
INSERT INTO tblmarkets VALUES (NULL, Market3);
INSERT INTO tblproducts VALUES(NULL, Cherries);
INSERT INTO tblproducts VALUES(NULL, Apples);
INSERT INTO tblproducts VALUES(NULL, Pears);
INSERT INTO tblproducts VALUES(NULL, Appricots);
INSERT INTO tblmarketproducts VALUES(1, 1);
INSERT INTO tblmarketproducts VALUES(1, 3);
INSERT INTO tblmarketproducts VALUES(2, 2);
INSERT INTO tblmarketproducts VALUES(2, 3);
INSERT INTO tblmarketproducts VALUES(4, 2);
commit;
~Wynne Crisman
-Original Message-
From: Eric Leupold [mailto:[EMAIL PROTECTED]
Sent: Saturday, March 29, 2003 7:43 AM
To: [EMAIL PROTECTED]
Subject: Nested SQL QUERY
I hope someone can help me with a syntax error I'm getting with a nested
query. I'm a newbie.
Here is the statement:
SELECT *
FROM tblmarkets
WHERE MarketID IN
(SELECT MarketID
FROM tblmarketproducts, tblproducts
WHERE tblmarketproducts.ProductID = tblproducts.ProductID AND
ProductName = 'Apples') ORDER BY MarketName
and I'm getting the following error message:
You have an error in your SQL syntax near 'SELECT MarketID
FROM tblmarketproducts, tblproducts
WHERE tblmarke' at line 4
I have 3 tables - tblmarkets, tblmarketproducts, and tblproducts. I am
trying to return the columns in tblmarkets based on the value of
'Apples' (test value for a form variable). MarketID is the primary key
in tblmarkets which is related to MarketID in tblmarketproducts. The
second column in tblmarketproducts is ProductID which is related to
ProductID in tblproducts. The other column in tblproducts is ProductName
which is the initial form variable the query is based upon ('Apples' is
the test variable for the statement').
I have also tried a nested INNER JOIN statement:
SELECT tblMarkets.*, tblMarketProducts.*, tblProducts.ProductName FROM
tblProducts INNER JOIN (tblMarkets INNER JOIN tblMarketProducts ON
tblMarkets.MarketID = tblMarketProducts.MarketID) ON
tblProducts.ProductID = tblMarketProducts.ProductID WHERE
tblMarkets.ProductID = varProductID and MarketName LIKE 'varName%' AND
City Like 'varCity%' AND State LIKE 'varState%' AND County LIKE
'varCounty%' AND Zip LIKE 'varZip%' AND Approved = '1' ORDER BY
MarketName
and get a syntax error in line 2.
I am at a loss and would ve grateful for any help.
Eric Leupold
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using LIKE to search for occurence of a column value in a string
On Saturday, March 29, 2003, at 07:41 AM, Trevor Smith wrote:
2. your syntax just seems wrong. This should be:
SELECT * FROM MyTable WHERE NeedleColumn LIKE '%needle%';
to search for the string 'needle' anywhere in NeedleColumn, if that's
what you were looking for.
I'm jumping in mid-stream so excuse me if I'm confused.
The syntax was correct for the task.
Lets say for example, that you have a database of bad words:
CREATE TABLE BadWords (Word CHAR( 10 ) NOT NULL);
INSERT INTO BadWords (Word) VALUES ('Fudge');
INSERT INTO BadWords (Word) VALUES ('Dang');
INSERT INTO BadWords (Word) VALUES ('Darn');
You want to validate an arbitrary string against the database to find
out if it contains any bad words.
example string that contains a bad word: 'Darn Database
To find which bad words were used in the phrase you are checking, You
would issue a query like this:
SELECT * FROM BadWords WHERE 'Darn Database' LIKE CONCAT('%', Word, '%')
which returns
Word
Darn
When I just tried this small example, it worked perfectly which really
surprised me. I spent about 2 hours testing this every which way before
my previous post, too.
Looking into it more, I found that the reason that it didn't work
before was that I was using PHPMyAdmin under IE for the mac. I browsed
a table to get a key value to test with and copied the value I wanted
to search for from the browser and pasted it into my test query.
The string in my test that I thought I was using was:
Mozilla/4.0 (compatible; MSIE 5.02; Windows 98)
When I pasted this string into my text editor and told it to add escape
codes for hidden characters, I got this:
Mozilla/4.0\0xCA(compatible;\0xCAMSIE\0xCA5.02;\0xCAWindows\0xCA98)
It seems that PHPMyAdmin converts spaces in the query results into
nbsp; entities for html display. On the Mac with IE, when you copy a
string that contains nbsp; from the browser, it seems to get converted
into the character \0xCA instead of the normal space character.
Everything works fine if I search for the correct string containing
normal spaces.
I feel stupid for not double checking my queries outside of PHPMyAdmin
before posting my last message.
Does this help you, Jakob?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL-Max
I'm running a recently up2dated RH 7.2 machine. I just downloaded all the My SQL 4x. RPMS from the MYSQL site: MySQL-4.0.1-2.i386.rpm MySQL-embedded-4.0.12-0.i386.rpm MySQL-bench-4.0.12-0.i386.rpm MySQL-Max-4.0.12-0.i386.rpm MySQL-client-4.0.12-0.i386.rpm MySQL-shared-4.0.12-0.i386.rpm MySQL-devel-4.0.12-0.i386.rpm Mysql starts up fine and runs except when I install the MySQL-Max rpm. When install the rpm I get the following error message: Preparing...### [100%] 1:MySQL-Max ### [100%] Restarting mysqld. error: execution of %post scriptlet from MySQL-Max-4.0.12-0 failed, exit status 1 Then when I try to start Mysql I get the following error message: [EMAIL PROTECTED] root]# service mysql start [EMAIL PROTECTED] root]# Starting mysqld-max daemon with databases from /var/lib/mysql 030329 15:38:10 mysqld ended I wanted the MySQL-Max so I use SSL connections to the server. Anyone have any ideas what I am doing wrong? :) Dave -- ...Unix, MS-DOS and Windows NT (also known as the Good, the Bad, and the Ugly) OSIS Dave J. Hala Jr. 641.485.1606 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Nested SQL QUERY
HI
MySQL does not support sub selects/nested queries :(
Peter
-Original Message-
From: Eric Leupold [mailto:[EMAIL PROTECTED]
Sent: 29 March 2003 16:43
To: [EMAIL PROTECTED]
Subject: Nested SQL QUERY
I hope someone can help me with a syntax error I'm getting with a nested
query. I'm a newbie.
Here is the statement:
SELECT *
FROM tblmarkets
WHERE MarketID IN
(SELECT MarketID
FROM tblmarketproducts, tblproducts
WHERE tblmarketproducts.ProductID = tblproducts.ProductID AND
ProductName = 'Apples')
ORDER BY MarketName
and I'm getting the following error message:
You have an error in your SQL syntax near 'SELECT MarketID
FROM tblmarketproducts, tblproducts
WHERE tblmarke' at line 4
I have 3 tables - tblmarkets, tblmarketproducts, and tblproducts. I am
trying to return the columns in tblmarkets based on the value of 'Apples'
(test value for a form variable). MarketID is the primary key in tblmarkets
which is related to MarketID in tblmarketproducts. The second column in
tblmarketproducts is ProductID which is related to ProductID in tblproducts.
The other column in tblproducts is ProductName which is the initial form
variable the query is based upon ('Apples' is the test variable for the
statement').
I have also tried a nested INNER JOIN statement:
SELECT tblMarkets.*, tblMarketProducts.*, tblProducts.ProductName
FROM tblProducts INNER JOIN (tblMarkets INNER JOIN tblMarketProducts ON
tblMarkets.MarketID = tblMarketProducts.MarketID) ON tblProducts.ProductID =
tblMarketProducts.ProductID
WHERE tblMarkets.ProductID = varProductID and MarketName LIKE 'varName%' AND
City Like 'varCity%' AND State LIKE 'varState%' AND County LIKE 'varCounty%'
AND Zip LIKE 'varZip%' AND Approved = '1'
ORDER BY MarketName
and get a syntax error in line 2.
I am at a loss and would ve grateful for any help.
Eric Leupold
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cannot connect to mysql server
Hello, I have an interesting problem. I installed mysql client and server from Redhat 7.3 RPM. Then I set up a username and password for a user monty (to use example from the manual). Then I grant priveleges for the user with: mysql GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] - IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysql GRANT ALL PRIVILEGES ON *.* TO monty@% - IDENTIFIED BY 'some_pass' WITH GRANT OPTION; and restarted mysql server. Let's assume the server name is sql.domain.edu I then tried to connect to the server as user monty from localhost: $ mysql -u monty -p and then tried it from another machine console: $ mysql -u monty -h sql.domain.edu -p and all is well. But when i tried to connect using the console from localhost (ie. sql.domain.edu) by specifiying hostname: $ mysql -u monty -h sql.domain.edu -p I got the cannot connect user [EMAIL PROTECTED] error message. Why does this happen? I fixed this by doing mysql GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] - IDENTIFIED BY 'some_pass' WITH GRANT OPTION; but I thought I already specified the wildcard on my previous grant previlege so that monty can access from *any* machine. Just curious as t why this happens, can anyone help? Thanks a lot. Reuben D. Budiardja -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: embedded select / left join question
Jim Miller wrote: I want to do a join and select of these tables that will give me all the Entrants who did NOT enter contest 1 (i.e., Mary and Bill). Try: select entrant.name from entrant left join contestEntries on entrant.id = contestEntries.entrant AND contestEntries.ContestNumber = 1 where contestEntries.entrant is null Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OS X install for Unix dummy
I just downloaded and installed the mysql-max-4.0.12.pkg, ran cd /usr/local/mysql sudo ./bin/mysqld_safe (Enter your password) (Press CTRL+Z) (Press CTRL+D to exit the shell) bg as specified in the readme. When attempting to set the root password, I successfully ran /usr/local/mysql/bin/mysqladmin -u root password 'new-password' but it choked on: /usr/local/mysql/bin/mysqladmin -u root -h $hostname password 'new-password' with the error: hostname: Undefined variable. Problem is, I'm such a Unix newbie I don't know how to retrieve the hostname it is looking for. I tried the name of my computer and my user name but those didn't work. What is it looking for? Thanks Ken Tozier -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
get table name from select staement as output as field
hi guys is this possible ? select tablename1 as table1, id from tablename1 , tablename2 ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: get table name from select staement as output as field
On 30-Mar-2003 Daniel Rossi wrote: hi guys is this possible ? select tablename1 as table1, id from tablename1 , tablename2 ? select 'tablename1' as table1, id from ... Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
