I have a problem in my MySQL command
select upper(file_type) as 'ItemType', ponum as 'Policy Number', office as 'Office', fullname as 'FullName', remarks as 'Remarks', concat(x.id, '-',x.file_type) as 'Identification'
from (select * from active_tb
union
select * from processed_tb) as x
left join filelocation as y on x.identification = y.f_id
I get unknown column x.Identification
when trying to execute the query.
First I join two different tables, then select the columns I need, then assign an alias. I need to concatenate the identification column.
But I cannot use the alias in a left join.
Just build your identification column in your left join sub query first.
SELECT upper(file_type) AS 'ItemType',
ponum AS 'Policy Number',
office AS 'Office',
fullname AS 'FullName',
remarks AS 'Remarks',
x.identification
FROM (SELECT *,
concat(x.id, '-', x.file_type) AS 'identification'
FROM active_tb
UNION
SELECT *,
concat(x.id, '-', x.file_type) AS 'identification'
FROM processed_tb) AS x
LEFT JOIN filelocation AS y
ON x.identification = y.f_id
I solved it now.
Here is my code:
select ItemType, 'Policy Number', Office, fullname, identification, f_location
from (select upper(file_type) as 'ItemType', ponum as 'Policy Number', office as 'Office', fullname as 'FullName', remarks as 'Remarks', concat(id, '-',file_type) as 'Identification' from active_tb
union
select upper(file_type) as 'ItemType', ponum as 'Policy Number', office as 'Office', fullname as 'FullName', remarks as 'Remarks', concat(id, '-',file_type) as 'Identification' from processed_tb) as x
left join filelocation on filelocation.f_id = x.Identification
Please comment other suggestions.