query syntax help
Hello all, 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 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` MySQL version 4.0.15 w/InnoDB tables TIA, -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- 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
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
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. -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 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]
select query syntax help
Hello All, I have two table the look like this (greatly simplified): ResourceTable - int ResourceID var ResourceName ResourceLinkTable - int ResourceLinkID int ResourceID var Text I need to find all rows in ResourceTable for which there is NO entry in ResourceLinkTable. I know I could do this with sub-selects like this: Select * from ResourceTable where ResourceID not in (select distinct ResourceID from ResourceLinkTable) How can I do this in MySQL 4.0 without using sub-selects? Thanks, Dan Lamb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select query syntax help [ANSWER]
This is a common question. The syntax looks like this: SELECT a.* FROM tbl_a AS a LEFT JOIN tbl_b AS b ON a.id = b.id WHERE b.id.id IS NULL; The idea is you're retrieving a recordset of the two tables where the rows are joined on the id. For tbl_b, the id field has no value (its null) so you can identify those rows by asking for nulls in the `tbl_b` `id` column. In your case, I would try: SELECT tbl.* FROM ResourceTable AS tbl LEFT JOIN ResourceLinkTable AS lnk ON tbl.ResourceID= lnk.ResourceID WHERE lnk.ResourceID.id IS NULL ORDER BY ResourceName ASC; Regards, Adam -Original Message- From: Dan Lamb [mailto:[EMAIL PROTECTED] Sent: Thursday, October 30, 2003 9:39 AM To: [EMAIL PROTECTED] Subject: select query syntax help Hello All, I have two table the look like this (greatly simplified): ResourceTable - int ResourceID var ResourceName ResourceLinkTable - int ResourceLinkID int ResourceID var Text I need to find all rows in ResourceTable for which there is NO entry in ResourceLinkTable. I know I could do this with sub-selects like this: Select * from ResourceTable where ResourceID not in (select distinct ResourceID from ResourceLinkTable) How can I do this in MySQL 4.0 without using sub-selects? Thanks, Dan Lamb -- 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: select query syntax help
Dan, SELECT ResourceTable.* FROM ResourceTable LEFT JOIN ResourceLinkTable ON ResourceTable.ResourceID = ResourceLinkTable.ResourceID WHERE ResourceLinkTable.ResourceID IS NULL; Regards, Thomas On Thu, 30 Oct 2003, Dan Lamb wrote: Hello All, I have two table the look like this (greatly simplified): ResourceTable - int ResourceID var ResourceName ResourceLinkTable - int ResourceLinkID int ResourceID var Text I need to find all rows in ResourceTable for which there is NO entry in ResourceLinkTable. I know I could do this with sub-selects like this: Select * from ResourceTable where ResourceID not in (select distinct ResourceID from ResourceLinkTable) How can I do this in MySQL 4.0 without using sub-selects? Thanks, Dan Lamb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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
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
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
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
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
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
