Let’s say I have two MySQL tables:
table1 (templates) Columns: id_template, name, col3, col4, …
table2 (aliases) Columns: id_alias, id_template_orig, id_user, alias
I want to select a row from the table1 (templates) including all the columns (*). In the same select statement I want to check if current user has saved an alias for the original template name in the table2 (aliases). There is no problem getting the alias column included in the result row with LEFT JOIN but I wonder if there is a way to replace the original name column value already selected from table1 with the table2 alias value.
So here is the select so far:
SELECT table1.*, table2.alias, CASE WHEN table2.alias IS NULL THEN table1.name ELSE table2.alias END name FROM table1 LEFT JOIN table2 ON table2.id_template_orig=table1.id_template WHERE table1.id_template='1'
This works almost fine except that it returns two name columns instead of replacing the first with the second
I know that it is not the best practice to select all the columns with table1.* and my goal would be achieved by instead using select id_template, col3, col4, … from table1 but sometimes * just makes things much easier.
Unfortunately, there is no option in MySQL to select
* but one column – as in BigQuery for example, that supports
select * except (name).
You have two options:
select table1.*; instead, enumerate all the columns you want, expect
select *, and give a different alias to the generated column.
The second option might be your best pick, since you clearly stated that you want to keep
select table1.*. It would look like:
select t1.*, t2.alias, coalesce(t2.alias, t1.name) as new_name from table1 t1 left join table2 t2 on t2.id_template_orig = t1.id_template where t1.id_template = 1
coalesce()can just replace your
table aliases make the query easier to write and read