MySQL subquery within LIKE CONCAT

Question

I'm trying to create a query that checks an inserted value against a field. I'm trying to accomplish this using a sub-query, but got stuck. What I'm trying to accomplish:

(message1) User1 says: Hello User2
(message2) ChatX says: User1, user2 said hello to you! 

I figured that in order to accomplish that, I need a subquery within the like statement.

SELECT chat.id, chat.userid, chat.message, user.userid, user.username 
FROM chat, user 
WHERE LOWER(message) LIKE CONCAT('hello ', (SELECT user.username FROM user WHERE XXX = user.username)) 
  AND chat.userid = user.userid

The XXX in the LIKE statement, is the username someone said hello to. It's there it should check against the user table and if the message matches a user output ChatX's line. My question, how do I make XXX work and how do I set it?

Answer 1

SELECT c.id, c.message,
       sender.userid, sender.username,
       receiver.userid, receiver.username
FROM chat c
JOIN user sender ON c.userid = sender.userid
JOIN user receiver ON LOWER(message) like CONCAT('hello ', receiver.username)