Use SQL to replace some different words in a column-CodePudding

I am a new bird to SQL/MySQL and I am practicing SQL and database. I used MS Server Studio and typed: "Select DocumentLevel,Title,Owner From Production.Document"， and it showed like the picture under: Table 1, but the owner column are showed as names.

I use SQL like this:

Select REPLACE(Owner,'217','Abby') From Production.Document

but it only showed a column with no title and only one number 217 was replaced.

Could you help me to use SQL to get the table like 2?

CodePudding user response：

The number is most likely an identity from another table.
So use it to get the data from the other table.

Let's assume that other table is Production.Persons
And it has the columns : ID, Name
Where ID is the primary key of Production.Persons

Then joining would work

Select DocumentLevel, Doc.Title, Owner.Name
From Production.Document As Doc
Left Join Production.Persons As Owner On Owner.ID = Doc.Owner

But if you're stuck without a table, and the teacher doesn't allow one.

Then you could use a CASE or CASE WHEN for the logic.

Select DocumentLevel, Title
, Case Owner
  When 217 Then 'Abby'
  When 219 Then 'Billy'
  When 220 Then 'Cindy' 
  End As Owner
From Production.Document