Problem using IN statement MySQL 5
Hello, I'm trying to run q query with an IN statement in MySQL 5. Like so: SELECT * from encore enc, article art WHERE enc.encore_id= '10' AND `article_id` IN (`articles`) Its should return all the articles in the encore.articles column but instead only returns the first article. In encore,articles is the data 43,44,45,46. These are article IDs. If I manually place 43,44,45,46 into the query like so: SELECT * from encore enc, article art WHERE enc.encore_id= '10' AND `article_id` IN (43,44,45,46) All 4 articles are returned. Any ideas why this is not working? TIA! -- Paul Nowosielski Webmaster 2401 Broadway St Boulder, Co 80304 Tel: 303.440.0666 ext:219 Cell: 303.827.4257 www.celebrityaccess.com www.protouronline.com www.boxofficenetwork.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem using IN statement MySQL 5
Your select has two tables, but you don't join them. Can you provide the table info, and a subset of the data that should be pulled back by this query? David Paul Nowosielski wrote: Hello, I'm trying to run q query with an IN statement in MySQL 5. Like so: SELECT * from encore enc, article art WHERE enc.encore_id= '10' AND `article_id` IN (`articles`) Its should return all the articles in the encore.articles column but instead only returns the first article. In encore,articles is the data 43,44,45,46. These are article IDs. If I manually place 43,44,45,46 into the query like so: SELECT * from encore enc, article art WHERE enc.encore_id= '10' AND `article_id` IN (43,44,45,46) All 4 articles are returned. Any ideas why this is not working? TIA! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem using IN statement MySQL 5
Paul, SELECT * from encore enc, article art WHERE enc.encore_id= '10' AND `article_id` IN (`articles`) First, IN() accepts either a literal value list, or a subquery which returns a value list. Just passing it a table name won't work. Second, your join looks suspicious. Is this what you mean?... SELECT * FROM encore AS enc INNER JOIN article AS art USING (article_id) WHERE enc.encore_id= 10; PB - Paul Nowosielski wrote: Hello, I'm trying to run q query with an IN statement in MySQL 5. Like so: SELECT * from encore enc, article art WHERE enc.encore_id= '10' AND `article_id` IN (`articles`) Its should return all the articles in the encore.articles column but instead only returns the first article. In encore,articles is the data 43,44,45,46. These are article IDs. If I manually place 43,44,45,46 into the query like so: SELECT * from encore enc, article art WHERE enc.encore_id= '10' AND `article_id` IN (43,44,45,46) All 4 articles are returned. Any ideas why this is not working? TIA! -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.17/226 - Release Date: 1/10/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem using IN statement MySQL 5
Paul Nowosielski [EMAIL PROTECTED] wrote on 01/11/2006 02:41:05 PM: Hello, I'm trying to run q query with an IN statement in MySQL 5. Like so: SELECT * from encore enc, article art WHERE enc.encore_id= '10' AND `article_id` IN (`articles`) Its should return all the articles in the encore.articles column but instead only returns the first article. In encore,articles is the data 43,44,45,46. These are article IDs. If I manually place 43,44,45,46 into the query like so: SELECT * from encore enc, article art WHERE enc.encore_id= '10' AND `article_id` IN (43,44,45,46) All 4 articles are returned. Any ideas why this is not working? TIA! -- Paul Nowosielski Webmaster 2401 Broadway St Boulder, Co 80304 Tel: 303.440.0666 ext:219 Cell: 303.827.4257 www.celebrityaccess.com www.protouronline.com www.boxofficenetwork.com They look similar but an actual list of numeric values is not the same as a string containing a list of numeric values. Your `articles` column in your `encore` table contains the single string 43,44,45,46 which converts to a single numeric value of 43. That's why you only see the one row returned. What you want to be able to do is do treat that string as separate values. One way to do this with MySQL is with the function FIND_IN_SET() but that will negate the use of indexes. Check out the other SET and string related functions as parts of alternate solutions. http://dev.mysql.com/doc/refman/5.0/en/set.html http://dev.mysql.com/doc/refman/5.0/en/string-functions.html The better solution is to re-organize your data so that there is a third table that matches up encores to articles. This two-column table could contain millions of entries and you might think this will slow things down but the engine will be able to use indexes and your queries will actually move MUCH faster. Even on a few hundred entries you will be able to notice that FIND_IN_SET() will be slow. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: problem with update statement
That is very odd behavior and shouldn't be happening. I ran a quick test on my machine and MySQL does not exhibit that behavior. You may try running and repair on your table. Perhaps something is out of whack. What version of MySQL are you running? On Apr 22, 2005, at 6:08 PM, [EMAIL PROTECTED] wrote: Hello, all: In a MyISAM table, I have a column named MAC, of type VARCHAR(17). This field is used to hold MAC addresses of computers' network interface cards. These MAC addresses are in the form XX:XX:XX:XX:XX:XX, where X can be either a number or an uppercase letter. I can run select * from table where MAC='00:04:FB:23:5A:44' and the correct record is returned. However, performing update table set port_index='123' where MAC='00:04:FB:23:5A:44' does not work as I expected. It does update the correct record, but also updates all other records whose MAC field is empty. When I originally created this field, I used type VARCHAR (17), null, default value NULL. In troubleshooting this, I have since change the type to CHAR(17), not null, empty default value. Still have the same problem. Does anyone know what's going on here, and how I could correct this? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- 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: problem with update statement
Hi,
this is an example where what you describe doesn't happen (v 4.1 and 5.0) :
mysql create table ports(mac varchar(17),port_index varchar(3));
Query OK, 0 rows affected (0.10 sec)
mysql insert into ports values('00:04:FB:23:5A:44','120'),(NULL,'120');
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql select * from ports;
+---++
| mac | port_index |
+---++
| 00:04:FB:23:5A:44 | 120|
| NULL | 120|
+---++
2 rows in set (0.00 sec)
mysql select * from ports where mac='00:04:FB:23:5A:44';
+---++
| mac | port_index |
+---++
| 00:04:FB:23:5A:44 | 120|
+---++
1 row in set (0.00 sec)
mysql
mysql update ports set port_index='123' where mac='00:04:FB:23:5A:44';
Query OK, 1 row affected (0.41 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql select * from ports;
+---++
| mac | port_index |
+---++
| 00:04:FB:23:5A:44 | 123|
| NULL | 120|
+---++
2 rows in set (0.00 sec)
mysql
if you use an old version which is buggy, just migrate.
Mathias
Selon Brent Baisley [EMAIL PROTECTED]:
That is very odd behavior and shouldn't be happening. I ran a quick
test on my machine and MySQL does not exhibit that behavior. You may
try running and repair on your table. Perhaps something is out of
whack. What version of MySQL are you running?
On Apr 22, 2005, at 6:08 PM, [EMAIL PROTECTED] wrote:
Hello, all:
In a MyISAM table, I have a column named MAC, of type VARCHAR(17).
This field is used to hold MAC addresses of computers' network
interface cards. These MAC addresses are in the
form XX:XX:XX:XX:XX:XX, where X can be either a number or an
uppercase letter. I can run select * from table where
MAC='00:04:FB:23:5A:44' and the correct record is returned. However,
performing update table set port_index='123' where
MAC='00:04:FB:23:5A:44' does not work as I expected. It does update
the correct record, but also updates all other records whose MAC field
is empty. When I originally created this field, I used type VARCHAR
(17), null, default value NULL. In troubleshooting this, I have since
change the type to CHAR(17), not null, empty default value. Still
have the same problem. Does anyone know what's going on here, and how
I could correct this?
Thanks!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with update statement
Thanks for the info. I am using 4.0.24, so maybe this is the problem.
I was able to get around this by first checking if the mac field was
empty, but I will look into upgrading mysql to a more current version.
thanks
- Original Message -
From: [EMAIL PROTECTED]
Date: Monday, April 25, 2005 9:26 am
Subject: Re: problem with update statement
Hi,
this is an example where what you describe doesn't happen (v 4.1
and 5.0) :
mysql create table ports(mac varchar(17),port_index varchar(3));
Query OK, 0 rows affected (0.10 sec)
mysql insert into ports
values('00:04:FB:23:5A:44','120'),(NULL,'120');Query OK, 2 rows
affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql select * from ports;
+---++
| mac | port_index |
+---++
| 00:04:FB:23:5A:44 | 120|
| NULL | 120|
+---++
2 rows in set (0.00 sec)
mysql select * from ports where mac='00:04:FB:23:5A:44';
+---++
| mac | port_index |
+---++
| 00:04:FB:23:5A:44 | 120|
+---++
1 row in set (0.00 sec)
mysql
mysql update ports set port_index='123' where
mac='00:04:FB:23:5A:44';Query OK, 1 row affected (0.41 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql select * from ports;
+---++
| mac | port_index |
+---++
| 00:04:FB:23:5A:44 | 123|
| NULL | 120|
+---++
2 rows in set (0.00 sec)
mysql
if you use an old version which is buggy, just migrate.
Mathias
Selon Brent Baisley [EMAIL PROTECTED]:
That is very odd behavior and shouldn't be happening. I ran a quick
test on my machine and MySQL does not exhibit that behavior. You
may
try running and repair on your table. Perhaps something is out of
whack. What version of MySQL are you running?
On Apr 22, 2005, at 6:08 PM, [EMAIL PROTECTED] wrote:
Hello, all:
In a MyISAM table, I have a column named MAC, of type
VARCHAR(17). This field is used to hold MAC addresses of
computers' network
interface cards. These MAC addresses are in the
form XX:XX:XX:XX:XX:XX, where X can be either a number or an
uppercase letter. I can run select * from table where
MAC='00:04:FB:23:5A:44' and the correct record is returned.
However, performing update table set port_index='123' where
MAC='00:04:FB:23:5A:44' does not work as I expected. It does
update the correct record, but also updates all other records
whose MAC field
is empty. When I originally created this field, I used type
VARCHAR (17), null, default value NULL. In troubleshooting
this, I have since
change the type to CHAR(17), not null, empty default value.
Still
have the same problem. Does anyone know what's going on here,
and how
I could correct this?
Thanks!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with update statement
Hello, all: In a MyISAM table, I have a column named MAC, of type VARCHAR(17). This field is used to hold MAC addresses of computers' network interface cards. These MAC addresses are in the form XX:XX:XX:XX:XX:XX, where X can be either a number or an uppercase letter. I can run select * from table where MAC='00:04:FB:23:5A:44' and the correct record is returned. However, performing update table set port_index='123' where MAC='00:04:FB:23:5A:44' does not work as I expected. It does update the correct record, but also updates all other records whose MAC field is empty. When I originally created this field, I used type VARCHAR (17), null, default value NULL. In troubleshooting this, I have since change the type to CHAR(17), not null, empty default value. Still have the same problem. Does anyone know what's going on here, and how I could correct this? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with insert statement; ERROR 1030 at line 188: Got error 28 from table handler
Hi Sebastian, If the new cds_catalog is created with primary key, this should produce a duplicate key error for the second iteration of the second table, as the data selected is from cds_catalog alone, but joining two tables causing cartisian joint to be formed(n*(m- t1.fieldt2.field)), each time the same set of data being inserted. reg, Eldo. On Mon, 27 Sep 2004 14:07:54 +0200 (CEST), Tobias Asplund [EMAIL PROTECTED] wrote: On Mon, 27 Sep 2004, Sebastian Geib wrote: I have a huge problem with the following insert statement: INSERT INTO cds_catalog SELECT cds_stage.cds_catalog.* FROM cds.cds_catalog, cds_stage.cds_catalog WHERE cds_stage.cds_catalog.prodidcds.cds_catalog.prodid; Look at this query, it will create a huge table as a result, as an approximisation the table created will have the number of rows in both tables multiplied with eachother. Has anyone else any idea? I tried all Google resources I could get hands on, but they were all about disk space on the tmp partition or repairing the db which both isn't the problem here. Are you sure 60GB is enough? Look above, say you have 1000 rows in each table, the result could be up to 100 rows. -- 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]
Problem with insert statement; ERROR 1030 at line 188: Got error 28 from table handler
Hi! I have a huge problem with the following insert statement: INSERT INTO cds_catalog SELECT cds_stage.cds_catalog.* FROM cds.cds_catalog, cds_stage.cds_catalog WHERE cds_stage.cds_catalog.prodidcds.cds_catalog.prodid; Whenever I'm running it, it produces the error mentioned above and sadly it has nothing to do with either the memory nor the hdd space because on the hdd there's still more than 60% free and there's about 500 Megs of RAM free during the transaction. Hopefully anyone can help me because I don't know how to work around this transaction. Any hint is appreciated. Thanks in advance. Best regards, Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with insert statement; ERROR 1030 at line 188: Got error 28 from table handler
Sebastian Geib wrote: Hi! I have a huge problem with the following insert statement: INSERT INTO cds_catalog SELECT cds_stage.cds_catalog.* FROM cds.cds_catalog, cds_stage.cds_catalog WHERE cds_stage.cds_catalog.prodidcds.cds_catalog.prodid; Whenever I'm running it, it produces the error mentioned above and sadly it has nothing to do with either the memory nor the hdd space because on the hdd there's still more than 60% free and there's about 500 Megs of RAM free during the transaction. Hopefully anyone can help me because I don't know how to work around this transaction. Any hint is appreciated. Thanks in advance. Best regards, Sebastian Sebastian, Just googling it appears either one of the tables needs to be repaired or you're running of disk space for tmp tables. walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem wil insert statement merging values
* dan orlic
INSERT INTO cp.Items SELECT distinct g.RecordID as id,'' as
category_id, '' as pattern_id,'' as manufacturer_id, g.Item + g.Desc1
+ g.Desc2 + g.Desc3 as description, g.Desc4 as price,0 as quantity, ''
as comments,'Active' as status,'n' AS is_bridal, 'Gallery' AS type,
now() as created, now() as last_modified FROM copperlamp.Items g order
by g.RecordID asc;
and it does not error out, but there are problems with this... for
example:
... g.Desc4 as price ... g.Desc4 ($500.00) is a varchar and price is a
BigDecimal(10.2). but when it gets inserted the value is 0.00 for
every field
The string $500.00 is easily identified as a price for a human, but mysql
don't know that $ means money. In general mysql will try to convert a
string to a number if the string is used in a numeric context, but a string
starting with $ is not identified as a number:
mysql select $500.00+0,500.00+0,mid($500.00,2)+0;
+-+++
| $500.00+0 | 500.00+0 | mid($500.00,2)+0 |
+-+++
| 0 |500 |500 |
+-+++
1 row in set (0.00 sec)
You could use MID(g.Desc4,2) in your statement to make mysql ignore the $
character.
that's one the other is:
...g.Item + g.Desc1 + g.Desc2 + g.Desc3 as description ... but though
they are all varchars i can't seem to incorporate all the values into
that one field. any thoughts would be great.
See the CONCAT() function:
URL: http://dev.mysql.com/doc/mysql/en/String_functions.html#IDX1246
CONCAT(g.Item,g.Desc1,g.Desc2,g.Desc3) as description
Often one would like a space between the columns that are concatenated, in
that case CONCAT_WS() is what you want:
CONCAT_WS(' ',g.Item,g.Desc1,g.Desc2,g.Desc3) as description
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem wil insert statement merging values
dan orlic wrote: INSERT INTO cp.Items SELECT distinct g.RecordID as id,'' as category_id, '' as pattern_id,'' as manufacturer_id, g.Item + g.Desc1 + g.Desc2 + g.Desc3 as description, g.Desc4 as price,0 as quantity, '' as comments,'Active' as status,'n' AS is_bridal, 'Gallery' AS type, now() as created, now() as last_modified FROM copperlamp.Items g order by g.RecordID asc; and it does not error out, but there are problems with this... for example: ... g.Desc4 as price ... g.Desc4 ($500.00) is a varchar and price is a BigDecimal(10.2). but when it gets inserted the value is 0.00 for every field that's one the other is: ...g.Item + g.Desc1 + g.Desc2 + g.Desc3 as description ... but though they are all varchars i can't seem to incorporate all the values into that one field. any thoughts would be great. dan -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with 'OR' statement
Could someone have a look at this syntax and give me some guidance what I may be overlooking? SELECT * from $TableName WHERE machinename != 'FIND_ME' OR machinename != 'OPEN' I can make the statement work individually, but when I try to add the 'OR' statement it fails to 'remove' the designated records from the display page. I have tried moving the 'FIND_ME' and 'OPEN' around and still get the same results. Any help would be appreciated Jess --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.558 / Virus Database: 350 - Release Date: 1/2/04 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with 'OR' statement
Am Friday 09 January 2004 17:22 schrieb Hunter, Jess: Could someone have a look at this syntax and give me some guidance what I may be overlooking? SELECT * from $TableName WHERE machinename != 'FIND_ME' OR machinename != 'OPEN' A query like where x=a or s=b will always return all values. Why? Because the value is always differen from at least a or b. So such a query is nonsense. You want all records unequal to a and b, i. e. everything except a and b? Use and. Stefan I can make the statement work individually, but when I try to add the 'OR' statement it fails to 'remove' the designated records from the display page. I have tried moving the 'FIND_ME' and 'OPEN' around and still get the same results. Any help would be appreciated Jess --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.558 / Virus Database: 350 - Release Date: 1/2/04 -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zlpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with 'OR' statement
Am Friday 09 January 2004 17:22 schrieb Hunter, Jess: Could someone have a look at this syntax and give me some guidance what I may be overlooking? SELECT * from $TableName WHERE machinename != 'FIND_ME' OR machinename != 'OPEN' Some other thing: Instead of saying where x!=a and x!=b you could also say where !(x==a or x==b). That's known as de Morgan's law. (sometimes the math course at university pays off, surprise!) Stefan I can make the statement work individually, but when I try to add the 'OR' statement it fails to 'remove' the designated records from the display page. I have tried moving the 'FIND_ME' and 'OPEN' around and still get the same results. Any help would be appreciated Jess --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.558 / Virus Database: 350 - Release Date: 1/2/04 -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zlpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with 'OR' statement
Am Friday 09 January 2004 17:57 schrieb Stefan Kuhn: Am Friday 09 January 2004 17:22 schrieb Hunter, Jess: Could someone have a look at this syntax and give me some guidance what I may be overlooking? SELECT * from $TableName WHERE machinename != 'FIND_ME' OR machinename != 'OPEN' A query like where x=a or s=b will always return all values. Why? Because Sorry, should read where x!=a or s!=b as in your mail. Stefan the value is always differen from at least a or b. So such a query is nonsense. You want all records unequal to a and b, i. e. everything except a and b? Use and. Stefan I can make the statement work individually, but when I try to add the 'OR' statement it fails to 'remove' the designated records from the display page. I have tried moving the 'FIND_ME' and 'OPEN' around and still get the same results. Any help would be appreciated Jess --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.558 / Virus Database: 350 - Release Date: 1/2/04 -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zlpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with 'OR' statement
Jess, You should use an AND () instead of the OR. You result is everything, correct? When the query is doing the machinename != 'FIND_ME', the record(s) with machinename = 'OPEN' are being returned. Likewise, when the machinename != 'OPEN' is being performed, the records(s) with machinename = 'FIND_ME' will be returned. Thus, every record is being returned. If you use AND the query will return your intended result, all the records where machinename != 'FIND_ME' AND machinename != 'OPEN'. HTH Matt At 10:22 AM 1/9/2004, you wrote: Could someone have a look at this syntax and give me some guidance what I may be overlooking? SELECT * from $TableName WHERE machinename != 'FIND_ME' OR machinename != 'OPEN' I can make the statement work individually, but when I try to add the 'OR' statement it fails to 'remove' the designated records from the display page. I have tried moving the 'FIND_ME' and 'OPEN' around and still get the same results. Any help would be appreciated Jess --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.558 / Virus Database: 350 - Release Date: 1/2/04 -- 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: Problem with 'OR' statement
Your current statement, with the OR, says to select a record if its name
is not FIND_ME or if its name is not OPEN. That will be all of them. I
expect you want all of them except the ones named FIND_ME and OPEN. So,
you want AND instead of OR, because logically, NOT (this OR that) =
NOT this AND NOT that. Try
SELECT * FROM $TableName
WHERE machinename != 'FIND_ME' AND machinename != 'OPEN'
Alternatively, you could do
SELECT * FROM $TableName
WHERE machinename NOT IN ('FIND_ME', 'OPEN')
Michael
Hunter, Jess wrote:
Could someone have a look at this syntax and give me some guidance what I
may be overlooking?
SELECT * from $TableName WHERE machinename != 'FIND_ME' OR machinename !=
'OPEN'
I can make the statement work individually, but when I try to add the 'OR'
statement it fails to 'remove' the designated records from the display page.
I have tried moving the 'FIND_ME' and 'OPEN' around and still get the same
results.
Any help would be appreciated
Jess
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.558 / Virus Database: 350 - Release Date: 1/2/04
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with SQL Statement
Hello,
i have tryed a lot but don´t find a way for the following problem
with my SQL Statement.
I have 3 tabels and will make a fulltext search on this 3 tables. My
fulltext indexes are propper set. On a hit i will see the datas from table1
with the main informations (the other 2 tables have only aditional
informations for some rows in table 1.
I have tried to make my select statement with 2 tables, to make it not to
complex while I try to analyze the problem.
Here a statment, how I tryed it:
SELECT DISTINCT table1.* FROM table1LEFT JOIN table2 ON
(table1.id=table2.table1_id) WHERE MATCH(table1.firma,table1.nachname)
AGAINST ('Maier') OR MATCH(table2.vorname,table2.nachname) AGAINST
('Maier');
I hope everybody understand my problem, my english is not good enough to
explain it better.
Thanks for you help,
Oliver N.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with SQL Statement
Hello,
i have tryed a lot but don´t find a way for the following problem
with my SQL Statement.
I have 3 tabels and will make a fulltext search on this 3 tables. My
fulltext indexes are propper set. On a hit i will see the datas from table1
with the main informations (the other 2 tables have only aditional
informations for some rows in table 1.
I have tried to make my select statement with 2 tables, to make it not to
complex while I try to analyze the problem.
Here a statment, how I tryed it:
SELECT DISTINCT table1.* FROM table1LEFT JOIN table2 ON
(table1.id=table2.table1_id) WHERE MATCH(table1.firma,table1.nachname)
AGAINST ('Maier') OR MATCH(table2.vorname,table2.nachname) AGAINST
('Maier');
I hope everybody understand my problem, my english is not good enough to
explain it better.
Thanks for you help,
Oliver N.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Problem with UPDATE statement
In what way is it mangling my query? and why won't the same query work from my php script. I would accept this if it was just any interface but myphpadmin is supposed to be in line with mysql standards. update cdrequest set albumfill = hot shots II, releasedatefill = 20010708 where requstID = 2; i get: You have an error in your SQL syntax near '\hot shots II\, releasedatefill = 20010708 where requstID = 2' at line 1 I can't see any reason for the error. Your web based interface is mangling your query. part 2: when I flip to properties for the cdrequest just to make sure i'm spelling the columns corectly and cut and paste the exact same query on that page IT WORKS Why would it work on that page but not the other. both are supposed to be places where you can enter a normal sql query.. Because your web based interface is mangling your query. Try using the mysql command line client. It also doesn't work in my PHP script which is where I really want it to work. Paste source. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with UPDATE statement
On Fri, Aug 10, 2001 at 02:19:21AM -0400, Julian Simpson wrote: In what way is it mangling my query? and why won't the same query work from my php script. I would accept this if it was just any interface but myphpadmin is supposed to be in line with mysql standards. update cdrequest set albumfill = hot shots II, releasedatefill = 20010708 where requstID = 2; i get: You have an error in your SQL syntax near '\hot shots II\, releasedatefill = 20010708 where requstID = 2' at line 1 I can't see any reason for the error. Your web based interface is mangling your query. Hi Julian. I'd recommend taking the suggestion and try the query through the command-line interface. If it works there, then you've isolated the problem to the interface you're using. Divide and conquer. BTW, should requstID be requestID? -- Maurice Aubrey [EMAIL PROTECTED] Watch the costs and the profits will follow. - Andrew Carnegie MySQL 3.22.32: up 21.9 days, processed 1,514,328,327 queries (799/sec. avg.) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with UPDATE statement
In what way is it mangling my query? and why won't the same query work from my php script. I would accept this if it was just any interface but myphpadmin is supposed to be in line with mysql standards. update cdrequest set albumfill = hot shots II, releasedatefill = 20010708 where requstID = 2; i get: You have an error in your SQL syntax near '\hot shots II\, releasedatefill = 20010708 where requstID = 2' at line 1 I can't see any reason for the error. Your web based interface is mangling your query. Hi Julian. I'd recommend taking the suggestion and try the query through the command-line interface. If it works there, then you've isolated the problem to the interface you're using. Divide and conquer. At this point I don't have telnet access on my host so I'm down to using scripts and myphpadmin. I've made a request to get telnet access and I'll let you know if command line helpsIn the meantime I'm going to try whatever mysql interfaces i can get my hands on. But it's my php script that Is the main problem. Even If command line does what it should and responds accordingly to a normal insert statement. It still doesn't help me to use it where i need itIn my code BTW, should requstID be requestID? Fortunately this was a typo i made only for the benefit of this group. rest assured I haven't been testing with a misspelled column name :) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Problem with UPDATE statement
On 10-Aug-2001 Julian Simpson wrote: In what way is it mangling my query? and why won't the same query work from my php script. I would accept this if it was just any interface but myphpadmin is supposed to be in line with mysql standards. update cdrequest set albumfill = hot shots II, releasedatefill ^ here ^ and here use single quotes : $qry=update cdrequest set albumfill = 'hot shots II', ... 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) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with UPDATE statement
I have a table 'cdrequest' it has columns 'albumfill' and 'releasedatefill' among others when i do the following query with myphpadmin update cdrequest set albumfill = hot shots II, releasedatefill = 20010708 where requstID = 2; i get: You have an error in your SQL syntax near '\hot shots II\, releasedatefill = 20010708 where requstID = 2' at line 1 I can't see any reason for the error. part 2: when I flip to properties for the cdrequest just to make sure i'm spelling the columns corectly and cut and paste the exact same query on that page IT WORKS Why would it work on that page but not the other. both are supposed to be places where you can enter a normal sql query.. It also doesn't work in my PHP script which is where I really want it to work. What am I missing?? thanx Julian btw. I've tried all different combinations of double quotes and single quotes so it's not that - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Problem with UPDATE statement
update cdrequest set albumfill = hot shots II, releasedatefill = 20010708 where requstID = 2; i get: You have an error in your SQL syntax near '\hot shots II\, releasedatefill = 20010708 where requstID = 2' at line 1 I can't see any reason for the error. Your web based interface is mangling your query. part 2: when I flip to properties for the cdrequest just to make sure i'm spelling the columns corectly and cut and paste the exact same query on that page IT WORKS Why would it work on that page but not the other. both are supposed to be places where you can enter a normal sql query.. Because your web based interface is mangling your query. Try using the mysql command line client. It also doesn't work in my PHP script which is where I really want it to work. Paste source. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
