Re: query syntax help
Thx's Fred... as soon as I sent the email and re-read it again... I spotted the 'as' alias table reference to the table, was actual a reserved word,..causing the error :) thx's again. -- MikeBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Fred van Engen wrote: On Tue, Jan 20, 2004 at 04:10:44PM -0600, Mike Blezien wrote: I've been looking at this SQL query a dozen times or more, but keep getting a syntax error message, Query: SELECT ai.affilid,ai.create_date,CONCAT(ai.fname,' ',ai.lname) AS name,aw.siteid,ai.email,as.username,as.status FROM affiliate_info ai,affiliate_signup as,affiliate_website aw AS is a reserved word. WHERE aw.siteid = 1000 AND ai.affilid = as.affilid AND aw.affilid = ai.affilid what is wrong with this query syntax ?? the syntax error is suppose to be in this area: `affiliate_website aw WHERE aw.siteid = 1000` A bit before that. Regards, Fred. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query syntax help
Mike Blezien said: > > I've been looking at this SQL query a dozen times or more, but keep > getting a syntax error message, Query: > > SELECT ai.affilid,ai.create_date,CONCAT(ai.fname,' ',ai.lname) AS > name,aw.siteid,ai.email,as.username,as.status > FROM affiliate_info ai,affiliate_signup as,affiliate_website aw ^^ reserved word > WHERE aw.siteid = 1000 > AND ai.affilid = as.affilid AND aw.affilid = ai.affilid Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query syntax help
On Tue, Jan 20, 2004 at 04:10:44PM -0600, Mike Blezien wrote: > I've been looking at this SQL query a dozen times or more, but keep getting > a syntax error message, Query: > > SELECT ai.affilid,ai.create_date,CONCAT(ai.fname,' ',ai.lname) AS > name,aw.siteid,ai.email,as.username,as.status > FROM affiliate_info ai,affiliate_signup as,affiliate_website aw AS is a reserved word. > WHERE aw.siteid = 1000 > AND ai.affilid = as.affilid AND aw.affilid = ai.affilid > > what is wrong with this query syntax ?? the syntax error is suppose to be > in this area: > `affiliate_website aw WHERE aw.siteid = 1000` > A bit before that. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query syntax help?
Try: SELECT FF.name AS thename, MAX(FF.label) AS thelabel, F.name AS fieldsname FROM regformfields as FF INNER JOIN regfields as F ON (FF.name = F.Name) WHERE FF.label != '' GROUP BY FF.name, F.name I don't think you can include the ORDER BY F.saveorder (another column) in this case, unless you include it (F.saveorder) in the SELECT and GROUP BY list. HTH, Tore. - Original Message - From: "Scott Brown" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, February 22, 2003 1:57 PM Subject: Query syntax help? > OK, I am having a bit of trouble designing a MySQL query that returns what > I want. Here is the query as I have it thus far: > > SELECT DISTINCT regformfields.name AS thename, >regformfields.label AS thelabel, >regfields.name AS fieldsname > FROM regformfields > INNER JOIN regfields ON (regformfields.name = regfields.Name) WHERE > regformfields.label != '' > ORDER BY regfields.saveorder; > > In this particular query, there can be multiple occurrences of thename(can > be filtered by DISTINCT), therefore multiple occurrences of thelabel (which > can't be filtered by DISTINCT, as it is always different for the same > thename), but fieldsname is always unique. > > I don't care which thename or which thelabel is returned, but I only want > one (these two tables, together with some others, construct a schema for > yet others...), i.e thename = 'email' may be returned twice in this result > set, but I only want it to appear once. DISTINCT, as it is used here, does > not return what I want, as thelabel will rarely, if ever, be distinct. > > The ideal query would force the DISTINCT to be related ONLY to thename, and > return whatever thelabel it happens to grab, based on however it is > indexing, which would be the first saveorder it stumbles upon. > > Any help would be appreciated! > > TIA, > --Scott Brown > > > > - > 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: Query syntax help
What you had looks fine except the date...change what you had to: AND date >= '2002-03-17'; # date needs quotes around it Should work. -Original Message- From: rory oconnor [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 21, 2002 8:49 AM To: mysql list (choose midget) Subject: Query syntax help I'm trying to figure out a query that will tell me the total number of people in our house email file that physically opted out in the last week. I'm a bit of a mysql newbie as you can tell... This is the general concept, though it doesn't seem to work: select count(id) from contact # my data table where optin='no'# shows they are an opt-out AND bad_email IS NULL # is ticked if it was a bounceback opt-out AND email IS NOT NULL # show only for records that have emails AND date >= 2002-03-17; # show data only since last sunday I appear to be getting hung up on the date part. I'm not sure if I can use that kind of operator on a date with that format. Any help is appreciated! Thanks, Rory - 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: Query syntax help
> I'm trying to figure out a query that will tell me the total number of > people in our house email file that physically opted out in the last > week. I'm a bit of a mysql newbie as you can tell... > > This is the general concept, though it doesn't seem to work: > > select count(id) from contact # my data table > where optin='no' # shows they are an opt-out > AND bad_email IS NULL # is ticked if it was a bounceback opt-out > AND email IS NOT NULL # show only for records that have emails > AND date >= 2002-03-17; # show data only since last sunday > > I appear to be getting hung up on the date part. I'm not sure if I can > use that kind of operator on a date with that format. Any help is > appreciated! You need to put the date constant in quotes: ... AND date >= '2002-03-17'; -- Roger - 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: Query syntax help
AND date >= "2002-03-17"; -Original Message- From: rory oconnor [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 21, 2002 10:49 AM To: mysql "list (choose midget) Subject: Query syntax help I'm trying to figure out a query that will tell me the total number of people in our house email file that physically opted out in the last week. I'm a bit of a mysql newbie as you can tell... This is the general concept, though it doesn't seem to work: select count(id) from contact # my data table where optin='no'# shows they are an opt-out AND bad_email IS NULL # is ticked if it was a bounceback opt-out AND email IS NOT NULL # show only for records that have emails AND date >= 2002-03-17; # show data only since last sunday I appear to be getting hung up on the date part. I'm not sure if I can use that kind of operator on a date with that format. Any help is appreciated! Thanks, Rory - 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
