CODEWITHSUNDEEP
mysqlsqlselect
Mohammad Naji
Mohammad Naji

Reputation: 5442

`SELECT field AS` to use UNION between 2 tables causes error#1064

Consider the following query:

SELECT x.* FROM
(
    (
        SELECT (id,
            insertuserid AS transaction_user_id,
            (user_price * (-1)) AS amount,
            "INVOICE" AS transaction_type,
            insertdatetime
            )
        FROM invoice
        WHERE transaction_user_id = 4
    )
    UNION
    (
        SELECT (id,
            user_id AS transaction_user_id,
            amount,
            "PAYMENT" AS transaction_type,
            insertdatetime)
        FROM payment
        WHERE user_id = 4
    )
)
AS x
ORDER BY x.insertdatetime

The query errors on the first AS it sees.

Even when I change insertuserid AS transaction_user_id to insertuserid in the first SELECT of Union, it errors on the second AS that is at the next line: (user_price * (-1)) AS amount!!!

Error message:

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
'AS transaction_user_id, (user_price * (-1)) AS amount, "' at line 5


SELECT x.* FROM ( ( SELECT (id, insertuserid AS transaction_user_id,
(user_price * (-1)) AS amount, "INVOICE" AS transaction_type, insertdatetime )
FROM invoice WHERE transaction_user_id = 4 ) UNION
( SELECT (id, user_id AS transaction_user_id, amount,
"PAYMENT" AS transaction_type, insertdatetime) FROM payment
WHERE user_id = 4 ) ) AS x
ORDER BY x.insertdatetime

Thank you

Upvotes: 1

Views: 313

Answers (1)

John Woo
John Woo

Reputation: 263723

just remove the parenthesis on the SELECT clause,

SELECT  x.* 
FROM
    (
        SELECT  id,
                insertuserid AS transaction_user_id,
                user_price * (-1) AS amount,
                'INVOICE' AS transaction_type,
                insertdatetime
        FROM    invoice
        WHERE   transaction_user_id = 4
        UNION
        SELECT  id,
                user_id AS transaction_user_id,
                amount,
                'PAYMENT' AS transaction_type,
                insertdatetime
        FROM    payment
        WHERE   user_id = 4
    ) AS x
ORDER BY x.insertdatetime

Upvotes: 4

Related Questions