join based on one column and use value from another col-CodePudding

I have a table themeNamesthat looks like this:

themeName        id
firstTheme       3
secondTheme      5
NewTheme         9
HelloTheme       8

I want to join it with another table updated that looks like this:

val      newName       oldName 
1        first         firstTheme
2        second        secondTheme

I want to create a new column in the first table by joining based on the oldNames/themeName such that if a themeName is not present, then that row's value should be empty.

The final result should be look like this:

themeName       id      finalName
firstTheme       3      first
secondTheme      5      second 
NewTheme         9      NULL
HelloTheme       8      NULL

How can I achieve this?

CodePudding user response：

You want a left join between the two tables:

SELECT
    t.themeName,
    t.id,
    u.newName AS finalName
FROM themeNames t
LEFT JOIN updated u
    ON u.oldName = t.themeName;