MySQL Creates a Table for an Internal Join UNION right join

advertisements

In my SQL 5.5:

Trying to create a table which is a result of a INNER JOIN of table A & B, and (UNION) RIGHT JOIN of table C & B.

CREATE TABLE IF NOT EXISTS TABLE_NAME AS (

(SELECT a.column1, b.column2 FROM TABLEA AS a

INNER JOIN TABLEB AS b

ON a.column1 = b.column1)

UNION

(SELECT c.column1, b.column2 FROM TABLEC AS c

RIGHT JOIN TABLEB AS b

ON b.column1 = c.column1)

);

Error:

ERROR 1064 (42000) at line 11: 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 '(SELECT a.column1, b.column2 FROM TABLEA AS a

The other attempt:

CREATE TABLE IF NOT EXISTS TABLE_NAME AS (

(SELECT a.column1, b.column2 FROM TABLEA AS a

INNER JOIN TABLEB AS b

ON a.column1 = b.column1)

UNION

(SELECT c.column1, b.column2 FROM TABLEC AS c

RIGHT JOIN TABLEB AS b

ON b.column1 = c.column1)

);

Error:

ERROR 1064 (42000) at line 11: 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

Could any guru offer some advice? Thanks.


Try the following instead:

CREATE TABLE IF NOT EXISTS TABLE_NAME AS

(SELECT a.column1, b.column2 FROM TABLEA AS a

INNER JOIN TABLEB AS b

ON a.column1 = b.column1)

UNION

(SELECT c.column1, b.column2 FROM TABLEC AS c

RIGHT JOIN TABLEB AS b

ON b.column1 = c.column1)

See Demo


Quoting from the documentation

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression

query_expression should not be enclosed by parentheses