I need to make a query to get all records whose score is 70 or more and replace all nicknames with their corresponding proper name. I'm using MySQL
Table A
| name | score |
|---|---|
| Nick | 75 |
| Kate | 76 |
| Robert | 49 |
| John | 91 |
| Jenny | 87 |
| Bill | 29 |
| Gabrielle | 57 |
| Taylor | 88 |
| Joseph | 68 |
The desired output should be the following:
Query Result
| name | score |
|---|---|
| Nicholas | 75 |
| Kate | 76 |
| John | 91 |
| Jennifer | 87 |
| Taylor | 88 |
Notice that the names Nick and Jenny were replaced.
Currently, the way I'm doing it is using REPLACE but that doesn't scale very well so I was wondering if there's a way to get the nicknames from Table B and use it on my query. My current query looks like this:
SELECT
-- repeat this for every nickname
REPLACE(REPLACE(name,'Nick','Nicholas'),'Jenny','Jennifer') AS name,
score
FROM
table_a
WHERE
score >= 70;
Table B
| nickname | proper_name |
|---|---|
| Nick | Nicholas |
| Jenny | Jennifer |
| Bill | William |
| Gaby | Gabrielle |
CodePudding user response：
You use a left join to look up the proper name for each row in table_a. If the proper name isn't found, it will be null, so you use coalesce to use the original name:
select coalesce(proper_name, name) as name, score
from table_a
left join table_b on table_b.nickname=table_a.name
where score >= 70