joejoeso joejoeso - 2 months ago 14
MySQL Question

Mysql: how to add a virtual column before UNION

I want to add a virtual column

'enabled' = 1
for all the result from the first union of my statement, and the
'enabled' = 0
for the next union. This is the statement I have so far...

SELECT p.*
FROM member_permissions mp
JOIN permissions p ON p.permission_id = mp.permission_id
WHERE member_id = 1
UNION
SELECT pgp
FROM member_permissions mp
JOIN permission_link pl ON pl.permission_group_id = mp.permission_group_id
JOIN permissions pgp ON pgp.permission_id = pl.permission_id
WHERE member_id = 1
UNION // this will have enabled = 0 //
SELECT *
FROM permissions

Answer

Just specify the new column as the source:

select column1, column2, ..., 1 as enabled
from ....
union
select column1, column2, ..., 0 as enabled
from ....

Just a note, you must use the same number of columns with the same data types when using union.

Comments