CODEWITHSUNDEEP
mysql
Foster
Foster

Reputation: 385

mySQL UNION add column into generated query result

so I have this query 

SELECT a.*, b.full_name as salesman 
                    from sales a
                    LEFT JOIN user b ON a.salesman_id = b.id
                    WHERE a.deleted_at IS NULL AND (a.status = '1'  || a.status = '2' )  
                    AND a.balance <= 0

I want to add another column that is not related to any of the column from the first query. I want to add another column (payment_amount) into the generated result

After googled a while, i come into this query 

SELECT a.*, b.full_name as salesman from sales a
                    LEFT JOIN user b ON a.salesman_id = b.id
                    WHERE a.deleted_at IS NULL AND (a.status = '1'  || a.status = '2' )  
                    AND a.balance <= 0

UNION ALL
SELECT '','','','','','','','','','','','','','','','','','','','','','','','','','','',payment_amount from transaction

However, i cant see payment_amount column next to the generated result.

Please be reminded, that I can't edit the database.

the first query returns 28 columns. What is the problem here? have been dealing with it for hours. Any help given is really appreciated. Thank you.

Upvotes: 1

Views: 332

Answers (2)

Barmar
Barmar

Reputation: 780818

All rows in the result of a SQL query have the same column names. When you use UNION, the column names are taken from the names/aliases from the first subquery in the union. So in your case, the payment_amount will be in the column named salesman, since that's the corresponding column in the first subquery.

If you want it to be in a column of its own, you can add an extra column 0 AS payment_amount to the first subquery, and an extra '' to the second subquery.

SELECT a.*, b.full_name as salesman, 0 AS payment_amount
from sales a
LEFT JOIN user b ON a.salesman_id = b.id
WHERE a.deleted_at IS NULL AND (a.status = '1'  || a.status = '2' )  
AND a.balance <= 0

UNION ALL
SELECT '', '','','','','','','','','','','','','','','','','','','','','','','','','','','',payment_amount from transaction

Upvotes: 0

Avi
Avi

Reputation: 1145

union all will just add rows to your results from previous query. What exactly is ur problem?. Also if you are adding extra column in your 2nd query u need to add on dummy column in first.

SELECT a.*, b.full_name as salesman,"" as payment_amount   from sales a
                    LEFT JOIN user b ON a.salesman_id = b.id
                    WHERE a.deleted_at IS NULL AND (a.status = '1'  || a.status = '2' )  
                    AND a.balance <= 0

UNION ALL
SELECT '','','',... till 28 times,payment_amount from transaction

Upvotes: 1

Related Questions