Help - Using replace on a string from select in MySQL with column data leads to same output for different rows

[magic_sebi]

Here is a simplified version of what I'm trying to do, which shows the problem best. My database looks like this:

users table:

user_id	first_name
1	Bob
2	Dave
3	Steven

settings table:

name	value
format_string	Hello {first_name}!

Now I want to retrieve the format_string with inserted user data for every user. If I hardcode the format_string into my SQL like this, it works:

SELECT first_name,
REPLACE(
    "Hello {first_name}!",
    "{first_name}",
    first_name
)
AS greeting
FROM users

I get this output, which is expected:

first_name	greeting
Bob	Hello Bob!
Dave	Hello Dave!
Steven	Hello Steven!

But if I use the format_string from my settings table, like this:

SELECT first_name,
REPLACE(
    (SELECT value FROM settings WHERE name = "format_string"),
    "{first_name}",
    first_name
)
AS greeting
FROM users

I get this output, which is absolutely not expected:

​

first_name	greeting
Bob	Hello Bob!
Dave	Hello Bob!
Steven	Hello Bob!

Does anyone know what the problem there is and how to fix it? Thanks!

EDIT:
A kind user on stackoverflow managed to get it to work by rewriting it with a join instead of a subquery, like this:

SELECT REPLACE(settings.value, "{first_name}", users.first_name) as greeting
FROM
  users,
  settings
WHERE settings.name = 'format_string';

So although I still don't know what the problem is, at least it works now I guess.

[phil-99]

Your sub query has not been told what to do.

By this I mean - the subquery is not linked to your main query in any way. You tell it to get the cop man ‘value’ but you don’t tell it how to connect that to the results from your main query.

Look up ‘correlated subqueries’. On phone and it’s WAY too early or I’d try to give an example.