CODEWITHSUNDEEP
mysql
stUrb
stUrb

Reputation: 6832

Return a string instead of field value when condition is met in MySQL

With my ongoing saga to try to do things directly in the database instead of using some php-logic, another question.

For instance: I've got a table: m/ id | by | msg an id, by who is this done and what is the message. The by field is an integer connected to another table which stores the name. Normally I would left join the two tables:

SELECT m.id, m.msg, user.name FROM m LEFT JOIN user ON m.by = u.id

With the resulting relation, e.g.:

1 | Hello World | Richard
2 | Foo Bar     | Some else
3 | Howdy       | Richard

I'm richard and have user.id=4

Is it possible to have the resulting relation to display you instead of Richard where the condition user.id = (int) is met?

Upvotes: 4

Views: 1116

Answers (2)

Rahul
Rahul

Reputation: 77876

Do it like this:

SELECT m.id, m.msg, 
(case when user.id = 4 then 'You'
 else user.name end) as username
FROM m LEFT JOIN user ON m.by = u.id

Upvotes: 3

eggyal
eggyal

Reputation: 125865

SELECT m.id, m.msg, IF(user.id = 4, 'you', user.name) AS name
FROM m LEFT JOIN user ON m.by = user.id

Upvotes: 5

Related Questions