What's wrong with this query?
I'm beating my head against the wall. I just can't see
what's wrong with it; what would be causing the error:
SELECT certificate.cert_num, master_info.uid
FROM certificate
JOIN master_info ON ( certificate.uid = master_info.uid )
LEFT JOIN endorsements ON ( certificate.cert_num = endorsements.cert_num )
WHERE certificate.active = 1
AND certificate.referred = 0
AND certificate.status IN ('O', 'C')
AND endorsements.endorse_mode='cancel'
ORDER BY master_info.company, certificate.bound, certificate.cert_num;
ERROR 1064: You have an error in your SQL syntax near 'ON ( certificate.uid =
master_info.uid ) LEFT JOIN endorsements ON ( certificate' at line 1
I don't/can't see where the query is syntactically incorrect. What am I missing?
thnx,
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What's wrong with this query?
On 10 Jul 2003 at 13:20, Chris Boget wrote:
SELECT certificate.cert_num, master_info.uid
FROM certificate
JOIN master_info ON ( certificate.uid = master_info.uid )
LEFT JOIN endorsements ON ( certificate.cert_num =
endorsements.cert_num ) WHERE certificate.active = 1 AND
certificate.referred = 0 AND certificate.status IN ('O', 'C') AND
endorsements.endorse_mode='cancel' ORDER BY master_info.company,
certificate.bound, certificate.cert_num;
ERROR 1064: You have an error in your SQL syntax near 'ON (
certificate.uid = master_info.uid ) LEFT JOIN endorsements ON (
certificate' at line 1
Plain JOIN doesn't take a join condition (ON). You presumably mean
INNER JOIN. See the documentation:
http://www.mysql.com/doc/en/JOIN.html
--
Keith C. Ivey [EMAIL PROTECTED]
Tobacco Documents Online
http://tobaccodocuments.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What's wrong with this query?
what would be causing the error:
SELECT certificate.cert_num, master_info.uid
FROM certificate
JOIN master_info ON ( certificate.uid = master_info.uid )
LEFT JOIN endorsements ON ( certificate.cert_num =
endorsements.cert_num )
WHERE certificate.active = 1
AND certificate.referred = 0
AND certificate.status IN ('O', 'C')
AND endorsements.endorse_mode='cancel'
ORDER BY master_info.company, certificate.bound, certificate.cert_num;
ERROR 1064: You have an error in your SQL syntax near 'ON (
certificate.uid =
master_info.uid ) LEFT JOIN endorsements ON ( certificate' at line 1
I don't think the [CROSS] JOIN gets a join condition. Check the manual on
JOIN syntax
http://www.mysql.com/doc/en/JOIN.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What's wrong with this query?
Why isn't the key being used in the c (certificate) table?
SELECT DISTINCT a.uid, a.company, a.firstname, a.lastname
FROM master_info a, logins lsl, logins lc, certificate c WHERE
a.uid = lsl.uid AND lc.parent = lsl.uid AND lc.uid = c.uid AND
c.void 1 AND c.status IN
('CP', 'MC', 'AIC', 'RP', 'VQ', 'NQ', 'LQ', 'RQ', 'NG', 'EP', 'AA',
'AD' ) AND lsl.active = 1 AND lsl.void = 0 ORDER BY company, uid
How do you think the key should be used? You have a key on status,
but you're asking for a wide range of status values. Presumably
MySQL thinks (probably correctly) that using the index to find a
range of status values from 'AA' to 'VQ' is no faster than doing a
full table scan.
I could be missing something, but if so it would help if you could
explain how you expect the key to be used.
Well, I would expect it to use the key on the status column. But
barring that (as you suggest, MySQL might think that using the
key on status would be no faster), I would at least expect it to be
using the key for the 'uid' field and possible the (uid,void,status)
key as well. Even just using the key on the uid field could very
well speed the query up as it would be pulling the records matching
the 'status' column from a diminished field of possible records.
Or do I have it all wrong?
Chris
mysql,query,blah
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What's wrong with this query?
Why isn't the key being used in the c (certificate) table?
SELECT DISTINCT a.uid, a.company, a.firstname, a.lastname
FROM master_info a, logins lsl, logins lc, certificate c WHERE
a.uid = lsl.uid AND lc.parent = lsl.uid AND lc.uid = c.uid AND
c.void 1 AND c.status IN
('CP', 'MC', 'AIC', 'RP', 'VQ', 'NQ', 'LQ', 'RQ', 'NG', 'EP', 'AA', 'AD' )
AND lsl.active = 1 AND lsl.void = 0
ORDER BY company, uid
EXPLAIN:
+---+++-+-+---+---+-+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+---+++-+-+---+---+-+
| c | ALL| uid,status | NULL|NULL | NULL | 11552 | where
used; Using temporary; Using filesort |
| lc| eq_ref | PRIMARY,uid,parent | PRIMARY | 10 | c.uid | 1 | |
| lsl | eq_ref | PRIMARY,uid| PRIMARY | 10 | lc.parent | 1 | where
used |
| a | eq_ref | PRIMARY| PRIMARY | 10 | lsl.uid | 1 | |
+---+++-+-+---+---+-+
mysql show index from certificate;
+-++-+--+-+---+-+--++-+
| Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Comment |
+-++-+--+-+---+-+--++-+
+-++-+--+-+---+-+--++-+
| certificate | 0 | PRIMARY |1 | invoice_num | A| 11552
| NULL | NULL | |
| certificate | 0 | cert_num|1 | cert_num| A|NULL
| NULL | NULL | |
| certificate | 0 | cert_num|2 | cert_order | A| 11552
| NULL | NULL | |
| certificate | 1 | uid |1 | uid | A|NULL
| NULL | NULL | |
| certificate | 1 | status |1 | status | A|NULL
| NULL | NULL | |
| certificate | 1 | invoice_num |1 | invoice_num | A|NULL
| NULL | NULL | |
| certificate | 1 | invoice_num |2 | status | A|NULL
| NULL | NULL | |
| certificate | 1 | x1 |1 | uid | A|NULL
| NULL | NULL | |
| certificate | 1 | x1 |2 | status | A|NULL
| NULL | NULL | |
| certificate | 1 | x1 |3 | void| A|NULL
| NULL | NULL | |
+-++-+--+-+---+-+--++-+
10 rows in set (0.00 sec)
mysql show index from logins;
+++--+--+-+---+-+--++-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Comment |
+++--+--+-+---+-+--++-+
| logins | 0 | PRIMARY |1 | uid | A | 9091 |
NULL | NULL | |
| logins | 1 | uid |1 | uid | A | NULL |
NULL | NULL | |
| logins | 1 | parent |1 | parent | A | NULL |
NULL | NULL | |
| logins | 1 | type |1 | type| A | NULL |
NULL | NULL | |
| logins | 1 | level|1 | level | A | NULL |
NULL | NULL | |
+++--+--+-+---+-+--++-+
5 rows in set (0.01 sec)
mysql show index from master_info;
+-++---+--+-+---+-+--++-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation|
Cardinality | Sub_part | Packed | Comment |
+-++---+--+-+---+-+--++-+
| master_info | 0 | PRIMARY |1 | uid | A|9078 |
NULL | NULL | |
| master_info | 1 | slbco_idx |1 | slbco_id| A|NULL |
NULL | NULL
Re: What's wrong with this query?
On 19 Jun 2003 at 16:18, Chris Boget wrote:
Why isn't the key being used in the c (certificate) table?
SELECT DISTINCT a.uid, a.company, a.firstname, a.lastname
FROM master_info a, logins lsl, logins lc, certificate c WHERE
a.uid = lsl.uid AND lc.parent = lsl.uid AND lc.uid = c.uid AND
c.void 1 AND c.status IN
('CP', 'MC', 'AIC', 'RP', 'VQ', 'NQ', 'LQ', 'RQ', 'NG', 'EP', 'AA',
'AD' ) AND lsl.active = 1 AND lsl.void = 0 ORDER BY company, uid
How do you think the key should be used? You have a key on status,
but you're asking for a wide range of status values. Presumably
MySQL thinks (probably correctly) that using the index to find a
range of status values from 'AA' to 'VQ' is no faster than doing a
full table scan.
I could be missing something, but if so it would help if you could
explain how you expect the key to be used.
--
Keith C. Ivey [EMAIL PROTECTED]
Tobacco Documents Online
http://tobaccodocuments.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
what's wrong with this query?
Please help me with the following c code: sprintf(update, update chrc set center='); len = 24; memcpy(update+len, center, 128); len += 128; sprintf(update+len, ', var='); len += 8; memcpy(update+len, var, 128); len += 128; sprintf(update+len, ' where cc='%s', row[0]); len += 15; if (ret = mysql_real_query(conn, update, len)) printf(fail update %d\n, ret); int center[4][8], var[4][8] row[0] is a chinese character (2 bytes) update is a buffer of char [500] mysql describe chrc; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | cc | char(2) binary | YES | | NULL| | | count | int(11) | YES | | NULL| | | center | char(128) binary | YES | | NULL| | | var| char(128) binary | YES | | NULL| | ++--+--+-+-+---+ The query I want to do is update chrc set center='--', var'++' where cc='xx' (1) where -- is center[4][8] (all of them), ++ is var[4][8], and xx is the chinese character. The result I get is that all rows in the table chrc are updated. And it can be seen from /var/log/mysql/mysql.log, the query executed is update chrc set center=' (2) But I dumped the variable update to a file, and it is exactly as what I want in (1). Please point me out what is the problem. Thank you very much. - 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
What's wrong with this query?
Hi all, I've tried to create the following type of field in a table: create table temp(id float(100,50) unsigned); insert into temp values(123.1234567890123456789012345678901234567890123456789012345); select * from temp; This prints: 123.12345886230469 What's wrong with MySQL? It didn't give me any error when creating the table or inserting the data, but it modifies the number. Thank you. I use MySQL 4.05 under Windows 2k. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - 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
