Mysql Error Code 1064 Union
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with error 1064 (42000): you have an error in your sql syntax; us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow 1064 mysql Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up mysql error 1064 (42000) UNION SELECT error 1064 but works if executed on it's own up vote 0 down vote favorite I've got two mySQL servers - offline is version 5.5.25, online is version 5.0.95. I am attempting to run a UNION SELECT to join two you have an error in your sql syntax check the manual that corresponds to your mysql server version result sets together. Both queries extract the same data from the same tables using the same joins - the only difference to each is the where query. Basic syntax is: (SELECT QUERY A) UNION (SELECT QUERY B) ORDER BY date_created DESC, subscription_rate_id ASC If I run this query offline, I get this error: Error Number: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN webmaster
#1064 - You Have An Error In Your Sql Syntax;
d ON d.dupe_master_id = webmaster.webmaster_id IN' at line 16 If I run the queries separately then both of them work. If I run the whole query ONLINE, it works. This only occurs in my OFFLINE mySQL which is running the newer mySQL version of the two. I believe the error relates to query B - but the same INNER JOIN is in query A. The query is horrendously long and not easily shortened / broken down which is why I haven't pasted it here. I will find a way to provide it if absolutely neccessary. Any help appreciated. mysql select union share|improve this question edited Dec 4 '12 at 10:30 bluefeet♦ 147k33196278 asked Dec 4 '12 at 10:24 MikkyX 9912 Can you post the full query? –bluefeet♦ Dec 4 '12 at 10:32 Trying, but pastebin says it's under heavy load. Will post the link as soon as I've got the query up there. –MikkyX Dec 4 '12 at 10:35 You can always post your tables, sample data and then your query in a sql fiddle. –bluefeet♦ Dec 4 '12 at 10:36 Got it up on pastebin: pastebin.com/asgSwMu0 The first query runs on its own. This then returns a list of IDs that are passed into the second query - you can see the webmaster_id NOT IN part. This ensures the second query cannot return results there were present in the first query (sometimes they can overlap - the system generating this q
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About
Check The Manual That Corresponds To Your Mysql Server Version For The Right Syntax To Use Near
Us Learn more about Stack Overflow the company Business Learn more about hiring developers mysql server version for the right syntax to use near '' at line 1 or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack error 1064 mysql 42000 mysqldump Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Syntax error in MySQL near UNION? up http://stackoverflow.com/questions/13700747/union-select-error-1064-but-works-if-executed-on-its-own vote 5 down vote favorite SELECT * FROM Activity AA WHERE AA.act_id IN ((SELECT A.act_id FROM Activity A WHERE A.user_id = 'lhfcws') UNION (SELECT J.act_id FROM Joinin J WHERE J.user_id = 'lhfcws')) ORDER BY AA.act_time ERROR MESSAGE: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT http://stackoverflow.com/questions/10510335/syntax-error-in-mysql-near-union J.act_id FROM Joinin J WHERE J.user_id = 'lhfcws')) ORDE' at line 7 Activity(act_id, user_id, act_name) Joinin(act_id, user_id) mysql sql syntax share|improve this question edited May 9 '12 at 8:27 user166390 asked May 9 '12 at 5:32 Lhfcws 507 possible duplicate of MySQL 3.23 UNION fails with Error 1064 –Makoto May 9 '12 at 5:42 That duplicate does not have a satisfactory [accepted] answer; this one does. –user166390 May 9 '12 at 8:26 add a comment| 2 Answers 2 active oldest votes up vote 5 down vote accepted The reason for your error is the parens around the select statements. You should write it as: SELECT * FROM Activity AA WHERE AA.act_id IN (SELECT A.act_id FROM Activity A WHERE A.user_id = 'lhfcws' UNION SELECT J.act_id FROM Joinin J WHERE J.user_id = 'lhfcws') ORDER BY AA.act_time But do go over @Raphaël Althaus ideas for improving your query. share|improve this answer answered May 9 '12 at 5:57 Ilion 4,85711033 got it! thx for your help~! –Lhfcws May 9 '12 at 6:49 add a comment| up vote 2 down vote Hmm, don't think you need such a subquery select * from Activity a where a.user_id
DOMAINS WEB DESIGN WEB DESIGN SERVICES CREATE YOUR OWN WEBSITE SITE HOSTING TOOLS MEET http://www.inmotionhosting.com/support/website/database-troubleshooting/error-1064 US MEET US ABOUT US PARTNERS & AWARDS BLOG WE'RE HIRING CONTACT US AMP LOGIN SUPPORT CENTER Search Support Center Product http://dba.stackexchange.com/questions/28101/mysql-1221-incorrect-usage-of-union-and-order-by Guides Dedicated Hosting Reseller Hosting KnowledgeBase Website Email Domain Names Reseller Billing Community Support Browse Questions Ask a Question User Submitted Articles error 1064 Education Channels Android BoldGrid cPanel DokuWiki Drupal 7 Drupal 8 Everything Email Joomla 2.5 Joomla 3.1 K2 Magento 1.6 Mambo MediaWiki Moodle Movable Type OpenCart OpenCart 2.0 osCommerce PHP-Fusion PHP-Nuke phpBB phpList Premium Website Builder PrestaShop 1.5 PrestaShop 1.6 SMF Tiki Wiki VirtueMart 3 you have an Web Hosting Manager Website Design WordPress Tools & Resources Email Configuration Blacklist Check Visual DNS Lookup Quick Tagger Icon Sets Favicon Generator Bounce Back Parser What is my IP address? >>Website .htaccess (17)Adobe Flash (2)APIs (2)Backup and Restore (15)Billing (8)Browser Issues (2)Connecting to a Database (5)cPanel (26)cPanel Account Management (16)Cron Jobs (4)Cube Cart (2)Database Setup (5)Database Troubleshooting (6)Domain Management (2)Dreamweaver (12)Drush (2)eCommerce (7)Elasticsearch (4)Fantastico (4)File Management (16)File Manager (6)FTP (10)FTP Client Setup (21)General Server Setup / Management (15)Getting Started Guides (18)Ghost (3)Google Tools (21)How To (23)HTML (2)Image Editing (2)IP Addresses (3)iWeb (1)JavaScript (2)Joomla (10)Joomla 2.5 (36)Koken (11)Linux (27)Logaholic (5)Magento (2)Maintaining Your Website (1)Managing Files (7)Microsoft FrontPage (11)Microsoft Publisher (1)ModSecurity (7) MODx (3)New Customers (7)Open Classifieds (1)PCI Compliance (7)PHP (15)PHP Configuration (7)PHP Troubleshooting (6)phpBB (25)phpMyAdmin (10)Premium Website Builder (22)Prestashop (1)Protect
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top MySql: #1221 - Incorrect usage of UNION and ORDER BY up vote 2 down vote favorite I have the following query which is intended to retrieve the newest 3 comments for each post in an array of posts. SELECT * FROM comment AS tbl WHERE sscm_id IN ( SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4 ORDER BY t1.sscm_id DESC LIMIT 3 UNION ALL SELECT sscm_id FROM comment AS t2 WHERE t2.sscm_oid = 3 ORDER BY t2.sscm_id DESC LIMIT 3 ) which gives me the following error: 1221 - Incorrect usage of UNION and ORDER BY Some searching & googling seemed to suggest that placing () around each subquery might solve this problem but changing the query to SELECT * FROM comment AS tbl WHERE sscm_id IN ( (SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4 ORDER BY t1.sscm_id DESC LIMIT 3) UNION ALL (SELECT sscm_id FROM comment AS t2 WHERE t2.sscm_oid = 3 ORDER BY t2.sscm_id DESC LIMIT 3) ) but this gives me the following error: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ALL (SELECT sscm_id FROM bpsocial_comment AS t2 WHERE t2.sscm_oid = 3 OR' at line 4 So: how can I get the UNION in combination with the ORDER BY in the subqueries to work? Thanks R mysql order-by syntax union share|improve this question asked Nov 2 '12 at 21:37 user13955 11813 Is sscm_id the primary key of the table? –ypercubeᵀᴹ Nov 2 '12 at 22:14 add a comment| 1 Answer 1 active oldest votes up vote 1 down vote accepted There are several ways to pass the IN / LIMIT limitation and the UNION / ORDER BY syntax requirements. Wrap the Union in a derived table: SELECT * FROM comment AS tbl WHERE sscm_id IN ( SELECT sscm_id FROM ( ( SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4 ORDER BY t1.sscm_id DESC LIMIT 3) UNION ALL ( SELECT sscm_id FROM comment AS t2 WHERE t2.sscm