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?
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?
> > 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]
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]