Question 270101 in Mysql Question posted in
The official MySQL documentation can be found here.

UNION ALL You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version

I am using Union All for two select query. Both side the query is different I have to find the records from both table and show. I have tried below query but I am getting 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 ‘as table2’ at line 4

SELECT * FROM 
(select company_id as ID,concat('https://example.com/axy/',`slug`) as url from company22 c where company_name like '%xyz%' and c.is_active = 1 and admin_approved = 1 order by company_id desc limit 0, 1 ) as table1
UNION All
 (SELECT ID, post_title, post_content, post_date FROM `wp_posts` where post_content like  '%xyz%'  and post_status = 'publish' and post_type='table' order by post_date DESC ) as table2
Tags:

3

Answers


  1. 0

    To get that to work, you can make from the UNION a subquery, or don’t use the outer select at all

    SELECT * FROM 
((select company_id as ID,concat('https://example.com/axy/',`slug`) as url , NULL , NULL from company22 c where company_name like '%xyz%' and c.is_active = 1 and admin_approved = 1 order by company_id desc limit 0, 1 ) 
UNION All
 (SELECT ID, post_title, post_content, post_date FROM `wp_posts` 
 where post_content like  '%xyz%'  
 and post_status = 'publish' 
 and post_type='table' 
 order by post_date 
 DESC )) tab1;

(select company_id as ID,concat('https://example.com/axy/',`slug`) as url, NULL. NULL from company22 c where company_name like '%xyz%' and c.is_active = 1 and admin_approved = 1 order by company_id desc limit 0, 1 ) 
UNION All
 (SELECT ID, post_title, post_content, post_date FROM `wp_posts` 
 where post_content like  '%xyz%'  
 and post_status = 'publish' 
 and post_type='table' 
 order by post_date 
 DESC )
    Login or Signup to reply.
  2. 0

    You need not aliases then you combine selects with UNION:

    select * from (
    (select * from a)
    union all
    (select * from b)
) u;

    Test here: https://sqlize.online/sql/mysql57/65949fae8b0616845e1277bbb1efda4c/

    Login or Signup to reply.
  3. 0

    The error message tells you that you have a syntax error in your query of

     SELECT *
FROM   (
                SELECT   company_id                                         AS id,
                                  Concat('https://example.com/axy/',`slug`) AS url
                FROM     company22 c
                WHERE    company_name LIKE '%xyz%'
                AND      c.is_active = 1
                AND      admin_approved = 1
                ORDER BY company_id DESC
                LIMIT    0, 1 ) AS table1
UNION ALL
          (
                   SELECT   id,
                            post_title,
                            post_content,
                            post_date
                   FROM     `wp_posts`
                   WHERE    post_content LIKE '%xyz%'
                   AND      post_status = 'publish'
                   AND      post_type='table'
                   ORDER BY post_date DESC ) as table2

    It is because you are trying to select from a non-table. Let’s do our first improvement and convert your union into a table (this will still be incorrect). You had two tables you generated, but their union was not a table.

     SELECT *
FROM   (
       (
                SELECT   company_id                                         AS id,
                                  Concat('https://example.com/axy/',`slug`) AS url
                FROM     company22 c
                WHERE    company_name LIKE '%xyz%'
                AND      c.is_active = 1
                AND      admin_approved = 1
                ORDER BY company_id DESC
                LIMIT    0, 1 ) AS table1
union ALL
          (
                   SELECT   id,
                            post_title,
                            post_content,
                            post_date
                   FROM     `wp_posts`
                   WHERE    post_content LIKE '%xyz%'
                   AND      post_status = 'publish'
                   AND      post_type='table'
                   ORDER BY post_date DESC ) AS table2 )

    Next, the problem is that your table1 and table2 have very different column lists. This spells disaster. Make sure that the column list in the two tables look similar.

    Finally, it is very probably wrong logically to have company_id at the same field place as post id. So you will need to think your query through again.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search